Excel Formula to Split String by Comma (5 Examples)

In the below dataset, ID no., LastName, and Dept. are kept as a single string separated by commas. Our goal is to split the strings into 3 columns.

5 Examples to Split String by Comma with Excel Formula


Method 1 – Combining LEFT and FIND Functions to Split String by Comma

Steps:

  • Enter the following formula in 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 before the first comma. You need to minus 1 to get the data excluding the comma.

Combine LEFT and FIND Functions to Split String by Comma

  • Press ENTER. You will see ID no. in cell C5. Drag the Fill Handle to get the rest of the ID no. in the same column.

Here is the result.

Combine LEFT and FIND Functions to Split String by Comma (Result)


Method 2 – Using MID and FIND Functions to Split String 

Steps:

  • Enter the following formula in 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.

The MID returns the characters between these two commas.

Formula with MID and FIND Functions to Split String in Excel

  • Press ENTER. You will see the LastName in cell D5. Drag the Fill Handle to get the rest of the LastNames in the same column.

Here is the result.

Formula with MID and FIND Functions to Split String in Excel (Result)


Method 3 – Uniting RIGHT and FIND Functions

Steps:

  • Enter the following formula in 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.

Unite RIGHT and FIND Functions

  • Press ENTER. You will see the Dept. in cell E5. Drag the Fill Handle to get the rest of the Dept. in the same column.

Here is the result.

Unite RIGHT and FIND Functions (Result)


Method 4 – Combining TRIM, MID, SUBSTITUTE, REPT, and LEN Functions

Steps:

  • Enter 1, 2, and 3 instead of columns titles ID No., LastName, and Dept. Enter the following formula in 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.

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

  • Press ENTER. You will see ID no. in cell C5. Drag the Fill Handle to get the rest of the ID no. in the same column. Drag the Fill Handle in the right direction to get LastName and Dept.

Here is the result.

Combine TRIM, MID, SUBSTITUTE, REPT, and LEN Functions (Result)

Read More: How to Split One Cell into Two in Excel


Method 5 – Splitting a String by Comma Using the FILTERXML Function

Steps:

  • Enter the following formula in 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.

Split a String by Comma Using the FILTERXML Function in Excel

  • Press ENTER. You will see the ID no., LastName, and Dept. in cells C5, D5, and E5. Drag the Fill Handle to get the rest of the data.

Here is the result.

Split a String by Comma Using the FILTERXML Function in Excel (Result)


Download the Practice Workbook

Download the following Excel file to practice.


Related Articles

Get FREE Advanced Excel Exercises with Solutions!
Hafizul Islam
Hafizul Islam

Hafizul Islam is an excellent marine engineer who loves working with Excel and diving into VBA programming. For him, programming is like a superhero tool that saves time when dealing with data, files, and the internet. His skills go beyond the basics, including Rhino3D, Maxsurf C++, AutoCAD, Deep Neural Networks, and Machine Learning. He got his B.Sc in Naval Architecture & Marine Engineering from BUET, and now he's switched gears, working as a content developer. In this role,... Read Full Bio

2 Comments
  1. What if the string to be split, contains not exactly 3, but 1 to 3 substrings, i.e., a maximum of 2 commas?

    • Reply Lutfor Rahman Shimanto
      Lutfor Rahman Shimanto Dec 24, 2023 at 6:11 PM

      Hello JOHN

      Thanks for sharing your query. You wanted to split strings within Excel cells that do not always have 3 words separated by commas. In contrast, there can be a string like one or at most three words like the following image. Besides, we must keep the cell empty if more than 3 words mean more than 2 commas exist.

      I am delighted to inform you that I have developed two formulas. The first formula consists of the IF, LEN, SUBSTITUTE, TRIM, and MID functions. The other formula consists of the IF, LEN, SUBSTITUTE, TRANSPOSE, and FILTERXML functions.
      OUTPUT OVERVIEW:

      1. Using IF, LEN, SUBSTITUTE, TRIM, and MID functions
      Follow these steps:
      Step 1: Select cell C5, insert the given formula and drag the Fill Handle icon to E5.

      =IF(LEN($B5)-LEN(SUBSTITUTE($B5,”,”,””))>2,””,TRIM(MID(SUBSTITUTE($B5,”,”,REPT(” “,LEN($B5))),(C$4-1)*LEN($B5)+1,LEN($B5))))

      Step 2: Select range C5:E5 and drag the Fill Handle icon to cell E10.

      2. Using FILTERXML, TRANSPOSE, IF, LEN, and SUBSTITUTE functions

      I am attaching the solution workbook for better understanding. I hope these ideas will help you to reach your goal. Good luck.

      DOWNLOAD WORKBOOK

      Regards
      Lutfor Rahman Shimanto
      ExcelDemy

Leave a reply

Advanced Excel Exercises with Solutions PDF

 

 

ExcelDemy
Logo