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.

**Table of Contents**hide

## 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,

### 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,

### 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,

### 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,

### 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,

## 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.