Extracting certain numbers from a Cell

[SAFMF]

I am trying to extract the digits after the '-' in this type of data 236-1,46-2,jm007-6,893-2.

Any help would be appreciated.

 

[Peter_SSs]

You didn't answer this one

Is there always only one digit?

What I mean by it is: Could there be this? a32,b3,c321

 

[SAFMF]

in some rare occasions there may a situation where the number may be a32,b3,c1. But there would never be any time when three digits follow the letter

 

[Peter_SSs]

And following the discussion between some of us earlier in the thread ..
a32,b3,c1 has 3 "terms" (two commas)

Is there a maximum number of "terms" in a single cell or could a cell have this (or longer)?
a2,b4,c54,d3,e6,f5,x4,s3,a3,b56,g3

 

[SAFMF]

the maximum number of 'terms' is seven in a single cell

 

[Jborg]

you can try this amybe this is what you want.
Data is in B4 and formula is in H4.

=SUMPRODUCT(IFERROR(MID(B4,--(FREQUENCY(--(MID(B4,ROW(INDIRECT("1:"&LEN(B4))),1)="-")*ROW(INDIRECT("1:"&LEN(B4))),--(MID(B4,ROW(INDIRECT("1:"&LEN(B4))),1)<>"-")*ROW(INDIRECT("1:"&LEN(B4))))=1)*ROW(INDIRECT("1:"&LEN(B4)+1)),1)+0,0))

 

[SAFMF]

you can try this amybe this is what you want.
Data is in B4 and formula is in H4.

=SUMPRODUCT(IFERROR(MID(B4,--(FREQUENCY(--(MID(B4,ROW(INDIRECT("1:"&LEN(B4))),1)="-")*ROW(INDIRECT("1:"&LEN(B4))),--(MID(B4,ROW(INDIRECT("1:"&LEN(B4))),1)<>"-")*ROW(INDIRECT("1:"&LEN(B4))))=1)*ROW(INDIRECT("1:"&LEN(B4)+1)),1)+0,0))

This formula returns a '0'. I have changed the B4 to be V2 as this is the first cell where my data is currently sitting.

 

[SAFMF]

This formula returns a '0'. I have changed the B4 to be V2 as this is the first cell where my data is currently sitting.

If I change the second zero in this part +0,0)) to a 1 it returns 9 not thre as expected from the data it is looking at which in this example is g1,b1,a1

Any ideas?

 

[Jborg]

In the samle I have done on my part,(Text in B4 and Formula in H4), it is returning 11.

Always Ctrl + Shift + Enter

 

[billszysz]

Not beautiful but works

=SUM(IFERROR(--RIGHT(TRIM(MID(SUBSTITUTE($A2,",",REPT(" ",LEN($A2))),{0;1;2;3;4;5;6}*LEN($A2)+1,LEN($A2))),2),IFERROR(--RIGHT(TRIM(MID(SUBSTITUTE($A2,",",REPT(" ",LEN($A2))),{0;1;2;3;4;5;6}*LEN($A2)+1,LEN($A2)))),0)))

CTRL+SHIFT+ENTER

 

[István Hirsch]

Try this formula to handle the second series of samples (b1,f23,c2 etc.):

=SUM(IFERROR(1*REPLACE(TRIM(MID(SUBSTITUTE(","&A1&",",",",REPT(" ",50)),{1,2,3,4,5,6,7,8}*50,50)),1,1,""),0))