Removing coma delimitation, inserting a space?

[WangDoodle]

I have a huge data file that must unfortunately be mandatorily downloaded in a single column format, with coma delimitation for each separate piece of data instead of a space, as follows:

08/04/2009,158,7,1849,1184,824451,403711

What I need to do is put each of the bits of data (coma separating each bit) into a separate column, so that each piece may be further processed.

Is there a simple way to do this? Would be happy just pasting the data to a new worksheet, if there was a simple way to remove the coma, create a space between each number, and automatically place each piece of data into a separate column.

Many thanks for any help.

[Trebor76]

Hi there,

The easiest way is use the Text to Columns option. To do this (and assuming you're using 2003) follow these four steps:

1. Highlight the entire range the data currently resides in
2. From the Data menu select Text to Columns
3. Ensure the Delimited radio button on the Convert Text to Column dialog is selected and then click Next
4. Ensure only the Comma checkbox on the Convert Text to Column dialog is selected (ticked) and then press Finish

HTH

Robert

[se1429]

Hi,

You can import your data into EXCEL and the Import wizard will guide you through to specify your delimiter and will split the comma delimited file into columns.

In EXCEL 2003 or previous use: File ---> Import.
In EXCEL 2007: Data Tab - Get External Data - From Text.

Regards.

[JBeaucaire]

One final tip on the TEXT TO COLUMNS or DATE IMPORT WIZARD, don't skip the final Step 3 of 3 by clicking FINISH on Step 2 (which is OK in many instances).

Your data includes a DATE column and you should be sure to use Step 3 to highlight that column, set the "Column Data Format" to Date and select the appropriate style the data is coming in as. Your example doesn't make it clear whether is MDY or DMY...as long as you tell Excel, it won't have to guess and will convert that data properly.

[WangDoodle]

Trebor, SE1429,JBeaucaire, great thanks to the 3 of you for replying to my inquiry. Each solution work well! Really appreciate it.