Hi,

I have 2 columns with data I need to split up. One column contains first
and last names. I need that into 2 columns, first in one, last in another.

The other column of data has City ST Zip. There are no commas or periods.
I need that split into 3 columns.

I need help with a formula to do that.

Thank you!!!

The easiest way is to use text to columns. The only problem is that if
you have data in the columns to the right of the text you want to split
up, you need to insert blank columns. If you need the data in the order
it is currently in then you could use formulas.

I used A1 for my names and B1 for the addresses. Using columns C - G
for my new text, in C1:G1 I input:

=LEFT($A$1,FIND(" ",$A$1)-1)

D1

=RIGHT($A$1,FIND(" ",$A$1)-2)

E1

=LEFT($B$1,FIND(" ",$B$1)-1)

F1

=IF(ISERR(MID(B1,FIND(" ",B1)+1,IF(ISERR(FIND(" ",B1,FIND(" ",B1)+1)),
FIND(" ",B1),FIND(" ",B1,FIND(" ",B1)+1))-FIND("
",B1)-1)),"",MID(B1,FIND(" ",B1)+ 1,IF(ISERR(FIND(" ",B1,FIND("
",B1)+1)),FIND(" ",B1),FIND(" ",B1,FIND(" ",B1)+1))-FIND(" ",B1)-1))

G1

=RIGHT(B1,LEN(B1)-FIND("*",SUBSTITUTE(B1,"
","*",LEN(B1)-LEN(SUBSTITUTE(B1," ","")))))


HTH

Steve


--
SteveG
------------------------------------------------------------------------
SteveG's Profile: http://www.excelforum.com/member.php...fo&userid=7571
View this thread: http://www.excelforum.com/showthread...hreadid=498870

"Dan B" wrote...
I have 2 columns with data I need to split up. One column contains first
and last names. I need that into 2 columns, first in one, last in another.

Are all first and last names each single words? No middle names? No multiple
word last names? If so, you'd be better off inserting an empty column
immediately to the right of this column, selecting this column, using the
menu command Data Text to Columns, using its Delimited option, and using
Space as the field delimiter to split the column into first and last names.
If there are some middle names and some multiple word last names, it's more
difficult (e.g., handling both Jaan Van Burg and Nguyen Van Diep correctly
with one formula).

The other column of data has City ST Zip. There are no commas or periods.
I need that split into 3 columns.

If state is always a 2-character abreviation and zip code is always 5
digits,

City: =LEFT(TRIM(x),LEN(x)-9)
State: =LEFT(RIGHT(TRIM(x),8),2)
Zip: =RIGHT(TRIM(x),5)

If you have no multiple word cities, e.g., New York or Los Angeles, then you
could use Data Text to Columns for this. If you have a mixture of multiple
word city names and state names along with state abbreviations, then it gets
more difficult.

To answer your question pre se, with a formula solution.....you cannot do it
with "a" formula.....it will take 5 formulas, ie:
with your data in columns A and B, put the following formulas in columns C,
D, E, F, and G, copying down as appropriate.

In C1 put........ =MID(A1,1,FIND(" ",A1,1))
In D1 put.........=MID(A1,FIND(" ",A1,1),99)
In E1 put.........=MID(B1,1,FIND(" ",B1,1))
In F1 put.........=MID(B1,FIND(" ",B1,FIND(" ",B1,1)),LEN(B1)-6-5)
In G1 put.........=RIGHT(B1,5)

And, like Harlan says, the data must conform to a strict format, including 5
diget Zip, and no extra spaces

Vaya con Dios,
Chuck, CABGx3


"Dan B" wrote in message
...
Hi,

I have 2 columns with data I need to split up. One column contains first
and last names. I need that into 2 columns, first in one, last in
another.

The other column of data has City ST Zip. There are no commas or periods.
I need that split into 3 columns.

I need help with a formula to do that.

Thank you!!!

Thank you so much!! The suggestions in both posts worked perfectly!!



"Harlan Grove" wrote in message
...
"Dan B" wrote...
I have 2 columns with data I need to split up. One column contains first
and last names. I need that into 2 columns, first in one, last in
another.

Are all first and last names each single words? No middle names? No
multiple word last names? If so, you'd be better off inserting an empty
column immediately to the right of this column, selecting this column,
using the menu command Data Text to Columns, using its Delimited option,
and using Space as the field delimiter to split the column into first and
last names. If there are some middle names and some multiple word last
names, it's more difficult (e.g., handling both Jaan Van Burg and Nguyen
Van Diep correctly with one formula).

The other column of data has City ST Zip. There are no commas or periods.
I need that split into 3 columns.

If state is always a 2-character abreviation and zip code is always 5
digits,

City: =LEFT(TRIM(x),LEN(x)-9)
State: =LEFT(RIGHT(TRIM(x),8),2)
Zip: =RIGHT(TRIM(x),5)

If you have no multiple word cities, e.g., New York or Los Angeles, then
you could use Data Text to Columns for this. If you have a mixture of
multiple word city names and state names along with state abbreviations,
then it gets more difficult.

"CLR" wrote...
....
In C1 put........ =MID(A1,1,FIND(" ",A1,1))

This includes the space after the first name.

In D1 put.........=MID(A1,FIND(" ",A1,1),99)

This includes the space just before the last name.

In E1 put.........=MID(B1,1,FIND(" ",B1,1))

Same comment as for C1.

In F1 put.........=MID(B1,FIND(" ",B1,FIND(" ",B1,1)),LEN(B1)-6-5)

FIND(" ",B1,FIND(" ",B1,1)) always returns the position of the *first* space
in B1. Use FIND(" ",B1,FIND(" ",B1)+1). The 3rd argument to FIND is begin
search at rather than after.

Thank you Harlan......I made all those formulas up on the spur of the moment
and for all their faults, they did display the data the way the OP
requested, so I made the usual error in my haste and neglected to test,
test, TEST!.......I do apologize, and appreciate your shedding the light.

Vaya con Dios,
Chuck, CABGx3


"Harlan Grove" wrote in message
...
"CLR" wrote...
...
In C1 put........ =MID(A1,1,FIND(" ",A1,1))

This includes the space after the first name.

In D1 put.........=MID(A1,FIND(" ",A1,1),99)

This includes the space just before the last name.

In E1 put.........=MID(B1,1,FIND(" ",B1,1))

Same comment as for C1.

In F1 put.........=MID(B1,FIND(" ",B1,FIND(" ",B1,1)),LEN(B1)-6-5)

FIND(" ",B1,FIND(" ",B1,1)) always returns the position of the *first*
space
in B1. Use FIND(" ",B1,FIND(" ",B1)+1). The 3rd argument to FIND is begin
search at rather than after.