This article illustrates how to import text file data with multiple delimiters into an Excel worksheet using 3 different methods. We’ll use Excel’s built-in features and VBA code to accomplish the task. Let’s dive into the examples to get a clear understanding of the methods.
Download Practice Workbook
Download this practice workbook to exercise while you are reading this article.
3 Suitable Methods to Import a Text File with Multiple Delimiters into Excel
Let’s say we have a dataset with multiple delimiters in a text file. The dataset in the text file looks like this-
The dataset represents sale details for a shop. You can download the text file for practice from the link. Here, each of the data is separated by either a comma or a semicolon. We want to import this data from the text file to an Excel spreadsheet.
1. Import Text File Data with Multiple Delimiters Using the Legacy Wizards Feature
The legacy wizard’s feature to import text files facilitates us with a bunch of options to set the desired structure of the imported dataset in the Excel worksheet.
Go to the Legacy Wizards
- Go to the Data tab from the Excel Ribbon.
- Click on the Get Data option.
- Hover the mouse on the Legacy Wizards option.
- Choose the From Text (Legacy) option.
If the From Text (Legacy) Option is not Available
In case the From Text (Legacy) option is not available in the Legacy Wizards options, do the following to enable it.
- Press Alt + T + O on the keyboard to open the Excel Options.
- Click on the Data tab.
- Check the box named “From Text (Legacy)” under the “Show legacy data import wizards” options.
- Finally hit OK to save the settings.
Import the Text File
As we’ve clicked on the From Text (Legacy) option from the Legacy Wizards, it opened the “Import Text File” window to choose the text file. Navigate to the file location and choose to import it.
Configure the Settings
Step 1 of 3:
- Select the Delimited option.
- Check the “My data has headers” option, as the sample dataset, we’re using has a header.
- Click the Next button.
Step 2 of 3:
- Check the Semicolon and Comma options under the Delimiters options.
Note: Our dataset has these two delimiters. There are some more options including the “Other” option for delimiters that are not available in the list.
- Also, check the “Treat consecutive delimiters as one” option.
- Click the Next button.
Step 3 of 3:
- By default, the column data format is General. In this example, we’ll leave it as it is.
- Finally hit the Finish button.
Choose the Target
After all the above steps, choose where to put your imported data. Here, we’ve chosen cell A1 in the current worksheet.
Imported Dataset
Read More: How to Import Data into Excel from Another Excel File (2 Ways)
2. Prepare and Then Import Text File Data with Multiple Delimiters Using the Get and Transform Data Feature
In this illustration, we’ll use Excel’s get and transform data feature to import data with multiple delimiters from a text file to an Excel worksheet. But this time we need to prepare the data before importing it to Excel.
Prepare the Dataset
We need to reduce multiple delimiters to one delimiter to work with the get and transform feature. For our dataset, we need to replace all the semicolons with commas. To do that, we’ve done the following.
- Created a copy of the original text file named SampleDataModified.txt.
- Opened the file and pressed Ctrl + H.
- In the Replace window put a semicolon in the “Find what” input box and a comma in the “Replace with” input box.
- Click on the Replace All button.
- We now have only commas as delimiters in our dataset.
Import the Text File
Follow the simple steps to import the modified text file using the get and transform feature in Excel.
- Go to the Data tab.
- Click on the Get Data button.
- Hover on the From File option.
- Click on the From Text/CSV option.
- Navigate to the file location of the SampleTextModified.txt and select the file to import.
- In the preview, the dataset is shown as a table.
- Click on the Load button.
Imported Dataset
Read More: How to Import Data from Text File into Excel (3 Methods)
Similar Readings
- Import Data from Excel into Word Automatically Using VBA (2 Ways)
- How to Extract Data from a List Using Excel Formula (5 Methods)
- Excel Macro: Extract Data from Multiple Excel Files (4 Methods)
- How to Extract Year from Date in Excel (3 Ways)
- How to Extract Month from Date in Excel (5 Quick Ways)
3. Run a VBA Code to Import Text File Data with Multiple Delimiters into Excel Worksheet
We’re going to use the VBA Split and InStr functions to configure the VBA code to import text files to an Excel worksheet. Let’s introduce the functions first.
VBA Split Function: The Split function in Excel VBA is used to split a string into substrings. The function returns a zero-based one-dimensional array. Each element of the array is a substring separated by a predefined delimiter. The syntax of the VBA function is-
Split(expression, [delimiter, [limit, [compare]]])
Here,
expression– This required parameter represents a text string that contains substrings and delimiter. If the string is empty, the function will also return an empty array.
delimiter– A string character that is used to split the string into substrings. If omitted the function will use a space character as the delimiter. And if it is an empty string, it’ll return the original string as the output.
limit– It represents the number of substrings to return in the output. If omitted, the function will return all the substrings.
compare– It has several values. We can use vbBinaryCompare for a case–sensitive delimiter and vbTextCompare for a case-insensitive delimiter in the Split function.
VBA InStr Function: We use the InStr function in Excel VBA to search a specific string within a given string from a predefined position. The syntax is-
InStr([start], string 1, string 2, [compare])
Here,
[start]- The position from which it starts searching. The default is 1 if omitted.
string 1- The given string from which the function searches for the desired string.
string 2- The specific string that the function searches within the given string.
[compare]- The type of comparison. The default is Binary Comparison.
To import text file data with multiple delimiters into an Excel worksheet, we need to open and write VBA code in the visual basic editor. Follow the steps to open the visual basic editor and write some code there.
- Go to the Developer tab from the Excel Ribbon.
- Click the Visual Basic option.
- In the Visual Basic For Applications window, click the Insert dropdown to select the New Module option.
Now put your code inside the visual code editor and press F5 to run it. The following code replaced the semicolons of the text file with commas by using the VBA Replace function.
Sub ImportTextFileDatatoExcel()
Dim fileLocation As String, textData As String
Dim rowNum As Long
folderLocation = "D:\Exceldemy"
Set fso = CreateObject("Scripting.FileSystemObject")
Set folder = fso.GetFolder(folderLocation)
rowNum = 1
Close #1
For Each textFile In folder.Files
fileLocation = folder & "\" & textFile.Name
Open fileLocation For Input As #1
Do While Not EOF(1)
Line Input #1, textData
textData = Replace(textData, ";", ",")
If InStr(textData, ",") = 0 Then
Cells(rowNum, 1) = textData
Else
tArray = Split(textData, ",")
nColumn = 1
For Each element In tArray
Cells(rowNum, nColumn) = element
nColumn = nColumn + 1
Next element
End If
rowNum = rowNum + 1
Loop
Close #1
Next textFile
End Sub
In the above code, put your own folder location that holds the text file in the code. The following screenshot shows the file location for this illustration.
Imported Dataset
Read More: Convert Excel to Text File with Delimiter (2 Easy Approaches)
Notes
- The VBA code we used can work with multiple text files in the destined folder efficiently.
- If the specified delimiter doesn’t exist in the source string, the Split function will return the string as it is.
- If the compare argument of the Split function is omitted, the default value is vbBinaryCompare.
- The InStrRev function returns 0 if the substring doesn’t exist in the given string.
Conclusion
Now, we know how to import text file data into Excel with multiple delimiters in Excel with the help of suitable examples. Hopefully, it would help you to use the functionality more confidently. Any questions or suggestions don’t forget to put them in the comment box below.
Related Articles
- Extract Filtered Data in Excel to Another Sheet (4 Methods)
- How to Extract Data from Excel Sheet (6 Effective Methods)
- Extract Data from Image into Excel (With Quick Steps)
- How to Convert Notepad to Excel with Columns (5 Methods)
- Excel VBA: Pull Data Automatically from a Website (2 Methods)
- VBA Code to Convert Text File to Excel (7 Methods)