How to take a spreadsheet that has firstname, lastname, and initial all in the same column, and turn it into two columns

Sometimes your data isn't as broken down, as granular, as you'd like.

Name

Hanson,John B

Herman,Jill

Jenson,Jim J

What you typically want, for the most power over your data, is this.

Lastname	Firstname
Hanson	John B
Herman	Jill
Jenson	Jim J

Here's how to split it automatically, not manually.

First, you need to take out that space between the comma and the first name. Select all the cells in that name column.

Choose Edit > Find and Replace. Type a comma immediately followed by a space in the Search For field and just type a comma in the Replace With field. Click More and select Current Selection Only.

Then click Replace All.

And you'll see the data looks like this.

 

Now you're ready to split it at the comma. First, be sure that you have an empty column to the right of the Name column, to put the first name and initial into. Then click on the column header to select the whole column.

Choose Data > Text to Columns.

Select the comma, but nothing else. You'll see a preview of how it will be split.

Then click OK and you'll see the actual data split.

Comments

[vcgold]

I cannot get it ~! Can you give a more detailed information?

[Andrea]

Very useful, thank you.
I've just tried it out, and it works, but I've found a simpler way:

you can avoid replacing ", " with ",", and instead use ", " as separator in the Text to Columns (choose "Other" separator and type ", ", then check the "merge delimiters" box)

[Andrea]

Very useful, thank you.
I've just tried it out, and it works, but I've found a simpler way:

you can avoid replacing ", " with ",", and instead use ", " as separator in the Text to Columns (choose "Other" separator and type ", ", then check the "merge delimiters" box)

[Andrea]

Sorry for the duplicate

[steen bondo]

Maybe off-topic, but I thourght I would show a far more advanced way of 'find and replace'. I have used this technique a few times, for renaming hundreds of pictures: http://www.labnol.org/software/tutorials/rename-multiple-files-bulk-excel-googl-docs/2481/

[Phil DE TROY]

This proposal of Andrea works properly only if the original text is perfectly formatted, if some records don't have the space they will be ignored and if others have double space, a single space will remain in front of the text. Best use the replace all, records with missing space will be ignored, run 'replace all' a second time, a third time to be sure, then convert.

[RK Hnat]

This tip came at an appropriate time where I had a immediate need to utilize this instruction. Everything worked as directed. Thanks!!

[Steve]

Thanks for pointing out such a wonderful tool! I used the tedious method of copying the column, then deleting first names, individually, in the one column, then repeating for the next column. It was tedious with 125 names, but I was NOT going to do it for a 1600-name list!

What a time-saver! Thanks for being on top of this, and contributing.

[Joe]

Great tip. Cheers!

[wow gold]

cheep wow goldworld of warcrft gold.

[EtherSaga Gold]

good, I like it.

[cheap Perfect World Gold]

I like the gold.

[buy AO credits]

But you can buy AO credits

[runescape gold]

Without thinking I buy some game gold

[Atlantica online Gold]

Have you heared about a game which you need use Atlantica online Gold to play,

[dofus kama]

Do you know dofus kama?

[Perfect World Gold]

good

[Cheap metin2 yang]

i think it is good

[Darkfall money]

Do you know the fiesta Gold, in the game you need the
fiesta money. It can help you increase your level. My friends always asked me how to
buy fiesta Gold, and I do not know he spend how much money to buy the
fiesta online gold, when I see him in order to play the game and search which the place can buy the
fiesta online money. I am happy with him.
Do you know the Darkfall Gold, in the game you need the Darkfall money. It can help you increase your level. My friends always asked me how to buy Darkfall Gold, and I do not know he spend how much money to buy the Darkfall Gold, when I see him in order to play the game and search which the place can buy the
cheap Darkfall gold. I am happy with him.

[flyff penya]

It is what I want to find out, thanks.

[secondlife money]

But the logical end case of that thought is that secondlife money about technology is never appropriate.

[aoc gold]

As a new player , you may need some game guides or information to enhance yourself.

[tWEllie]

Very good information about this post. The perfect custom written papers and the chance to buy essays about this topic is suggested by custom writing service.