uncombining columns

[Jorge]

I have a question. Someone below asked the question about combining columns
(See "Combining columns????"). I would like to know if there is a formula
that would do the same but the other way around, in other words, breaking the
content of a cell into separate cells.

The formula provided to combine was: = A1&", "&B1&", "&C1 so I guess what I
need is the opposite of what this formula does. Any thoughts?

I especifically want to break down a cell that has a name and last name in
the same cell and break it down into two cells so I can do a .CSV file and
imported into my outlook.

Thanks for the replies in advance.

 

[Pete_UK]

Do you have a comma and a space between the two names, or just a
space? Will you always have two names, or might there be a 3rd name
(or initials)?

Pete

 

[Gord Dibben]

Maybe try Data>Text to Columns>Delimited by space or whatever.


Gord Dibben MS Excel MVP

 

[Jorge]

There are sometimes one name and one last name, other times two names and a
last name and even some other times two names or a name and initial and two
last names, in any case, what I want to separate is, so far, always separated
by a comma.

Question: Using the Data>Text to Columns>Delimited by comma function would
need to be used separately for each cell right? Is there a way I can select a
column with 100 cells with names and last names and splitted easily into two
colums with names in one column and last names in the other?

Also, it hasn't been the case yet but, what if the names and last names have
no commas separating them (i.e. George Walker Bush) where I have two names
and just one last name - how could I choose to separate just the last name in
this case?

Thank you, you guys are great!

 

[Pete_UK]

You do not need to apply Data | Text-to-columns a single cell at a
time - highlight the range A1:A100 and then click on Data | Text-to-
columns and follow it through.

In the case where you only have spaces to separate names, and have
more than two names, then you can build up a formula using FIND (or
SEARCH) to look for the spaces, in conjunction with LEN and
SUBSTITUTE, and use LEFT, RIGHT or MID to extract the names.

Hope this helps.

Pete