How do I open space-delimited file in Microsoft Excel?

Question

When i try to open space delimited file in excel, the whole content opens in a single column. Is there any way to open the space delimited file in excel so that the delimited contents are properly formatted into different columns?

My scenario is that i have a file that needs the delimiter to be some thing like '" ' (A inverted command followed by a space), because my file format is like this:

"First Name" "Gender" "Phone" 
"Tom Jones" "M" "123-123-1234"

Streetlight

More suited for superuser.com – Allin

Answer 1

Use the standard "Open" command. Then go to "Text Import Wizard" and choose that the original data type was delimited. You may need to go to a different step of the Wizard to specify that the delimiter is a space, but the Wizard does include the option of specifying what character the delimiter is.

Packston

I guess i was not clear with my question. My scenario is that i have a file that needs the delimiter to be some thing like '" ' (A inverted command followed by a space), because my file format is like this: "First Name" "Gender" "Phone" "Tom Jones" "M" "123-123-1234" – Streetlight

You can specify any delimiter you want in the Wizard. – Packston

Yes, i can as long as it is a single character. Turns out that it doesn't support more than one character. – Streetlight

Choose the quotation mark and delete the empty columns. – Packston

I don't get the deleting the empty columns part. Can you explain? – Streetlight

Select the entire column and delete it by shifting the rest of the cells to the left. – Packston

Answer 2

On the Data tab click on From Text in the Get External Data section.

Tell the Wizard to use the space as the delimiter.

Bannon

Answer 3

I think you can specify the string quotation mark(', " or none) in the wizard, in the same step where you specify the delimiter.

1. Open the CSV file with Excel 2016.
2. Look for "Data" tab and "Text in column" button.
3. In the step 1, select "Delimited".
4. In the step 2, select first "space", and then choose "string classifier" as ". Then Excel will recognise the string quoted in " " and separate in columns the data with space.
5. Change format in step 3. "Finish".

Note: be sure that all the delimiter is one space; several spaces will be divided into several columns. To avoid that, please preprocess the csv file with a text editor. Sublime Text/Notepad++ will do. To make is more clear, I always substitute the delimiter with ;.

Replace multiple (more than one) spaces with ;: find \s\s* and replace with ;. Change by need.

Commentator

Answer 4

For Excel on Mac, I had to :

1. Select column
2. Data -> Text to columns
3. Select Delimited
4. Select Delimiter

Wicklow

Answer 5

It's insane that that such a simple task is so difficult. Use google docs, it will recognise the format, and copy back.

Absolve