How to Split the Text String By a Specified Character in Excel Office 365?

When you want to split a text string by a specified character, such as a hyphen, comma, you can use the FIND, LEFT, and RIGHT functions. This page describes how to split a string by a specified character in Excel. Let’s see them below!! Get an official version of ** MS Excel** from the following link: https://www.microsoft.com/en-in/microsoft-365/excel

Formula to Split the Text String:

There are three types of formulas available to split the text string by a special character.

• Use the below formula to extract all the characters to the left of the character.

=LEFT(B1,FIND(“-“,B1,1)-1)

• In case, you need to find the right side text from the given string, use the below formula.

=RIGHT(B1,LEN(B1)-FIND(“-“,B1))

Description:

• FIND – The Excel FIND function is used to ** find out the one text string ** inside the other.
• LEFT – In Excel, this function will extract digits from the numbers starting from the left side. Read more on the LEFT function.
• RIGHT – This function extracts the characters from the right side of a text string. Read more on the RIGHT Function.

Practical Examples:

Assume that you have a list of student’s names with the corresponding height in a range, and you need to extract the name and height into separate columns. Let’s follow the below steps

• First, you need to give your input range.

• Then, you need to enter the left function formula in the formula bar section to extract the names from the list.
• In the formula, the FIND function will locate the position of the first dash character (“-“) from the selected cell.
• The LEFT function will extract all the characters from the left side including the special character.
• On the FIND function, you need to subtract 1, to remove the dash character from the result of the LEFT Function.

• Similarly, you can use the RIGHT and LEN Functions instead of the LEFT function to get the student’s height from the list.

• Finally, you can get the splitter text string as per the below screenshot.

Conclusion:

Hope you understood the simple steps to split the text string by a specified character in Excel. Let me know if you have any doubts regarding this article or any other Excel/VBA topic. Click here to know more about Geek Excel.

Related Articles:

• Excel Office 365 VBA Variables Data Types ~ A Complete Guide!!
• How to Enable Wrap Text Feature in Excel Office 365?Quick Steps