I am having a hard time getting a SharePoint 2013 list to sort in the correct order. We enter strings like this example 50-00-0. Then we want to split this into three columns so we may order them correctly. However when we try to get the first set of numbers using the calculated formula function:

=INT(LEFT([CAS #],LEN([CAS #])-5))

The calculated column still sorts as a string rather than as a number

I have also tried various other numeric functions to try and get the column to sort as a number, for example the VALUE function and adding 0 to the formula - Neither of these worked either.

  • try =(LEFT([CAS #],LEN([CAS #])-5))*1
    – Gaurravs
    Dec 7, 2015 at 19:00
  • or try =INT(LEFT([CAS #],LEN([CAS #])-5)*1
    – Gaurravs
    Dec 7, 2015 at 19:12
  • I have updated as answer, you can mark it. It can be helpful for someone else too
    – Gaurravs
    Dec 8, 2015 at 8:55

2 Answers 2

=(LEFT([CAS #],LEN([CAS #])-5))*1


=INT(LEFT([CAS #],LEN([CAS #])-5)*1

Both Should work fine


Extracting 111-222-33 for any sequence of 3 with a search for the dash:


=MID([CAS #],1,SEARCH("-",[CAS #])-1)


=MID([CAS #],LEN(V1)+2,SEARCH("-",[CAS #],LEN(V1)+2)-LEN(V1)-2)


=MID([CAS #],LEN(V1)+LEN(V2)+3,LEN([CAS #]))

SharePoint does some type conversions for you, so

=LEFT( [CAS #] , 2 )

is the same as

=LEFT( [CAS #] , "2" )

Converting strings to numbers can be done with:



returns Yes

All functions: https://www.365csi.nl/vm365com/365coach/#/Calculated_Column_Functions_List

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.