Help - Separating numbers appearing on the right of a text string

[Faz1]

I need to separate the number from the right of a text string into its
own cell.


For example in the data:


"Salaries - Aides / Assistants 3708"
"Classroom Stationery & Materials 21"

I want to return 3708 and 21 as the value of the formula. Note that
there are spaces and the number lengths vary.

Does anybody know a formula I can write to extract this floating
numeric string which is not a fixed length and at the right of the text

string.

What this means is that I need to find the position from where the
numbers in the string start, and using the MID and LEN functions I can
specify this value to get the number extracted into its own cell.


Thanks.

 

[Max]

Here's one crack at it ..

Assuming the strings are in A1 down

Try in B1:
=MID(A1,MIN(IF(ISNUMBER(SEARCH({0;1;2;3;4;5;6;7;8;9},A1)),SEARCH({0;1;2;3;4;
5;6;7;8;9},A1))),LEN(A1)-MIN(IF(ISNUMBER(SEARCH({0;1;2;3;4;5;6;7;8;9},A1)),S
EARCH({0;1;2;3;4;5;6;7;8;9},A1)))+1)+0

Copy B1 down

 

[Guest]

Try Data/Text to columns (Fixed length)!

Regards,
Stefi


„Faz1†ezt írta:

 

[Max]

Sample construct at:
http://www.savefile.com/files/4680644
Separating_Numbers_From_Text_Faz1_gen.xls