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

F

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.
 
M

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
 
S

Stefi

Try Data/Text to columns (Fixed length)!

Regards,
Stefi


„Faz1†ezt írta:
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top