How to Convert Text to Columns with Multiple Delimiters in Excel

Converting text to columns is a common task in our day-to-day lives indeed. In countless situations, we have to split texts into columns, and this is where Microsoft Excel excels.
In this article, we’ll explore all the nitty-gritty of how to convert text to columns with multiple delimiters in Excel.

Excel Text to Columns with Multiple Delimiters: 6 Methods

On this occasion, assuming the List of Best Sellers dataset shown in the B4:B13 cells, contains the Book Name, Author, and Genre column. In this scenario, we want to split the Book Name, Author, and Genre into separate columns. Hence, without further delay let’s explore each method individually.

Here, we have used the Microsoft Excel 365 version, you may use any other version according to your convenience.

Method 1: Using Text to Columns Feature

First and foremost, we’ll begin with Excel’s Text to Column feature which is a handy tool for converting text to columns with multiple delimiters. Therefore, let’s observe and learn about the procedure in the steps shown below.

📌 Steps:

• At the very beginning, select the B5:B13 cells >> go to the Data tab >> click the Text to Columns option.

Immediately, the Convert Text to Columns Wizard pops out.

• Then, choose the Delimited option >> hit the Next button.

• At this time, insert a check mark for the Comma delimiter >> press the Next button.

• In turn, enter a Destination cell according to your preference, here it is the C5 cell >> click the Finish button.

• Now, a warning may appear, in this case, click the OK button.

Subsequently, the final result should look like the screenshot given below.

Read More: How to Convert Column to Text with Delimiter in Excel

Method 2: Utilizing TRIM, MID, SUBSTITUTE, REPT, and LEN Functions

For one thing, functions are the lifeblood of an Excel spreadsheet, and here we’ll combine the TRIM, MID, SUBSTITUTE, REPT, and LEN functions to separate text with multiple delimiters into columns. Here, the LEN function returns the length of the string, and the REPT  function repeats the text. Next, the SUBSTITUTE function replaces the old text with new text while the MID function returns the characters within the text. Lastly, the TRIM function removes any additional spaces.

📌 Steps:

• First, move to the C6 cell >> enter the equation given below.

Here, the B6, and C5 cells refer to the Book Name, Author, Genre column, and the number 1.

• Second, use the Fill Handle Tool to copy the formula across the rows.

• Third, select the C6:E6 cells >> Drag the Fill Handle tool to apply the formula to the cells below.

Finally, your output should look like the picture shown below.

Read More: How to Convert Text to Columns in Excel with Multiple Spaces

Method 3: Combining LEFT, RIGHT, MID, LEN, and FIND Functions

Alternatively, we can use the combo of LEFT, RIGHT, MID, LEN, and FIND functions to split text with multiple delimiters into different columns. In this case, the FIND function searches for the numbers within the given array, and the LEN function returns the length of the text string. In turn, the MID function returns the characters from the middle of a text string whereas the LEFT and RIGHT functions extract the text from the left and right end of the string respectively.
Suppose we have the List of Clientele dataset shown in the B4:B12 cells, which contains the Name, Country, and City columns with the texts separated by semicolons. Here, we want to split the Name, Country, and City into different columns, so let’s see the process in detail.

📌 Steps:

In the first place, navigate to the C5 cell >> insert the following expression into the Formula Bar.

In the above expression, the B5 cell represents the Name, Country, and City columns.

• Afterward, jump to the D5 cell >> type in the following expression.

• Later, insert the formula below into the E5 cell.

Eventually, the results should look like the screenshot below.

Read More: How to Split Text to Columns Automatically with Formula in Excel

Method 4: Employing Flash Fill

If using complex formulas doesn’t suit you, then our next method might be the answer you’re looking for. Here, we’ll use the Flash Fill feature of Excel to convert text to columns with multiple delimiters.

📌 Steps:

• At the very beginning, manually type in the Name Trevor in the C5 cell >> in the Home tab, click the Fill drop-down >> select the Flash Fill option.

Now, Excel will autofill the rest of the cell.

Likewise, apply the technique to the Country and City columns and the final output should look like the image given below.

Method 5: Using Power Query

Admittedly, Power Query is an overlooked feature of Excel, despite its ability to organize and analyze data quickly and effectively. In the following section, we’ll discuss how we can convert text to columns with multiple delimiters with just a few clicks. So, let’s see it in action.

📌 Steps:

• First and foremost, move to the B4 cell >> hit the keyboard shortcut CTRL + T to insert an Excel Table >> press OK.

• Next, go to the Data tab >> click the From Table/Range option.

Not long after, the Power Query Editor appears.

• From this point, press the Split Column drop-down >> choose the By Delimiter option.

• Later, select the Semicolon option >> insert a check on Each occurrence of the delimiter option >> hit the OK button.

• Following this, Double-click the column headers to rename them >> press the Close & Load option to exit the Power Query window.

Finally, completing all the steps should yield the following result.

Read More: How to Use Line Break as Delimiter in Excel Text to Columns

Method 6: Applying VBA Code

If you often need to convert text to columns with multiple delimiters in Excel, then you may consider the VBA Code below. It’s simple & easy, just follow along.

📌 Steps:

• To begin with, navigate to the Developer tab >> click the Visual Basic button.

In an instant, the Visual Basic Editor opens in a new window.

• Next, go to the Insert tab >> select Module.

Now, for your ease of reference, you can copy the code from here and paste it into the window as shown below.

Sub Separate_Text_String()

Dim Arr() As String, _
cnt As Long, _
j As Variant

For k = 5 To 13
 Arr = Split(Cells(k, 2), ";")
 cnt = 3
 
 For Each j In Arr
 Cells(k, cnt) = j
 cnt = cnt + 1
 
 Next j
Next k

End Sub

• Following this, hit the Run button or the F5 key on your keyboard.

Ultimately, the results should appear in the screenshot given below.

Practice Section

We have provided a Practice section on the right side of each sheet so you can practice yourself. Please make sure to do it by yourself.

Download Practice Workbook

Conclusion

To sum up, this article shows 6 effective ways to convert text to columns with multiple delimiters in Excel. So, read the full article carefully and download the free workbook to practice. Now, we hope you find this article helpful and if you have any further queries or recommendations, please feel free to comment here.

Related Articles

• How to Use Text to Columns in Excel for Date
• How to Convert Text to Columns Without Overwriting in Excel
• [Fixed!] Excel Text to Columns Is Deleting Data
• How to Undo Text to Columns in Excel
• Excel Text to Columns Not Working
• How to Use Text to Columns Feature with Carriage Return in Excel

<< Go Back to Excel Text to Columns | Splitting Text | Split in Excel | Learn Excel