Help - separating numbers from a text string that appear on the right

[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 numbers are not of fixed length.

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.

 

[Guest]

Hi

Try this:
=VALUE(MID(A1,FIND(" ",A1),999))

Andy.

 

[Guest]

Hmmm. That will find the first space!! It depends on the content, but you
could try:
=VALUE(MID(A1,FIND(" ",A1),999))

Andy.

 

[Max]

Here's another crack at it, as responded to your multi-post in
..worksheet.functions
(pl refrain from multi-posting)

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

 

[Max]

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

 

[RagDyer]

You can try this *array* formula:

=--MID(A1,MATCH(FALSE,ISERROR(--MID(A1,ROW(INDIRECT("1:100")),1)),0),100-SUM
(--ISERROR(1*MID(A1,ROW(INDIRECT("1:100")),1))))

--
Array formulas are entered using CSE, <Ctrl> <Shift> <Enter>, instead of the
regular <Enter>, which will *automatically* enclose the formula in curly
brackets, which *cannot* be done manually.
--
HTH,

RD
==============================================
Please keep all correspondence within the Group, so all may benefit!
==============================================