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:
Table of Contents
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.
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.
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
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.
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.
2 Click on the Additional Settings button
3 Under the Number tab, find List Separator and simply change your desired single character separator.
4 Click on Apply, Ok.
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.
