text to column

M

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?
 
T

Teethless mama

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

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

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
 
M

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?
 
G

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.
 
R

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
 
G

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
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top