Sometimes, you need to find the word from a cell that contains a specific character such as @, comma, slash, etc. To solve this problem, you may need some quick tricks. Here we will explain how to extract the word that contains a 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:
- Use the below formula to get the word that containing a specific character in Excel.
=TRIM(MID(SUBSTITUTE(B1,” “,REPT(” “,255)),FIND(“symbol”,SUBSTITUTE(B1,” “,REPT(” “,255)))-100,255))
Function Explanations:
- TRIM Function – This function will help to remove the extra spaces from the text and there is no space at the start or end of the text string.
- MID Function – It is used to extract digits from the given number as well.
- SUBSTITUTE Function – Using this function, you can replace existing text with new text in a text string when you want to replace text based on its content, not position.
- REPT Function – This function helps to repeat characters a given number of times.
- FIND Function – Helps to find out the one text string inside the other.
Practical Example:
Assume that you have input data as per the below image. Here, you need to extract all the mail id specified by the @ symbol. Let’s see how could we solve this job in Excel.
- First, you need to enter the input data.
- Apply the above-given formula into a blank cell where you want to get the results.
- Then press ENTER key to get the results.
- After that, drag the formula cell, and you will get the following result.
Verdict:
Hope you understood how to extract the word from the text string that contains a special character in Excel. Make use of this. Please state your query in the below comment box. I reply to queries frequently. Thanks for visiting Geek Excel!! Keep Learning!!
Top comments (0)