text to column

[Mike]

I have a column that contains email addresses, a space, and then a street
address which has spaces between the street number and the street name. In
some cases the street name can contain as many as 4 more spaces. I can't see
a way to tell the text to column feature to only look at the first space. I
would like to move the address to it's own column leaving the email address
in the first coulmn. There are 500 rows in the cloumn and not all of the
email addresses are the same length. What is the formula?

 

[Teethless mama]

Formula for email address:
=LEFT(A1,FIND(" ",A1)-1)

Formula for street address:
=MID(A1,FIND(" ",A1),99)

 

[Gary''s Student]

Just find the first blank.

With data in A1:

=LEFT(A1,FIND(" ",A1)) will give the text before the first blank
=MID(A1,FIND(" ",A1)+1,LEN(A1)-FIND(" ",A1)) will give the text after the
first blank

 

[Mike]

While this works, it leaves the source column and requires a cut and paste
step. Isn't there a way to simply parse this? Is there a step that could
insert a character after the email address such as a comma that I could then
use the text to cloumn feature?

 

[Gary''s Student]

If you select the cells to change and run this macro:

Sub mike()
For Each r In Selection
v = r.Value
n = InStr(1, v, " ")
v1 = Left(v, n - 1)
v2 = Right(v, Len(v) - n)
v3 = v1 & "^" & v2
r.Value = v3
Next
End Sub

It will change the first blank into a ^
You can then use Text to Columns.

 

[Rick Rothstein \(MVP - VB\)]

As long as you are running code, why not just accomplish the task in code?

Sub mike()
For Each r In Selection
v = r.Value
n = InStr(1, v, " ")
r.Value = Left(v, n - 1)
r.Offset(0,1).Value = Right(v, Len(v) - n)
Next
End Sub

Rick

 

[Gord Dibben]

In step 2 of Data>T to C check the "treat consecutive delimiters as one"

The 4 spaces will become one space.


Gord Dibben MS Excel MVP