Formula to split Text from Numbers in cell

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

=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)

"John Ortt" <johnortt@noemailsuppliedasdontwantspam.com> wrote in message
news:448694ba$1_1@glkas0286.greenlnk.net...
> 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
>
>

Worked a treat, thanks Bob.


"Bob Phillips" <bob.NGs@somewhere.com> wrote in message
news:eHlILRhiGHA.3440@TK2MSFTNGP02.phx.gbl...
> =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)
>
> "John Ortt" <johnortt@noemailsuppliedasdontwantspam.com> wrote in message
> news:448694ba$1_1@glkas0286.greenlnk.net...
>> 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
>>
>>
>
>