software

I need to separate the number from the right of a text string into its
own cell.For example in the data:quot;Salaries - Aides / Assistants 3708quot;
quot;Classroom Stationery amp; Materials 21quot;

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
savefile.com/projects/236895
--
quot;Faz1quot; gt; wrote in message oups.com...
gt; I need to separate the number from the right of a text string into its
gt; own cell.
gt;
gt;
gt; For example in the data:
gt;
gt;
gt; quot;Salaries - Aides / Assistants 3708quot;
gt; quot;Classroom Stationery amp; Materials 21quot;
gt;
gt; I want to return 3708 and 21 as the value of the formula. Note that
gt; there are spaces and the number lengths vary.
gt;
gt; Does anybody know a formula I can write to extract this floating
gt; numeric string which is not a fixed length and at the right of the text
gt;
gt; string.
gt;
gt; What this means is that I need to find the position from where the
gt; numbers in the string start, and using the MID and LEN functions I can
gt; specify this value to get the number extracted into its own cell.
gt;
gt;
gt; Thanks.
gt;
Try Data/Text to columns (Fixed length)!

Regards,
Stefi?az1??ezt ?rta:

gt; I need to separate the number from the right of a text string into its
gt; own cell.
gt;
gt;
gt; For example in the data:
gt;
gt;
gt; quot;Salaries - Aides / Assistants 3708quot;
gt; quot;Classroom Stationery amp; Materials 21quot;
gt;
gt; I want to return 3708 and 21 as the value of the formula. Note that
gt; there are spaces and the number lengths vary.
gt;
gt; Does anybody know a formula I can write to extract this floating
gt; numeric string which is not a fixed length and at the right of the text
gt;
gt; string.
gt;
gt; What this means is that I need to find the position from where the
gt; numbers in the string start, and using the MID and LEN functions I can
gt; specify this value to get the number extracted into its own cell.
gt;
gt;
gt; Thanks.
gt;
gt;

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