Split String by Character in Excel (6 suitable Ways)

Sometimes you may have a dataset where different kinds of data are inserted in a cell and separated by a character such as delimiter, hyphen, dash, etc. This kind of dataset is disorganized and finding out any particular information is quite tough. In this article, I will show you 6 ways by which you will be able to split string by character in Excel

Let’s say, we have a dataset where Name, Email Address, and Contact Number are entered as a single string and separated by a character i.e. comma (,). For demonstrating you with simplicity we are set to split by comma.

dataset

Download Practice Workbook

6 Ways to Split String by Character in Excel

1. Split String by Character Using LEFT and FIND Functions

Using the LEFT functions and the FIND functions together allows us to split a string by a character from the left side of the string. Type the following formula in an empty cell (B6)

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

Here, the FIND function returns the position of the first comma (“,”) from the string A6 and the LEFT function extracts the characters from the string which is before the specific character (first comma). Remember, you have to subtract 1 to extract excluding the comma.

LEFT and FIND

Press ENTER and you will get the Name in cell B6.

left and find

Drag the cell to the end of your dataset in order to apply the formula to all other cells in column A. You will get the Names from all of the entries.

split string by character

2. 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. Type the following formula in an empty cell (C6)

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

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

MID, FIND

Press ENTER. As a result, you will get the Email Address in cell C6.

split string by character

Drag the B7 cell to the end of your dataset and you will get all the Email Addresses.

split string by character

3. RIGHT, LEN and FIND functions to Split String by Character

By using the RIGHT function, the LEN function, and the FIND function altogether, you can split the string and get the right part after a specific character from that string. Type the following formula in an empty cell (D6)

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

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

RIGHT FIND

Press ENTER and you will get the Contact Number in cell D6.

LEN

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

split string by character


Similar Readings:


4. 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 A6, type the following formula in an empty cell (B6)

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

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

LEFT

Press ENTER and you will get the Name in cell B6.

SEARCH

Drag the cell to the end of your dataset in order to apply the formula to all other cells in column A. You will get the Names from all of the entries.

split string by character

5. Flash Fill to Split String by Character

Using Flash Fill is another technique to split string by character. First, manually input the part of the string in a cell (C6)

FIRST DATA

After that, go to Data > Data Tools and select Flash Fill.

Flash fill

Now you can see Excel has automatically given the split string in all other cells of that column.

split string by character

Read more: Splitting Text in Excel Using Flash Fill

6. Text to Columns Command

You can also use the Text to Columns command to split a string by a character. First, select the dataset.

data selection

After that, go to Data > Data Tools and select Text to Columns.

Text to columns

Now a window named Convert Text to Columns Wizard will appear. Check Delimited and press on Next.

Convert text to column

In the second step, Select Comma and press on Next.

convert text to column

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 box. In the last step, select General and click on Finish.

convert text to column

Now you will see, different parts of the string which were separated by the character comma, have been split into different cells.

split string by character

Conclusion

By following any of the above described methods, you can split string by character. If you face any kind of confusion please leave a comment and give us the chance to remove your confusion.


Further Readings

Prantick

Prantick

Hi, I'm Prantick Bala, currently working and doing research on Microsoft Excel in Softeko.com. I have completed BSc in Naval Architecture and Marine Engineering From Bangladesh University of Engineering and Technology (BUET). Currently I am in the last semester of my MBA at Institute of Business Administration (IBA), Dhaka University (DU). I'm always eager to learn through experience and have a deep interest in solving problem to get optimum solution.

We will be happy to hear your thoughts

Leave a reply

ExcelDemy
Logo