Hafiz Islam
You may have a table in your Excel worksheet where several kinds of data are kept in a cell and separated by commas. If you want to split them into several columns, you are in a right place. In this article, you will learn 5 examples of Excel formula to split string by comma.
Download Practice Workbook
Download the following Excel file for your practice.
5 Examples to Split String by Comma with Excel Formula
Let’s introduce first our dataset where ID no., LastName, and Dept. are kept as a single string separated by commas. Our goal is to split the strings into 3 columns.
1. Combine LEFT and FIND Functions to Split String by Comma
Combining LEFT and FIND functions together helps us to split a string separated by commas into several columns. Just follow the steps below to do this.
Steps:
- First, write down the following formula in an empty cell C5.
=LEFT(B5,FIND(",",B5)-1)Here, the FIND function gives the location of the first comma from the string B5 and the LEFT function returns the characters from the string which is before the first comma. You need to minus 1 to get the data excluding the comma.
- Press ENTER. You will see ID no. at Cell C5. Now, drag the Fill Handle to get the rest of the ID no. in the same column.
Here is the result,
Read More: VBA to Split String into Multiple Columns in Excel (2 Ways)
2. Formula with MID and FIND Functions to Split String in Excel
Combining MID and FIND functions together helps us to split a string separated by commas into several columns. Just follow the steps below to do this.
Steps:
- First, write down the following formula in an empty cell D5.
=MID(B5,FIND(",",B5)+1,FIND(",",B5,FIND(",",B5)+1)-FIND(",",B5)-1)Here, FIND(“,”,B5)+1 gives the starting location of the 1st character after the 1st comma.
FIND(“,”, B5, FIND(“,”, B5)+1) gives the starting location of the 1st character after the 2nd comma.
-FIND(“,”, B5)-1 excludes all the characters of the string after the 2nd comma.
Finally, the MID returns the characters between these two commas.
- Press ENTER. You will see the LastName at Cell D5. Now, drag the Fill Handle to get the rest of the LastNames in the same column.
Here is the result,
Read More: Excel VBA: Split String by Character (6 Useful Examples)
Similar Readings
- How to Split Cells in Excel (5 Easy Tricks)
- Excel VBA: Split String into Rows (6 Ideal Examples)
- How to Split a Cell into Two Rows in Excel (3 ways)
3. Unite RIGHT and FIND Functions
Combining RIGHT and FIND functions together helps us to split a string separated by commas into several columns. Just follow the steps below to do this.
Steps:
- First, write down the following formula in an empty cell E5.
=RIGHT(B5,LEN(B5)-FIND(",",B5,FIND(",",B5)+1))Here, LEN(B5) determines the length of the string in cell B5.
The FIND(“,”, B5, FIND(“,”, B5)+1 gives the location of the last comma from the string, and finally, the RIGHT function returns the characters from the string which is after the last comma.
- Press ENTER. You will see the Dept. at Cell E5. Now, drag the Fill Handle to get the rest of the Dept. in the same column.
Here is the result,
Read More: Excel VBA: Split String by Number of Characters (2 Easy Methods)
4. Combine TRIM, MID, SUBSTITUTE, REPT, and LEN Functions
Combining TRIM, MID, SUBSTITUTE, REPT, and LEN functions together helps us to split a string separated by commas into several columns. Just follow the steps below to do this.
Steps:
- First, enter 1, 2, and 3 instead of columns titles ID No., LastName, and Dept. Now, write down the following formula in an empty cell C5.
=TRIM(MID(SUBSTITUTE($B5,",",REPT(" ",LEN($B5))),(C$4-1)*LEN($B5)+1,LEN($B5)))The summary of this formula is to replace the commas with spaces using SUBSTITUTE and REPT functions. Then, the MID function returns text related to the nth occurrence and finally, the TRIM function helps to get rid of the extra spaces.
- Press ENTER. You will see ID no. at Cell C5. Now, drag the Fill Handle to get the rest of the ID no. in the same column. And drag the Fill Handle in the right direction to get LastName and Dept.
Here is the result,
Read More: Excel VBA: Split String into Cells (4 Useful Applications)
5. Split a String by Comma Using the FILTERXML Function in Excel
Using FILTERXML function helps us to split a string separated by commas into several columns. Just follow the steps below to do this.
Steps:
- First, write down the following formula in an empty cell C5.
=TRANSPOSE(FILTERXML("<t><s>" &SUBSTITUTE(B5,",","</s><s>") & "</s></t>","//s"))If you are using Excel for MS 365, you can apply the FILTERXML function to split a string by commas. In this method, firstly the text string turns into an XML string by changing commas to XML tags. The TRANSPOSE function turns the array to lay horizontally instead of vertically.
- Press ENTER. You will see the ID no., LastName, and Dept. at Cell C5, D5, and E5 respectively. Now, drag the Fill Handle to get the rest of the data.
Here is the result,
Read More: Excel Formula to Split: 8 Examples
Conclusion
In this tutorial, I have discussed 5 examples of Excel formulas to split strings by comma. I hope you found this article helpful. You can visit our website ExcelDemy to learn more Excel-related content. Please, drop comments, suggestions, or queries if you have any in the comment section below.
