# Sorting Calculated Numeric Columns Based on String Column

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

## 2 Answers

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

OR

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

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