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.

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
--
Rgds
Max
xl 97
---
Singapore, GMT+8
xdemechanik
http://savefile.com/projects/236895
--
"Faz1" wrote in message
oups.com...
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.

Try Data/Text to columns (Fixed length)!

Regards,
Stefi


€žFaz1€� ezt Ã*rta:

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.

Sample construct at:
http://www.savefile.com/files/4680644
Separating_Numbers_From_Text_Faz1_gen.xls
--
Rgds
Max
xl 97
---
Singapore, GMT+8
xdemechanik
http://savefile.com/projects/236895
--