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.

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

2 Answers 2

Reset to default
0 
=(LEFT([CAS #],LEN([CAS #])-5))*1

OR

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

Both Should work fine

Improve this answer
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: https://www.365csi.nl/vm365com/365coach/#/Calculated_Column_Functions_List

Improve this answer

Your Answer

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

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.