text to column

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?

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

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


"Mike" wrote:

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?

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

--
Gary''s Student - gsnu200798


"Mike" wrote:

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?

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?

"Teethless mama" wrote:

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

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


"Mike" wrote:

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?

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.
--
Gary''s Student - gsnu200798


"Mike" wrote:

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?

"Teethless mama" wrote:

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

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


"Mike" wrote:

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?

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


"Gary''s Student" wrote in message
...
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.
--
Gary''s Student - gsnu200798


"Mike" wrote:

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?

"Teethless mama" wrote:

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

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


"Mike" wrote:

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?

In step 2 of DataT to C check the "treat consecutive delimiters as one"

The 4 spaces will become one space.


Gord Dibben MS Excel MVP

On Fri, 8 Aug 2008 08:09:00 -0700, Mike
wrote:

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?