0

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?

2

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!

| improve this answer | |
1

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),
))))))

 

| improve this answer | |
  • Thanks Mike for shedding more lights on Excel formulas. – Dikesh Gandhi Sep 23 '19 at 2:47

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.