Prantick Bala
Sometimes you may have a dataset where different kinds of data are inserted in a cell and separated by a character such as a delimiter, a hyphen, a 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.
Download Practice Workbook
6 Ways to Split String by Character in Excel
Let’s say, we have a dataset of 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 (,). For demonstrating simplicity we are set to split by comma. Now, we will split the string by character in Excel.
Not to mention, we have used the Microsoft 365 version. You may use any other version at your convenience.
1. Split String by Character Using LEFT and FIND Functions
Using the LEFT function and the FIND function 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 (C5).
=LEFT(B5,FIND( ",",B5)-1)Here, 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). Remember, you have to subtract 1 to extract excluding the comma.
- Press ENTER.
- Eventually, drag down the Fill Handle tool for other cells.
Finally, you will get the following output.
Read More: How to Split Text in Excel Using Formula (5 Easy Ways)
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. Type the following formula in an empty cell (C5)
=MID(B5,FIND(",",B5)+1,FIND(",",B5,FIND(",",B5)+1)-FIND(",",B5)-1)Here, 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. Finally MID gives the characters between these two commas.
- Sequentially, press ENTER. As a result, you will get the Email Address in cell C5.
- Consequently, drag it down to the end of your dataset and you will get all the Email Addresses.
Read More: How to Split Text in Excel by Character (5 Quick Methods)
3. Employing RIGHT, LEN, and FIND functions
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 (C5)
=RIGHT(B5,LEN(B5)-FIND(",",B5,FIND(",",B5)+1))Here, 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.
- Subsequently, press ENTER and you will get the Contact Number in cell C5.
- Consequently, drag cell C5 and you will get all the Contact numbers by splitting the strings of column B.
Read More: How to Split Text after a Certain Word in Excel (7 Ways)
Similar Readings
- How to Separate Words Using Formula in Excel
- Splitting Text in Excel (8 Useful Applications)
- How to Separate Two Words in Excel (6 Easy Ways)
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, type the following formula in an empty cell (B6)
=LEFT(B5,SEARCH( ",",B5)-1)Here, 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). Remember, you have to subtract 1 to exclude the comma.
Eventually, press ENTER, and you will get the Name in cell B6 and drag the cell to the end of your dataset in order to apply the formula to all other cells in column B. You will get the Names from all of the entries.
Read More: How to Split First And Last Name in Excel (6 Easy Ways)
5. Applying Flash Fill to Split String by Character
Using Flash Fill is another technique to split strings by character. First, manually input the part of the string in a cell (C5).
After that, go to the Home tab >> Fill and select Flash Fill.
Now you can see Excel has automatically given the split string in all other cells of that column.
Similarly, write the first Contact Number in cell D5 and navigate to the Home tab >> select Fill and choose Flash Fill.
Finally, you will get an autosuggestion from Excel that fills the other cells of Contact Numbers.
Read More: How to Separate Text in Excel (10 Easy Methods)
6. Engaging Text to Columns Command
You can also use the Text to Columns command to split a string by a character.
- Firstly, select the entire dataset.
- Secondly, go to the Data tab >> select Text to Columns from the Data Tools option.
- Apparently, a window named Convert Text to Columns Wizard will appear. Check Delimited and press on Next.
- In the second step, Select Comma and press on Next.
- Lastly, 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.
Now you will see, different parts of the string which were separated by the character comma, have been split into different cells.
Read More: How to Split Text by Space with Formula in Excel (5 Ways)
Practice Section
We have provided a practice section on each sheet on the right side for your practice. Please do it by yourself.
Conclusion
That’s all about today’s session. And these are some easy methods to split a string by character in Excel. Please let us know in the comments section if you have any questions or suggestions. For a better understanding please download the practice sheet. Visit our website ExcelDemy, a one-stop Excel solution provider, to find out about diverse kinds of excel methods. Thanks for your patience in reading this article.
