So far we have learned how to extract text before the first space or comma character in Excel. Likewise, here we will discuss the simple steps to extract the text before the Nth specific character in Excel. Let’s get them below!! Get an official version of ** MS Excel** from the following link: https://www.microsoft.com/en-in/microsoft-365/excel
Formulas and Syntax Explanations:
- You can use the below formula to get the text before the Nth special character.
=LEFT(B1,FIND(“#”,SUBSTITUTE(B1,” “,”#”,Nth))-1)
Function Explanations:
- LEFT Function – It extracts the first character (starting from the left side) or characters from the given string based on the number you specified.
- FIND Function – This function is used to find out the one text string inside the other.
- SUBSTITUTE Function – Using this function you can easily replace existing text with new text in a text string when you want to replace text based on its content, not position.
Practical Example:
Refer to the below example image.
- Assume that you have input data as per the below example image.
- Here you need to extract the text which is before the second special character.
- So, apply the above-given formula to the formula bar section.
- Now it will display the result as per the below image.
Wind-Up:
Here, we have explained the way to extract text before the Nth special character in Excel Office 365 with formula syntax, explanation, and clear-cut examples. Please share your feedback in the below comment box. We will assist you. To learn more, check out Geek Excel
Top comments (0)