0

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

OR

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

Both Should work fine

0

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

V1

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

V2

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

V3

=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:

=VALUE(V1)

=ISNUMBER(VALUE("21"))

returns Yes

All functions: http://viewmaster365.com/365coach/#/Calculated_Column_Functions_List

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

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