Formula to split Text from Numbers in cell

J

John Ortt

Hi everyone

I have a column of data as follows

Joe Bloggs 995.1
Fred 22.6
James Brian Jones 333.54
Ted Baker 3875.34
etc

The name can be any number of words and the size of the number changes with
each entry.

Is there any way to split the text and numbers into two separate columns
using a code?

I tried using the search command with an isnumeric inside but to no
avail......

I also thought of looking for a space from the right hand side but I don't
know how to do it.

Any suggestions?

Thanks

John Ortt
 
B

Bob Phillips

=LEFT(A1,FIND("~",SUBSTITUTE(A1," ","~",LEN(A1)-LEN(SUBSTITUTE(A1,"
",""))))-1)

and

=MID(A1,FIND("~",SUBSTITUTE(A1," ","~",LEN(A1)-LEN(SUBSTITUTE(A1,"
",""))))+1,255)

--
HTH

Bob Phillips

(replace somewhere in email address with gmail if mailing direct)
 
J

John Ortt

Worked a treat, thanks Bob.


Bob Phillips said:
=LEFT(A1,FIND("~",SUBSTITUTE(A1," ","~",LEN(A1)-LEN(SUBSTITUTE(A1,"
",""))))-1)

and

=MID(A1,FIND("~",SUBSTITUTE(A1," ","~",LEN(A1)-LEN(SUBSTITUTE(A1,"
",""))))+1,255)

--
HTH

Bob Phillips

(replace somewhere in email address with gmail if mailing direct)
Click to expand...
 
Top