Split String by Character in Excel (6 Suitable Ways)

The sample dataset below contains the Contact Information of random people where Name, Email Address and Contact Number are entered as a single string and separated by a character i.e. comma (,).We will split the string by character.

Method 1 – Split String by Character Using LEFT and FIND Functions

Enter the following formula in an empty cell (C5).

=LEFT(B5,FIND( ",",B5)-1)

The FIND function returns the position of the first comma (“,”) from the string B5 and the LEFT function extracts the characters from the string which is before the specific character (first comma). It will subtract 1 to extract excluding the comma.

Press ENTER.

Drag down the Fill Handle tool for other cells.

You will get the following output.

Method 2 – Applying MID and FIND functions to Split String

If you want to get the texts between two specific characters, you can use the MID function and the FIND function together. Enter the following formula in an empty cell (C5),

=MID(B5,FIND(",",B5)+1,FIND(",",B5,FIND(",",B5)+1)-FIND(",",B5)-1)

FIND(“,”, B6)+1 returns the starting position of the first character after the first comma. FIND(“,”, B5, FIND(“,”, B5)+1) returns the starting position of the first character after the second comma. -FIND(“,”, B5)-1 indicates that all the characters of the string after the second comma will be excluded. MID gives the characters between these two commas.

Press ENTER and you will get the Email Address in cell C5.
Use the Fill Handle for the remaining cells to get all the Email Addresses.

Method 3 – Employing RIGHT, LEN, and FIND functions

The RIGHT function, the LEN function and the FIND function will split the string and extract the desired value after a specific character from that string. Enter the following formula in an empty cell (C5),

=RIGHT(B5,LEN(B5)-FIND(",",B5,FIND(",",B5)+1))

LEN(B5) calculates the total length of the string in cell B5. FIND(“,”, B5, FIND(“,”, B5)+1 finds the last comma and RIGHT extracts the characters after the last comma.

Press ENTER to get the Contact Number in cell C5.

Drag cell C5 and you will get all the Contact numbers by splitting the strings of column B.

Method 4 – Incorporating SEARCH and LEFT functions to Split String

You can use the SEARCH function instead of the FIND function to split a string.

To split the Name from the string of cell B5, enter the following formula in an empty cell (B6),

=LEFT(B5,SEARCH( ",",B5)-1)

The SEARCH function returns the position of the first comma (“,”) from the string B5 and the LEFT function extracts the characters from the string which is before the specific character (first comma). You have to subtract 1 to exclude the comma.

Press ENTER to get the Name in cell B6. Use the Fill Handle tool for the remaining cells.

Method 5 – Applying Flash Fill to Split String by Character

Enter the part of the string in a cell (C5).

Go to the Home tab >> Fill and select Flash Fill.

Excel will automatically give the split string in all other cells of that column.

Enter the first Contact Number in cell D5 and navigate to the Home tab >> select Fill and choose Flash Fill.

You will get an auto suggestion from Excel that fills the other cells of Contact Numbers.

Read More: How to Separate Two Words in Excel

Method 6 – Engaging Text to Columns Command

Select the entire dataset.
Go to the Data tab >> select Text to Columns from the Data Tools option.

Check Delimited in the Convert Text to Columns Wizard and click on Next.

Select Comma and click on Next.

If your string is separated by any other character such as Tab, Semicolon or Space, you have to select that character. You can also enter other characters in the Other Select General and click on Finish.