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.
Table of Contents hide
Download Practice Workbook
6 Ways to Convert Text to Columns with Multiple Delimiters in Excel
Method 1: Using Text to Columns Feature
Method 2: Utilizing TRIM, MID, SUBSTITUTE, REPT, and LEN Functions
Method 3: Combining LEFT, RIGHT, MID, LEN, and FIND Functions
Method 4: Employing Flash Fill
Method 5: Using Power Query
Method 6: Applying VBA Code
Practice Section
Conclusion
Related Articles
Download Practice Workbook
Converting Text to Columns with Multiple Delimiters.xlsm
6 Ways to Convert Text to Columns with Multiple Delimiters in Excel
On this occasion, assuming the List of Best Sellers dataset shown in the B4:B13 cells, containing 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 Use Text to Columns Feature with Carriage Return 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.
- Output → 43 ” “
- 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”
- 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 Split Text to Columns Automatically with Formula in Excel
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.
=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.
- Output → 7
- 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.
- Output → Trevor
- 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.
- Output → Iceland
- 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.
- Output → Reykjavik
Eventually, the results should look like the screenshot below.
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 apply 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 VariantFor k = 5 To 13 Arr = Split(Cells(k, 2), ";") cnt = 3 For Each j In Arr Cells(k, cnt) = j cnt = cnt + 1 Next jNext kEnd Sub
⚡ 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 Lop 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).
- 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.
Conclusion
To sum up, this article shows 6 effective ways how 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. Finally, visit ExcelDemy for many more articles like this.
Related Articles
- How to Use Text to Columns in Excel for Date (With Easy Steps)
- [Fixed!] Excel Text to Columns Is Deleting Data
- How to Convert Text to Columns Without Overwriting in Excel