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"

Answer 1

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.

Answer 2

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.

Answer 3

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

Tell the Wizard to use the space as the delimiter.