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.

 

[KRice]

Here is one idea...

Book1
	A	B
1	12 Count--CAB6 118	118
2	41 42 Count- - CAB 6 1234	1234
Sheet4

Cell Formulas
Range	Formula
B1:B2	B1	=IF(ISERROR(FIND(" ",A1)),A1,RIGHT(A1,LEN(A1)-FIND("~",SUBSTITUTE(A1," ","~",LEN(A1)-LEN(SUBSTITUTE(A1," ",""))))))

If you want the extracted "number" to actually be treated as a number rather than text, then add a 0 to the formula...

=0+IF(ISERROR(FIND(" ",A1)),A1,RIGHT(A1,LEN(A1)-FIND("~",SUBSTITUTE(A1," ","~",LEN(A1)-LEN(SUBSTITUTE(A1," ",""))))))

 

[Rick Rothstein]

This formula should work for up to a 10-digit number in your version of Excel 365...

=MAX(IFERROR(0+RIGHT(B3,11-SEQUENCE(10)),))

 

[Peter_SSs]

Sometimes the numbers will be 1, 2, 3, 4 or more.

I think I need to add something to the formula that will take the numbers after the last space.

Here are two other options that should do that. I have included the second one as you may not yet have the TEXTAFTER function.

22 07 04.xlsm
	B	C	D
3	12 Count--CAB6 118	118	118
4	41 42 Count- - CAB 6 1234	1234	1234
5	ABC 12345678	12345678	12345678
End number

Cell Formulas
Range	Formula
C3:C5	C3	=TEXTAFTER(B3," ",-1)+0
D3:D5	D3	=RIGHT(SUBSTITUTE(B3," ",REPT(" ",20)),20)+0

 

[bosco_yip]

Another option,

In C3, formula copied down :

=-LOOKUP(1,-RIGHT(B3,ROW($1:$99)))

 

[Peter_SSs]

Another option,

In C3, formula copied down :

=-LOOKUP(1,-RIGHT(B3,ROW($1:$99)))

That would give an incorrect result if this was a possible value
12 Count--DEC 11

 

[rplohocky]

This formula should work for up to a 10-digit number in your version of Excel 365...

=MAX(IFERROR(0+RIGHT(B3,11-SEQUENCE(10)),))

Thank you for all the help! All of these options worked great. I ended up using Rick's only because it was shorter. Thanks Rick!

 

[Peter_SSs]

I ended up using Rick's only because it was shorter.

Just be aware that does not specifically extract the number after the last space.
I don't know what is possible with your data, but it would produce incorrect results with, for example, these values
12 Count--CDEC 11
12 Count--CAB6 1.18

 

[rplohocky]

Just be aware that does not specifically extract the number after the last space.
I don't know what is possible with your data, but it would produce incorrect results with, for example, these values
12 Count--CDEC 11
12 Count--CAB6 1.18

Peter_SSs,
I assume Ricks formula would provide an accurate result if my numbers never changed the format such as no decimals or spaces???

 

[Peter_SSs]

.. no decimals or spaces???

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.