SharePoint Calculated Field from string to number
Every now and then you run into unexpected SharePoint behavior. The Title field of an item is a 'single line of text' field. In my specific case however this Title field contained a file number. Not a problem at all, BUT you get a really strange sort order when you start to sort numbers as strings. File number 1000 comes before file number 9. This is however expected behavior.
The client wanted to be able to sort the items in a numeric way so I simply created a calculated columns of type number with this expression "=[Title]". Easy peasy right? And that's where the unexpected behavior came along. The field indeed got the value of the Title field, but if I sorted the list by this field, it behaved exactly as if the field contained a single line of text. So again, 1000 comes before 9. Checked if I indeed set the calculated field to be a number and well, I did. The value simply wasn't treated as a number but as text. Strange...
So I started digging in my development experience and I decided to make a little change to the expression. I changed the expression to "=[Title] + 0". And what do you know? The calculated field now indeed returned a number and was treated like a number. Sorting by the field now produced the expected behavior. File number 9 now comes before file number 10000.
Cheers,
Wes