How to Convert Text to Columns with Multiple Delimiters in Excel

Get FREE Advanced Excel Exercises with Solutions!

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.

excel text to columns multiple delimiters

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.

Using Text to Columns Feature

Immediately, the Convert Text to Columns Wizard pops out.

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

Select Delimited option

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

Check the Comma Option

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

Choose Destination

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

Click OK

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

excel text to columns multiple delimiters using Text to Columns feature

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.

=TRIM(MID(SUBSTITUTE($B6,",",REPT(" ",LEN($B6))),(C$5-1)*LEN($B6)+1,LEN($B6)))

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

Formula Breakdown:

  • LEN($B6) → returns the number of characters in a string of text. Here, the B6 cell is the text argument that yields the value 43.
    • Output43 ”                                           “
  • REPT(” “,LEN($B6)) → becomes
    • REPT(” “,43) → repeats text a given number of times. Here, the ” “ is the text argument that refers to blank space while the 43 is the number_times argument that instructs the function to insert 43 blank repeatedly.
    • Output”                                           “
  • SUBSTITUTE($B6,”,”,REPT(” “,LEN($B6))) → replaces existing text with new text in a text string. Here, the B6 refers to the text argument while Next, the “,” represents the old_text argument, and the REPT(” “,LEN($B6)) points to the new_text argument which replaces the commas with blank spaces.
    • Output“Poor Economics                                            Abhijit Banarjee                                            Economics”
  • MID(SUBSTITUTE($B6,”,”,REPT(” “,LEN($B6))),(C$5-1)*LEN($B6)+1,LEN($B6))  returns the characters from the middle of a text string, given the starting position and length. Here, the SUBSTITUTE($B6,”,”,REPT(” “,LEN($B6))) cell is the text argument, (C$5-1)*LEN($B6)+1 is the start_num argument, and LEN($B6) is the num_chars argument such that the function returns the first character from the left side.
    •  Output“Poor Economics                             “
  • TRIM(MID(SUBSTITUTE($B6,”,”,REPT(” “,LEN($B6))),(C$5-1)*LEN($B6)+1,LEN($B6))) becomes
    • TRIM(“Poor Economics                             “)  removes all but single spaces from a text. Here, the “Poor Economics                             ” cell is the text argument and the function gets rid of excess spaces after the text.
    •  Output“Poor Economics”

Utilizing TRIM, MID, SUBSTITUTE, REPT, and LEN Functions

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

Using Fill Handle Tool

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

using fill handle to convert text to columns multiple delimiters in excel

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

excel text to columns multiple delimiters with TRIM, MID, SUBSTITUTE, REPT, and LEN Functions

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.

Combining LEFT, RIGHT, MID, LEN, and FIND Functions

📌 Steps:

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

=LEFT(B5,FIND(";",B5)-1)

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

Formula Breakdown:

  • FIND(“;”,B5) → returns the starting position of one text string within another text string. Here, “;” is the find_text argument while B5 is the within_text argument. Specifically, the FIND function returns the position of the semicolon(;) character in the string of text.
    • Output7
  • LEFT(B5,FIND(“;”,B5)-1) becomes
    • LEFT(B5,7)  returns the specified number of characters from the start of a string. Here, the B5 cell is the text argument whereas 7 is the num_chars argument such that the function returns the 7 characters from the left side.
    •  OutputTrevor

Using LEFT and FIND functions

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

=MID(B5,FIND(";",B5)+1,FIND("@",B5)-FIND(";",B5)-1)

Formula Explanation:

  • FIND(“@”,B5)-FIND(“;”,B5)-1 → here, the FIND function returns the position of the semicolon(;) and the at the rate (“@”) characters within the string of text.
    • 16 – 7 – 1 → 8
  • FIND(“;”,B5)+1 → for example, the FIND function locates the the semicolon(;) characters within the string of text.
    • 7 + 1 → 8
  • MID(B5,FIND(“;”,B5)+1,FIND(“@”,B5)-FIND(“;”,B5)-1) becomes
    • MID(B5,8,8)  here, the B5 cell is the text argument, 8 is the start_num argument, and 8 is the num_chars argument such that the function returns the 8 characters after the first 8 characters.
    •  OutputIceland

Using MID and FIND functions

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

=RIGHT(B5,LEN(B5)-FIND("@",B5))

How this Formula Works:

  • LEN(B5)-FIND(“@”,B5) the LEN function returns the length of the string in the B5 cell, in contrast, the FIND function returns the position of the at the rate (“@”) character.
    •  26 – 16 → 10
  • RIGHT(B5,LEN(B5)-FIND(“@”,B5)) becomes
    • RIGHT(B5,10) returns the specified number of characters from the end of a string. Here, the B5 cell is the text argument whereas 10 is the num_chars argument such that the function returns the 10 characters from the right side.
    •  OutputReykjavik

Applying RIGHT, LEN, and FIND functions

Eventually, the results should look like the screenshot below.

excel text to columns multiple delimiters with LEFT, RIGHT, MID, LEN, and FIND Functions

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.

Employing Flash Fill

Now, Excel will autofill the rest of the cell.

Autofilled cells

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

excel text to columns multiple delimiters with Flash Fill option


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.

Using Power Query

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

Go to Data tab

Not long after, the Power Query Editor appears.

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

Split Columns by Delimiter

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

Choose semicolon option

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

Loading dataset

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

excel text to columns multiple delimiters with Power Query

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.

Applying VBA Code

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

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

Inserting 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

VBA Code for excel text to columns multiple delimiters

Code Breakdown:

Here, I will explain the VBA code used to convert text to columns with multiple delimiters. In this case, the code is divided into 2 steps.

  • In the first portion, the sub-routine is given a name, here it is Separate_Text_String().
  • Next, define the variables Arr, cnt, and j as String, Long, and Variant.
  • In the second potion, use the For Loop through each cell and split the text delimited by semicolons.
  • Now, in the code, the statement “For k = 5 To 13” represents the starting and ending row numbers of the data, here it is 5 to 13.
  • Then, the “;” in the “Arr = Split(Cells(k, 2), “;”)” is the delimiter which you can change to a comma, pipe, etc. if you wish.
  • Lastly, the “cnt = 3” indicates the third column number (Column C).

Code explanation for excel text to columns multiple delimiters

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

Running VBA code

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

excel text to columns multiple delimiters with VBA Code


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.

Practice Section


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


<< Go Back to Excel Text to ColumnsSplitting TextSplit in ExcelLearn Excel

What is ExcelDemy?

ExcelDemy - Learn Excel & Get Excel Solutions Center provides online Excel training , Excel consultancy services , free Excel tutorials, free support , and free Excel Templates for Excel professionals and businesses. Feel free to contact us with your Excel problems.
Eshrak Kader
Eshrak Kader

Eshrak Kader is a dedicated professional with a BSc. Degree in Naval Architecture and Marine Engineering from Bangladesh University of Engineering and Technology. He boasts a rich background in Microsoft Office Suite, and over the past year, he has authored over 120 insightful articles for the ExcelDemy. Currently, Eshrak leads a team of 5 in the ExcelDemy Video project and enjoys problem-solving and making videos on Excel. Eshrak’s passion for continuous learning underscores his commitment to excellence in... Read Full Bio

We will be happy to hear your thoughts

Leave a reply

Advanced Excel Exercises with Solutions PDF

 

 

ExcelDemy
Logo