I have a list of names that shows first and last names in one column (entry
="John Doe") I want to separate these into two columns so that I have one
column with entry="John" and next column has entry="Doe". I can do this the
loooong way bt copying the first column over twice, and deleting the second
name form one column, and the first name from the other, cell by cell. This
is very tedious!
Is there a formula or macro that will recognise the space between the names,
and delete the word to right or left of the space? Obviously the names of
unpredictable length, so I can't strip out a constant number of characters.
Has anyone had this problem, and solved it?
thanks in advance for your assistance and good ideas.
G

Select all the names, use Data/Text to colulmns, choose delimited, then in
step 2 select Space, then click finish in the 3rd step!

"CHES" wrote:

I have a list of names that shows first and last names in one column (entry
="John Doe") I want to separate these into two columns so that I have one
column with entry="John" and next column has entry="Doe". I can do this the
loooong way bt copying the first column over twice, and deleting the second
name form one column, and the first name from the other, cell by cell. This
is very tedious!
Is there a formula or macro that will recognise the space between the names,
and delete the word to right or left of the space? Obviously the names of
unpredictable length, so I can't strip out a constant number of characters.
Has anyone had this problem, and solved it?
thanks in advance for your assistance and good ideas.
G

Hi,

ok names can be a real pita as you get double first names and double second
and mac with space etc.

but have you tried text to columns delimited on space from the data menu or

ok if your data is in col a copy it to col b.

do text to columns on column b

this should split it up ththen need to put some checks in
in col d
copy your data into column

d1=IF(ISERROR(FIND(" ",b1,1)),"","<<<")
e1=IF(ISERROR(FIND(" ",c1,1)),"","<<<")
that will find remaing spaces for you to check use autofilteron to get them

you may also want to have a look at '-' mac ma etc..


--
Hope this helps
Martin Fishlock, Bangkok, Thailand
Please do not forget to rate this reply.


"CHES" wrote:

I have a list of names that shows first and last names in one column (entry
="John Doe") I want to separate these into two columns so that I have one
column with entry="John" and next column has entry="Doe". I can do this the
loooong way bt copying the first column over twice, and deleting the second
name form one column, and the first name from the other, cell by cell. This
is very tedious!
Is there a formula or macro that will recognise the space between the names,
and delete the word to right or left of the space? Obviously the names of
unpredictable length, so I can't strip out a constant number of characters.
Has anyone had this problem, and solved it?
thanks in advance for your assistance and good ideas.
G

I found the answer in General Discussion (shoulda looked there first!)
My thanks to JulieD

For anyone else curious, Julie described using the DataText to Columns
function, selecting "delimited", going to the next screen and selecting
"space" instead of the auto choice of "tab", and then "finish"
Voila, it's automatic! As JulieD pointed out, if you want to keep the data
in its original format, copy and paste it into an adjacent column, as the
data you select becomes one of the two separated columns. Also make sure you
have an empty column for the second part of the separated data.

Thanks again, JulieD

Use Text to Columns

Assume your range of interest is A1:A10.
Make sure column B is empty (at least B1:B10)

Select the range A1:A10
Goto the menu DataText to Columns
DelimitedNextSpaceFinish

Biff

"CHES" wrote in message
...
I have a list of names that shows first and last names in one column (entry
="John Doe") I want to separate these into two columns so that I have one
column with entry="John" and next column has entry="Doe". I can do this
the
loooong way bt copying the first column over twice, and deleting the
second
name form one column, and the first name from the other, cell by cell.
This
is very tedious!
Is there a formula or macro that will recognise the space between the
names,
and delete the word to right or left of the space? Obviously the names of
unpredictable length, so I can't strip out a constant number of
characters.
Has anyone had this problem, and solved it?
thanks in advance for your assistance and good ideas.
G