Formula to split Text from Numbers in cell

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

John Ortt - 07 Jun 2006 10:07 GMT

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

Reply to this Message

Bob Phillips - 07 Jun 2006 10:25 GMT

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

and

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

Signature

HTH

Bob Phillips

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

> Hi everyone
>
[quoted text clipped - 23 lines]
>
> John Ortt

Reply to this Message

John Ortt - 07 Jun 2006 11:29 GMT

Worked a treat, thanks Bob.

> =LEFT(A1,FIND("~",SUBSTITUTE(A1," ","~",LEN(A1)-LEN(SUBSTITUTE(A1,"
> ",""))))-1)
[quoted text clipped - 33 lines]
>>
>> John Ortt

Reply to this Message