wnat to extract first & last names in sep cols from name list in 1

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

CHES - 08 Mar 2007 02:54 GMT

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

Reply to this Message

Bob Umlas, Excel MVP - 08 Mar 2007 03:19 GMT

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

> 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
[quoted text clipped - 8 lines]
> thanks in advance for your assistance and good ideas.
> G

Reply to this Message

Martin Fishlock - 08 Mar 2007 03:24 GMT

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

Signature

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

Reply to this Message

CHES - 08 Mar 2007 03:26 GMT

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

For anyone else curious, Julie described using the Data>Text 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

Reply to this Message

T. Valko - 08 Mar 2007 03:27 GMT

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 Data>Text to Columns
Delimited>Next>Space>Finish

Biff

>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
[quoted text clipped - 13 lines]
> thanks in advance for your assistance and good ideas.
> G

Reply to this Message