Hi Everyone,
I have thousands of cells with names and addresses, and each cell combines
both. I'd like to separate "Jane Doe 123 Street Road" into "Jane Doe" in one
column then the rest of the address in another. I've tried text to columns
and the formulas =LEFT, =MID, etc None of these have worked because
everyone's name is a different number of characters. Any help is greatly
appreciated.
Thank you,
Aaron

If it is always Name-space-name-space-address you could use

=LEFT(A1,FIND(" ",A1,FIND(" ",A1)+1)-1)

and

=RIGHT(A1,LEN(A1)-FIND(" ",A1,FIND(" ",A1)+1))

--

HTH

RP
(remove nothere from the email address if mailing direct)


"Aaron" wrote in message
...
Hi Everyone,
I have thousands of cells with names and addresses, and each cell combines
both. I'd like to separate "Jane Doe 123 Street Road" into "Jane Doe" in
one
column then the rest of the address in another. I've tried text to
columns
and the formulas =LEFT, =MID, etc None of these have worked because
everyone's name is a different number of characters. Any help is greatly
appreciated.
Thank you,
Aaron

try this

Sub findnuminstrall()
For Each c In Selection
For i = 1 To Len(c)
If Mid(c, i, 1) Like "*[0-9]*" Then Exit For
Next i
c.Offset(, 1) = Left(c, i - 1)
c.Offset(, 2) = Right(c, Len(c) - i + 1)
Next c
End Sub

--
Don Guillett
SalesAid Software

"Aaron" wrote in message
...
Hi Everyone,
I have thousands of cells with names and addresses, and each cell combines
both. I'd like to separate "Jane Doe 123 Street Road" into "Jane Doe" in
one
column then the rest of the address in another. I've tried text to
columns
and the formulas =LEFT, =MID, etc None of these have worked because
everyone's name is a different number of characters. Any help is greatly
appreciated.
Thank you,
Aaron

On Thu, 17 Nov 2005 09:12:05 -0800, "Aaron"
wrote:

Hi Everyone,
I have thousands of cells with names and addresses, and each cell combines
both. I'd like to separate "Jane Doe 123 Street Road" into "Jane Doe" in one
column then the rest of the address in another. I've tried text to columns
and the formulas =LEFT, =MID, etc None of these have worked because
everyone's name is a different number of characters. Any help is greatly
appreciated.
Thank you,
Aaron

Is there some consistency?

Does the address always start after the 2nd space?

Does the address always start with a number?
--ron

Thank you everyone!!

"Bob Phillips" wrote:

If it is always Name-space-name-space-address you could use

=LEFT(A1,FIND(" ",A1,FIND(" ",A1)+1)-1)

and

=RIGHT(A1,LEN(A1)-FIND(" ",A1,FIND(" ",A1)+1))

--

HTH

RP
(remove nothere from the email address if mailing direct)


"Aaron" wrote in message
...
Hi Everyone,
I have thousands of cells with names and addresses, and each cell combines
both. I'd like to separate "Jane Doe 123 Street Road" into "Jane Doe" in
one
column then the rest of the address in another. I've tried text to
columns
and the formulas =LEFT, =MID, etc None of these have worked because
everyone's name is a different number of characters. Any help is greatly
appreciated.
Thank you,
Aaron