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.

 

[XOR LX]

XOR LX,

Nicely done. One for my archives - thanks.

Cheers, hiker!

 

[hiker95]

XOR LX,

 

[SAFMF]

Give this a try too:

=SUM(IFERROR(1*TRIM(MID(SUBSTITUTE(SUBSTITUTE(A1,"-",","),",",REPT(" ",99)),{1,3,5,7,9,11,13,15,17}*99,99)),0))

Thanks for this formula. i have been trying to adapt it so that i can use it on a similar all be it smaller string like this;

[TABLE="width: 168"]
<tbody>[TR]
[TD]g1,b1,a1

what do I need to do to show a total of 3 for this example?

Your help is much appreciated


[/TD]
[/TR]
</tbody>[/TABLE]

 

[XOR LX]

XOR LX,

Haha! I hope that's Yorkshire ale we're drinking and not your local Pennyslvanian "beer"!

 

[hiker95]

XOR LX,

I prefer Yuengling Lager, Yuengling & Son is the oldest operating brewing company in the United States, established in 1829.

I have not had a chance to try Yorkshire ale. But, if I can find it - look out

 

[XOR LX]

XOR LX,

I prefer Yuengling Lager, Yuengling & Son is the oldest operating brewing company in the United States, established in 1829.

I have not had a chance to try Yorkshire ale. But, if I can find it - look out

I was only kidding! Actually we've started to get some really good craft ales over from the States here in the last few years, and so I'll keep an eye out for your Yuengling & Son.

Cheers!

 

[István Hirsch]

Thanks for this formula. i have been trying to adapt it so that i can use it on a similar all be it smaller string like this;

[TABLE="class: cms_table, width: 168"]
<tbody>[TR]
[TD]g1,b1,a1

what do I need to do to show a total of 3 for this example?

Your help is much appreciated
[/TD]
[/TR]
</tbody>[/TABLE]

My solution was based on the position of „-„ and „,” so if the „-„ is missing you can not use my formula directly for the new sample. Trying to work out a formula for g1,b1,a1 (I think the other posters, too), still it would be a good idea to show all the samples you want the formula to work for.

 

[SAFMF]

the examples in this case would vary in size as below.[TABLE="width: 168"]
<tbody>[TR]
[TD="class: xl65, width: 168"]d1,g1,b1,a1
[TABLE="width: 168"]
<tbody>[TR]
[TD="class: xl65, width: 168"]g1,b1
[TABLE="width: 168"]
<tbody>[TR]
[TD="width: 168"]g1,b1,a1[/TD]
[/TR]
</tbody>[/TABLE]

[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[/TR]
</tbody>[/TABLE]

 

[Peter_SSs]

Is there always only one digit?

Is the digit always "1" - your samples are.

Is there always only one letter before the digit(s)?

Make sure we have a good variety of data that fully represents the range of data you can have.

 

[SAFMF]

No the digit can be a two, so could be b1,a2,c3,d1.; Yes there is only ever one letter before the digits