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.

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

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

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

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

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

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

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

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

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

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

**Download the Practice Workbook**

Download the following Excel file to practice.

## Related Articles

- How to Split a Single Cell in Half in Excel
- How to Split Cell by Delimiter Using Excel Formula
- How to Split a Cell into Two Rows in Excel

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

Hello

JOHNThanks 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, andMIDfunctions. The other formula consists of theIF,LEN,SUBSTITUTE,TRANSPOSE, andFILTERXMLfunctions.OUTPUT OVERVIEW:1. Using IF, LEN, SUBSTITUTE, TRIM, and MID functionsFollow these steps:Step 1: Select cellC5, insert the given formula and drag theFill Handleicon toE5.Step 2: Select rangeC5:E5and drag theFill Handleicon to cellE10.2. Using FILTERXML, TRANSPOSE, IF, LEN, and SUBSTITUTE functionsI am attaching the solution workbook for better understanding. I hope these ideas will help you to reach your goal. Good luck.

DOWNLOAD WORKBOOKRegards

Lutfor Rahman ShimantoExcelDemy