Microsoft Excel is a powerful program. We can perform numerous tasks on datasets using Excel tools and features. There are many default Excel functions that we can use to create formulas. Many educational institutions and business companies use Excel files to store valuable data. Sometimes, we need to import data into Excel worksheets from CSV files. If we open the CSV files directly in Excel, however, the delimiters may not be recognized. As a result, we’ll see all the cell values in a single column. This article will show you 6 simple ways to Open CSV with Delimiter in Excel.
Excel acknowledges the default line separator that is used by Windows. Usually, that default delimiter is a comma (,). So, if we use commas as the delimiter in the CSV file, Excel will place the data into separate columns as desired. But the problem arises whenever there is something different than a comma as a delimiter. In such cases, all the data gets placed in a single column. In this article, we’ll use both commas and semicolons as delimiters for explaining the cases. To illustrate, we’ll use a sample dataset as an example. For instance, the below dataset is in a CSV file and we show it in a Notepad. Here, as you can see, the Salesman, Product, and Net Sales columns are separated by commas. In some methods, you’ll also see semicolons instead. Therefore, go through the methods to open CSV with a delimiter in Excel.
1. Opening CSV with Delimiter Through Excel Power Query Editor
In our first method, we’ll open the CSV file through the Power Query Editor in Excel. This is the most effective way to open CSV files as we can specify the delimiters here. So, follow the below steps to perform the task.
STEPS:
- First, go to the Data tab.
- Then, in the Get & Transform Data section, click From Text/CSV.
- As a result, the Import Data dialog box will pop out.
- Select the desired CSV file and press Import.
- Consequently, another dialog box will appear containing the dataset.
- Here, specify your delimiter.
- After that, press Load.
- Thus, you’ll get a new worksheet with the desired dataset in separate columns.
- See the below picture which is our outcome.
Read More: How to Open CSV File with Columns in Excel
2. Applying Text Import Wizard to Import CSV with Delimiter
Moreover, if the CSV file is saved as a text file, we can use the Text Import Wizard to import the data. Therefore, learn the following steps to carry out the operation.
STEPS:
- Firstly, open the .txt file using Excel.
- Accordingly, the Text Import Wizard dialog box will appear.
- In step 1, select Delimited and press Next.
- In step 2, choose your delimiter.
- Here, we choose Comma.
- Afterward, click Next.
- Lastly, click Finish.
- In this way, you can open a CSV file with delimiters in Excel.
Read More: Open CSV File in Excel Without Formatting
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. In this way, we can directly open the CSV file using Excel without using the power query. Hence, follow the below process to know how to perform the task.
STEPS:
- First of all, type sep=; in the first line of the CSV file.
- Here, semicolon is our delimiter.
- You can type your own one.
- After that, open the file as usual.
- Accordingly, you’ll see the dataset in the accurate format in Excel.
Read More: How to Open Large CSV Files in Excel
4. Opening CSV with Delimiter Using Text to Columns Feature
Again, you can use the Text to Columns Feature for delimiters other than commas. Learn the following process to see how you can get the job done.
STEPS:
- In the beginning, open the CSV file in Excel.
- You’ll see the values in a single column as demonstrated below.
- Now, select the desired column.
- Next, go to Data > Data Tools > Text to Columns.
- Thus, the wizard dialog box will emerge.
- In step 1, click Next.
- Subsequently, in step 2, choose Semicolon as the delimiter.
- Press Next.
- Similarly, in step 3, click Finish.
- As a result, it’ll return the converted dataset.
- Look at the following dataset which is our desired one.
Read More: How to View CSV File in Excel
5. Embedding VBA Code in Excel to Import CSV with Delimiter
If you don’t want to go through the hassle of opening it as we have demonstrated in the earlier methods, you can embed VBA code. So, follow the steps.
STEPS:
- Firstly, go to Developer > Visual Basic.
- The VBA window will appear.
- Next, select Insert > Module.
- As a result, the Module window will pop out.
- Copy the below code and paste it there.
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
- Then, save the file and press the F5 key to run the code.
- Consequently, a dialog will pop out.
- Select the desired CSV file and press Open.
- In this way, you can open CSV files with a delimiter in excel.
Read More: CSV File Not Opening Correctly in Excel
6. Adjusting Delimiter in Windows to Open CSV in Excel
Finally, we’ll show how you can adjust the delimiter in your windows to align it with the one you will use in CSV files. In such a way, excel will take the delimiter as default. Hence, go through the steps carefully.
STEPS:
- First of all, type in the region in the windows search bar.
- Subsequently, select Region Settings.
- A new window will appear.
- There, click Additional date, time & regional settings.
- In the next control panel, press Change date, time, or number formats.
- After that, click Additional settings.
- In the Customize Format dialog box, specify your desired Line separator.
- Notice that, it can’t be the same as the Decimal symbol.
- Then, click Apply and press OK.
- Hence, you can set the required delimiter as the default that you’ll use in the CSV files.
Read More: How to Open CSV File in Excel with Columns Automatically
Download Practice Workbook
Download the following workbook to practice by yourself.
Conclusion
Henceforth, you will be able to Open CSV with Delimiter in Excel using the above-described methods. Keep using them and let us know if you have more ways to do the task. Don’t forget to drop comments, suggestions, or queries if you have any in the comment section below.
