Formula to split text and numbers

[soren.bo.christensen]

Hi

I am trying to create a formula to split the content of a cell into
text and numbers but I can't figure out how to do it

Example
TextString 1 4456
Another TextString 23456
Third TextString 3 2245

The formula I am interested in will find the last number after the
space, in the example above I will be needing 4456, 23456 and 2245

Is that possible using a formula or do I need to write a macro to do
it?

Thanks
Soren
(e-mail address removed)

 

[Bob Phillips]

A formula will do it

=--(MID(A1,FIND(CHAR(1),SUBSTITUTE(A1,"
",CHAR(1),LEN(A1)-LEN(SUBSTITUTE(A1," ","")))),99))

 

[sbcxp]

Thank you so much. It gives me a #VALUE error but at least I now have
something to start with.

//Soren

 

[Bob Phillips]

It worked for me on all three examples that you gave.

--
__________________________________
HTH

Bob

Thank you so much. It gives me a #VALUE error but at least I now have
something to start with.

//Soren

 

[Ron Rosenfeld]

Hi

I am trying to create a formula to split the content of a cell into
text and numbers but I can't figure out how to do it

Example
TextString 1 4456
Another TextString 23456
Third TextString 3 2245

The formula I am interested in will find the last number after the
space, in the example above I will be needing 4456, 23456 and 2245

Is that possible using a formula or do I need to write a macro to do
it?

Thanks
Soren
(e-mail address removed)

If the number is always the last space-separated portion of the string, and if
that portion will always be a number, then you could try this:

=TRIM(RIGHT(SUBSTITUTE(TRIM(A1)," ",REPT(" ",99)),99))

which always picks up the last "word" in a string.
--ron