Copy comma delimited values into two excel cells

Question

I have comma delimited values that look like :

17  ab  2880662,2880663
15  ab  2877244,2877247
21  ab  2874201
21  ab  2873876

When I copy this to Excel it should split the comma-delimited values into two cells, like this:

17  ab  2880662    2880663
15  ab  2877244    2877247
21  ab  2874201    (empty)
21  ab  2873876    (empty)

When I use copy as a text it doesn't work, so I need a genius idea please :)

Answer 1

1. Paste the entire text into the first cell
2. click on the icon that appears on the bottom of the paste and choose "use text import wizard"
3. In step two of the wizard, choose both spaces and commas as delimiters

You should get exactly what you want. I tried this using your text and it worked. Not sure if Excel 2003 has this, but subsequent versions do.

Step 2:

Step 3:

Answer 2

You could first select a cell containing some text then choose:

Data > Text To Columns > Delimited > Next : Comma Check.

then just click Cancel to store these settings. If you copy and paste comma separated text from notepad now it should separate out into columns as required.

Note: with the default settings (Number format set to general) Excel tries to interpret some data values such as dates and numbers depending on your local settings. For example copying across "3/7" may be interpreted as an American or European date, or when copying numeric codes across leading zeros may be lost. To avoid any unwanted conversions, you can set the Number Format for the columns that data is going to be copied to as Text.

Answer 3

Save your data in a plain text editor like notpad as myfile.csv and open it with excel. There you can set the delimiter to comma.