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

11 Comments

  • Reply

    I have a single line of test field called "Project ID", I am trying to create a calculated number column called PrjId. I tried => "[Project ID] + 0" for the calculate field value but it did not work.

  • Reply

    Perhaps you are not using the right syntax!!

    "=[Project ID] + 0"

    are you missing equals sign..?

    Additionally ensure the site column name (field) indeed has space between Project and ID...in SharePoint you could easily be misled by the Display name and the actual name given/created for the field!

  • Reply

    Hi Kam!
    I did used your syntax.
    But my Calculated Field is combines the two strings and separated by a comma.
    Ex:
    15. ChapterName
    2. ChapterName

    This column is sort numbers as strings.

    Can you help me sort order correct? Please!

  • Reply

    Thanks Wesley. Worked great for me. I had noticed the Text issue but never would have thought of +0 as a resolution.

  • Reply

    Worked great for me. Thank you!

  • Reply

    Your saved me !!!

    Many Thanks !

  • Reply

    Simple yet genius. Solve my issue calculating from a choice field full of numbers. Thanks.

  • Reply

    Thank you Wes,
    You really helped me out with Group By Sorting of a calculated date field.
    For example I just wanted the date portion of a date/time field.
    I used this formula...
    =TEXT([Date of Entry],"dd-mm-yyyy")
    But I could not get the Sharepoint ListViewWebPart to Group By (descending) correctly as it treated the result as a string regardless of me setting the data type as Date and Time and format as Date Only.
    I can confirm that this works perfectly!
    =TEXT([Date of Entry],"dd-mm-yyyy")+0

    Cheers

  • Reply

    Worked for me. Thank you!

  • Reply

    this I tried as well as possible to retrieve value from my lookup column.

    I have an input "date" (date type column) of a file "myfile" which is a document of a librairy "docs". I also have a list "myListe" of "type" as a choice column and "validity" as a number.
    then "myfile" uses a lookup column "valid" to retrieve validity related to the type of "myfile". Now I have to calculate the expiration date of "myfile" using "valid" and the input date.
    I have tried but I got nothing so far!
    please help me. thank you!

  • Reply

    This has worked well. I was having personal number as text field wanted to sort it as number. User calculated filed =[Presonal number]+0 and worked well.
    Thanks Welsley
    Regards
    Mahendra Sonawane

Comments have been disabled for this content.