EXCEL, convert 1 column to two column

**OLDSALTY** · 16th January 2006

Office 2003, Excel
I have one column with first name and last name, I want to convert this to two columns one with first name and the other last name.

Welshjim · 16th January 2006

OLD SALTY--I suggest you go into one or the other of these duplicate posts,
http://www.windowsbbs.com/showthread.php?t=51070
click the Edit button, wipe out the message and perhaps replace with OOPS, or Duplicate Post. That way all the answers will be collected in one thread.

I think you can also change the title to Duplicate Post or Test, etc.

**PeteC** · 16th January 2006

Dupe deleted.

Old Salty - guess the only way you will achieve that is to create a new column and cut and paste either the first name or last name across to the new column - unless someone else knows better

**Christer** · 16th January 2006

A bit of work but it does the trick:

Copy the cells in the column (not the whole column) to a new excel document.

Edit > Replace: "a space" with "a comma" (Old Salty becomes Old,Salty).

Save as: *.csv

When you close the excel document, do not save a second time.

Open Excel and open the file you just saved as *.csv. (In the open dialog, you have to select "all files".) The import dialog will ask for the "separator" which is "comma". Follow the prompts and finish the import.

Now, you should have two columns to copy and paste into the original document.

Christer

**OLDSALTY** · 16th January 2006

Quote:

Originally Posted by Welshjim

OLD SALTY--I suggest you go into one or the other of these duplicate posts,
http://www.windowsbbs.com/showthread.php?t=51070
click the Edit button, wipe out the message and perhaps replace with OOPS, or Duplicate Post. That way all the answers will be collected in one thread.

I think you can also change the title to Duplicate Post or Test, etc.

Jim I don't understand. The link goes nowhere.

**OLDSALTY** · 16th January 2006

I got the answer after a lot of reading in help. It seems I have to select the column and then Data on the menu and then text to columns. I tried it and I have what I wanted. This is a neet thing.
Thanks to all.

**Christer** · 16th January 2006

Nice and simple and a good find!

I import a lot of text files (data in lines) into Excel and my method is time consuming. I will try to import the whole text into a single column and do "text to columns" instead.

Christer

Royalty · 17th January 2006

Text to columns DOES work great, but you need to have a delimiter that can be recognized, or use a fixed length (if the text you are separating is the same length in each record. If you opt to use a delimiter it can be just about any character, "space", "tab", "symbol"(?, %, !, $, etc). You can also parse the text into more than two columns, as long as it runs into a delimiter or enough characters for the fixed length option. You can optionally elect to overwrite data already in the columns to the right (where you want the parsed out text to end up), or just insert enough blank columns for the text you are parsing out.

Another handy thing about this function is that you can define each new column you create from this function as text or general, which in effect, will allow you to convert numbers into text and/or text into numbers for a whole column without having to recalculate each cell.