Separate numbers from a phrase in a cell

[bright_mind]

How do you separate numbers from a phrase in a cell given the scenarios below? All I need are the word/ phrases.

Here are sample contents of the cells:

the quick 89
quick 143
the quick brown 7
brown fox 23

As one can see, all the numbers are at the right end but the digits vary while the string portion ranges from one to four words.

I believe a simple RIGHT or FIND function does not work in this scenario. Text to Column does not work as well.

Thank you in advance for the replies.

 

[Aladin Akyurek]

How do you separate numbers from a phrase in a cell given the scenarios below? All I need are the word/ phrases.

Here are sample contents of the cells:

the quick 89
quick 143
the quick brown 7
brown fox 23

As one can see, all the numbers are at the right end but the digits vary while the string portion ranges from one to four words.

I believe a simple RIGHT or FIND function does not work in this scenario. Text to Column does not work as well.

Thank you in advance for the replies.

Try...

B2:

=LEFT(A2,MIN(FIND({0,1,2,3,4,5,6,7,8,9},A2&"0123456789")-1))

C2:

=SUBSTITUTE(A2,B2,"")+0

where A2 houses a string with a number at the end.

 

[Rick Rothstein]

On the off-chance that you could possibly have a number in the text part but only want the number at the end... something like "The 3M Company ordered 123" (hard to tell what you might have from the made up text examples you posted)... then you can use this formula in place of Aladin's posted formula for cell B2...

=TRIM(RIGHT(SUBSTITUTE(A2," ",REPT(" ",99)),99))

This formula finds whatever is after the last space character in the text (whether it is a number or not).

 

[bright_mind]

Thanks! I tried the formulas and they worked!