String Calculation SharePoint 2010

Question

I have the below string stored in a SharePoint Column:

11608,613,12,12643,12644,12656

I want to extract the value after the last comma. That is I need to extract only 12656 and use it for other operations.

How to achieve this using SP 2010 Designer workflow or calculated column?

Answer 1

You can create new calculated column with "Single line text" or "number" type. Add the below given formula to extract the value after last comma.

Formula for calculated column is:

=IF(ISERROR(RIGHT(Title,(SEARCH(",",Title,1)-1))),"",RIGHT(Title,(SEARCH(",",Title,1)-1)))

Note: Here I have used "Title" column for creating formula. Please replace it with your column name during applying the formula.

Hope this will help you!

Answer 2

Dikesh has the right answer to the OP's precise question. But it only works with the supplied example. Two problems:

• It returns blank if there is only one numeric value, or Title is blank.
• It extracts the last value, but only if it is the same length as the first value. (Change the first value to 12 to see what happens.)

First, a solution that counts backwards looking for a comma:

If you just want the last number, and the number of digits is not more than six (add to the formula for more) then:

=IF(LEFT(RIGHT(TheCol,2),1)=",",RIGHT(TheCol,1),
   IF(LEFT(RIGHT(TheCol,3),1)=",",RIGHT(TheCol,2),
     IF(LEFT(RIGHT(TheCol,4),1)=",",RIGHT(TheCol,3),
       IF(LEFT(RIGHT(TheCol,5),1)=",",RIGHT(TheCol,4),
         IF(LEFT(RIGHT(TheCol,6),1)=",",RIGHT(TheCol,5),
           IF(LEFT(RIGHT(TheCol,7),1)=",",RIGHT(TheCol,6),
TheCol)))))) 

 

Second, a solution that uses sightly reformatted data. If we can change:

11608,613,12,12643,12644,12656

to: (extra spaces so each is always five characters)

11608,  613,   12,12643,12644,12656

Then this works nicely for up to six numbers ("TheCol" is your column name):

=IF(ISERROR(SEARCH(",",TheCol)),TheCol,
    IF(LEN(TheCol)-LEN(SUBSTITUTE(TheCol,",",""))=1, MID(TheCol,7,5),
      IF(LEN(TheCol)-LEN(SUBSTITUTE(TheCol,",",""))=2, MID(TheCol,13,5),
        IF(LEN(TheCol)-LEN(SUBSTITUTE(TheCol,",",""))=3, MID(TheCol,19,5),
          IF(LEN(TheCol)-LEN(SUBSTITUTE(TheCol,",",""))=4, MID(TheCol,25,5),
            IF(LEN(TheCol)-LEN(SUBSTITUTE(TheCol,",",""))=5, MID(TheCol,31,5),
))))))