How to Open CSV with Delimiter in Excel (6 Methods)

Method 1 – Opening CSV with Delimiter Through Excel Power Query Editor

The sample dataset below has been created in Notepad and includes Salesman, Product, and Net Sales columns that are separated by commas.

STEPS:

• Go to the Data tab.
• In the Get & Transform Data section, click From Text/CSV.

• The Import Data dialog box will pop out.
• Select the desired CSV file and press Import.

• Another dialog box will appear containing the dataset.
• Specify your delimiter in the dropdown menu.
• Press Load.

• A new worksheet with the desired dataset in separate columns has been created.

Read More: How to Open CSV File with Columns in Excel

Method 2 – Applying Text Import Wizard to Import CSV with Delimiter

If the CSV file is saved as a text file, we can use the Text Import Wizard to import the data.

STEPS:

• Open the .txt file using Excel.
• The Text Import Wizard dialog box will appear.
• In step 1 of 3, select Delimited as your data type and press Next.

• In step 2, choose your delimiter and click Next.

• In Step 3 of 3 click Finish.

• The dataset will be opened in an Excel worksheet.

Read More: Open CSV File in Excel Without Formatting

Method 3 – Indicating Delimiter in CSV File for Opening in Excel

If we use something else other than a comma as a delimiter, we can specify it in the CSV file.

STEPS:

• Type sep=; in the first line of the CSV file to indicate that the delimiter is a semicolon.

• The file can now be opened in Excel and is formatted correctly.

Read More: How to Open Large CSV Files in Excel

Method 4 – Opening CSV with Delimiter Using Text to Columns Feature

STEPS:

• Open the CSV file in Excel.
• The values are presented in a single column as below.

• Select the column and go to Data > Data Tools > Text to Columns.

• The wizard dialog box will emerge.
• In step 1, click Next.
• In step 2, choose Semicolon as the delimiter.
• Press Next.

• In step 3, click Finish.
• The converted dataset is returned.

Read More: How to View CSV File in Excel

Method 5 – Embedding VBA Code in Excel to Import CSV with Delimiter

STEPS:

• Go to Developer > Visual Basic.

• In the VBA window select Insert > Module.
• Copy the below code and paste it in the Module window.

Sub open_csv()
Dim st As Worksheet, file_mrf As String
Set st = ActiveWorkbook.Sheets("Sheet1")
file_mrf = Application.GetOpenFilename("Text Files (*.csv),*.csv", _
, "Provide Text or CSV File:")
With st.QueryTables.Add(Connection:="TEXT;" & file_mrf, Destination:=st.Range("B2"))
.TextFileParseType = xlDelimited
.TextFileCommaDelimiter = True
.Refresh
End With
End Sub

• Save the file and press the F5 key to run the code.
•  dialog will pop out.
• Select the desired CSV file and press Open.

• You can now open CSV files with a delimiter in excel.

Read More: CSV File Not Opening Correctly in Excel

Method 6 – Adjusting Delimiter in Windows to Open CSV in Excel

STEPS:

• Search region in the windows search bar.
• Select Region Settings.

• A new window will appear.
• Select Additional date, time & regional settings.

• In the next control panel, select Change date, time, or number formats.

• Click Additional settings.

• In the Customize Format dialog box, specify your desired Line separator.
• Note, it cannot be the Decimal symbol.
• Click Apply and press OK.
• The default delimiter has been changed.

Read More: How to Open CSV File in Excel with Columns Automatically

Download Practice Workbook

Related Articles

• How to Read CSV File in Excel
• [Solved:] Excel Is Opening CSV Files in One Column

<< Go Back to Open CSV in Excel | Import CSV to Excel | Importing Data in Excel | Learn Excel