Extracting numbers from at end of a string

[rplohocky]

Hello,
I am trying to find a formula that will extract the numbers at the end of a string. Sometimes the numbers will be 1, 2, 3, 4 or more. I tried using this formula but it returns B6 118, when I want just 118.
This is what I using in column B:
Example: 12 Count--CAB6 118
My formula:

=RIGHT(B3,SUM(LEN(B3)-LEN(SUBSTITUTE(B3,{"0","1","2","3","4","5","6","7","8","9"},""))))

My return is: B6 118
I think I need to add something to the formula that will take the numbers after the last space.

Thank you.

 

[rplohocky]

I'm not sure that I understand. Your original post asked for the number after the last space. Are you now suggesting there are no spaces?

It would be good if we could have (preferably with XL2BB ) a sample of 8-10 examples of your data showing any variety that can occur with your data and the expected results. It is always difficult to give a robust but efficient suggestion for something where there is only one example.

This is an example of how this formula will be used. The last numbers will only increase in size but no more than maybe 5 or 6 digits.

 

[Peter_SSs]

This is an example of how this formula will be used. The last numbers will only increase in size but no more than maybe 5 or 6 digits.

In that case Rick's formula should be fine, but based on those samples, so should this

=--REPLACE(B3,1,FIND(" ",B3,9),"")

 

[rplohocky]

In that case Rick's formula should be fine, but based on those samples, so should this

=--REPLACE(B3,1,FIND(" ",B3,9),"")

Thank you Peter_SSs, I switched my formula to yours.

 

[Peter_SSs]

You're welcome - the advantage of having a better understanding of the scope of the data!

BTW, also

=--MID(B3,FIND(" ",B3,9),9)

 

[Rick Rothstein]

If your data is as you last showed it, then you do not need to put a formula in cell C3 and copy it down... since you are using XL365, a single formula (see below) in cell C3 will be enough. Clear the cells in Column C and then put this formula in cell C3, it will automatically spill down to the bottom of your data...

=0+MID(B3:INDEX(B:B,COUNTA(B:B)+1),FIND(" ",B3,9)+1,9)

NOTE: This formula assumes the number after the CAB text is either 1 or 2 digits long.

 

[Rick Rothstein]

If your data is as you last showed it, then you do not need to put a formula in cell C3 and copy it down... since you are using XL365, a single formula (see below) in cell C3 will be enough. Clear the cells in Column C and then put this formula in cell C3, it will automatically spill down to the bottom of your data...

=0+MID(B3:INDEX(B:B,COUNTA(B:B)+1),FIND(" ",B3,9)+1,9)

NOTE: This formula assumes the number after the CAB text is either 1 or 2 digits long.

If the number of digits after the CAB text could be longer than 2 digits, then use this formula instead...

=LET(X,B3:INDEX(B:B,COUNTA(B:B)+1),0+MID(X,FIND(" ",X,9)+1,9))

 

[Peter_SSs]

Hi Rick

I think that using COUNTA to determine the data extent is problematic. Even as shown I'm not sure whether we can see B1 and therefore I'm unsure whether it contains data or not & that information affects how far your formula spills. Further, if new rows are subsequently added at the top (but with no data added in column B) results will be missing.
More robust I believe would be this (where 1000 is a row that will surely be below where data will ever be)

=LET(rng,B3:INDEX(B:B,LOOKUP("z",B3:B1000,ROW(B3:B1000))),0+MID(rng,FIND(" ",rng,9),9))

Having said that, I think entering a short standard formula from above and double-clicking the Fill Handle is both a simpler task and produces a more readable formula.

 

[Rick Rothstein]

Having said that, I think entering a short standard formula from above and double-clicking the Fill Handle is both a simpler task and produces a more readable formula.

Okay, something I am not sure of so maybe you know. Is a single formula spilled through, say, 1000 rows more, less or equal in efficiency to putting 1000 live formulas in those 1000 cells?

 

[Peter_SSs]

Is a single formula spilled through, say, 1000 rows more, less or equal in efficiency to putting 1000 live formulas in those 1000 cells?

Do you mean in recalculation time?
If so, no, I don't know as a general rule. I would expect that it would depend on the specific formulas/functions used.
I did test two of the 'spill' formulas here v the 'short' standard formulas and the spill formulas were marginally faster to recalculate but with the slowest one taking about 1/100 of a second for a thousand rows any slight difference is irrelevant in my mind.

I think that you already have it, but for the record, I was talking about the process of writing such a formula and getting it in place.

 

[Rick Rothstein]

Yes, I was referring to recalculation time. Okay, so the shorter formula copied down makes sense here. Give that, the OP should change the Answer from my post to your last short formula post (Message #12) as that is the formula the OP says he is now using.