Excel Tip > Excel Functions List > Excel Text Formulas > Find if a character is in a cell in Microsoft Excel

Find if a character is in a cell in Microsoft Excel

Fb-Button

Sometimes, we struggle to find the special character or special text in the large data of Excel. In this article, we will learn how easily we can find out the special character or text in the data.

In Excel, we have so many ways to find out the character or text in a range or data. But here, we will use Find & Search function to find out the character.

Find the character by using the FIND function along with IFERROR function
 
Find Function will help us to find the specific character, text, value within a cell or range. We use this function in Case-Sensitive.
 
image 1
 
IFERROR function will help to provide the value as output if any error occurs while performing the Find function.
 
image 2

 

Let’s take example and understand the ways to find out the characters or text.

In Excel, we have Item list with amount but some of the cells are having “?” instead of amount.

image 3

 

To find “?” follow below steps:-

  • Enter the formula in cell C2.
  • =IFERROR(FIND("?",B2)>0,"Not found")

image 4

 

  • Press Enter
  • Copy the formula (Ctrl+C) and then paste it by selecting the range C3:C8
  • The cells which are not containing “?”, function will return to “Not found” and will return to “True” for those cells which are containing “?”

image 5

 

In the above image, we can see that 2 cells are there in which we have “?” character.

 

Now we will use SEARCH function along with IFERROR function

Again, we use SEARCH function to find the character, text etc. within a range or cell. We don’t use this function in case sensitive.

image 6

To understand the procedure, we will take the same example that is mentioned above .

Now follow the below steps:-

 

  • We enter the formula in cell D2
  • =IFERROR(SEARCH("Amt",B2)>0,"Not Found")

image 7

 

  • Press Enter
  • Copy the formula (Ctrl+C) and then paste it by selecting the range D3:D8
  • The cells which are not containing “?”, function will return to “Not found” and will return to “True” for those cells which are containing “?”

image 8

 

Now we can see in the above image that we have got the same results from both the formulas.
 
Below are the more examples:-

How to count the specific character in a cell
How to retrieve the text from specific character or text

 

image 48
 
If you liked our blogs, share it with your friends on Facebook. And also you can follow us on Twitter and Facebook.

We would love to hear from you, do let us know how we can improve, complement or innovate our work and make it better for you. Write us at info@exceltip.com

 
 

Previous
Next

Comments

    • Manish Singh

      Hi Anil,

      you can use conditional formatting with formula to highlight cells contains c at 4th position. Use formula, =SEARCH("c",A1)=4. I have used A1 as an example, you can set your start cell as you want.

      Reply ↓

Leave a Reply

Your email address will not be published. Required fields are marked *

Related Excel Tips

Categories

Terms and Conditions of use

The applications/code on this site are distributed as is and without warranties or liability. In no event shall the owner of the copyrights, or the authors of the applications/code be liable for any loss of profit, any problems or any damage resulting from the use or evaluation of the applications/code.