Open CSV files in Excel with the correct seperator/delimiter


👤 Diwas Poudel    🕒 May 8 2022    📁 TECH

You might have noticed that when you open your delimited CSV file in Excel, some of the data is not properly arranged or aligned with the excel columns. Excel simply splits the data based on what is set by default in the Excel Settings. It would be preferable if Excel allowed you to select separators, formats, and encoding when opening a CSV file and let the user decide which setting to use.

Also, Microsoft decided to use different separators based on localization, which irritates people who work on an international basis. In some countries, a comma is used as a separator, while in others, a semicolon is used as a separator.

Suppose you CSV file with the data as shown below:

1|2|3|4|5

Now you expect that each pipe(|) separated value will be in a separate column in excel but when you open this CSV file in Excel 

There are various methods of solving this:

Method 1 : Using SEP Syntax 

We can use SEP=<Delimitor> on the first line of a CSV file, and then when you open the same CSV file in Excel, Excel will format the data based on the Delimiter present in .

If you have SEP=| then excel will separate each data into columns on the basis of a pipe(|).

Example:

SEP=|
COl1|col2|col3|col4|col5

1|2|3|4|5

When you open this CSV file in Excel, it will scan line by line and on the first line it finds SEP syntax with a pipe(|) as a delimiter. Excel then gets the idea to break the data into columns based on the pipe(|), so it breaks each data into a column based on the semicolon.

opening-csv-file-in-excel-with-proper-format-using-SEP-syntax
fig. Opening CSV File in proper format in excel using SEP syntax
Note: You can use any single character (, | # @ ' *) etc as delimiters. 

Also, keep in mind that SEP syntax is only a portion of the CSV file, and Excel will not be displayed or written in the spreadsheets regardless of the format you specify in SEP.

When you use this method, Excel will override the system setting ("list separator character") and open the file correctly.

Method 2 : Using Text/CSV settings

This is one of my best approaches for splitting all your data on the basis of desire delimiters.

FYI: Tested successfully in Excel 2016 and Excel 2019

The steps are as follows:

1. First open the Empty Excel File 

2 Click on the Data tab

3 Under Get & Transform Data, select From Text/CSV option

from-text-csv-option
fig. From Text/CSV Option

4 Then Select your desired CSV file

5 Under the Delimiter dropdown select your desired delimiter. If you did not find your desire delimiter then select 'Custom' from the list and put your desire delimiter on the text box.

6 Then click on Transform Data.

custom-settings-delimiter
fig. Select your desire delimiter, if not exists then select custom option and set the new delimiter

You will now receive all of the data, which will be divided according to the delimiter you specified.

Method 3 : Using Regional Settings of Computer

You can make changes to the Excel formatting of the data by changing the regional settings of your computer, because Excel makes use of the settings that are present in the Regional Settings of your computer.

Steps to Change Regional Settings

1 Goto run and type "intl.cpl" and press Enter.

This will open the regional setting of your computer.

intl-cpl
fig. Regional and language Settings

2 Click on the Additional Settings button

select-additional-settings
fig. select 'Additional Settings'

3 Under the Number tab, find List Separator and simply change your desired single character separator.

4 Click on Apply, Ok.

select-list-separator
fig. select desire list separator

5 Now reopen the CSV file with Excel.

If this does not work then go forward and perform additional actions like :

Open Excel then clicks on File > Option >Advanced then Make sure "Use system separators" is checked.

Tested on Windows 8, Windows 10 and Windows 11.

Method 4 : Using Text to Column Excel Settings

Text to Columns helps to split the single column into multiple columns on the basis of the delimiter you have selected.

1 Open CSV file in Excel

2 Click on Data Tab located at the title bar.

3 Select the Desire column you want to split.

4 Then Under Data Tools, Tab to 'Text to Columns

5 Choose the Delimited checkbox and then click on Next 

6 Click on the desired delimiter ( Tab, Semicolon, Comma, Space) from the list, if this is not your desired delimiter then you can manually set the delimiter by checking other options. 

7 Click on Next and then the Finish button.

Conclusion:

You can open CSV delimited data in Excel using any of these four methods.

👊 MORE FROM OURTECHROOM 👊



Ourtechroom Main Logo
Hello and thank you for visiting Ourtechroom.com! We'll talk about everything from how to troubleshoot tech issues, to emerging technology, games, gadgets, and fun facts about technology.
Contact : ourtechroom777@gmail.com