Find if a character is in a range in Microsoft Excel

Microsoft Excel is a very vast tool that maintains the data and prepares it as per the requirement. It is used on a very large scale, and we can use it in any of the ways that is why every day we get new questions and answers.

In this article, you will learn about how to find the character in a range in Microsoft Excel. We have 3 options: - 1st is Formula, 2nd is Find option and 3rd option is Conditional formatting.

 

Let’s find the character through formula:-

In Excel, we have number of items along with their brands, few items have multiple brands; so, in data, two brands are mentioned with the “&” sign. Now we want to search in the range whether multiple brands are there or not.

 

image 1

 

In this case, we will use SUMPRODUCT, ISNUMBER and SEARCH function.

  • Enter the formula in cell D2
  • =SUMPRODUCT(ISNUMBER(SEARCH("&",A2:A8))+0)>0
  • Press Enter
  • Function will return true, it means multiple brands are available in the range

image 2

 

 Find and Replace Option

To search the specific character, text, etc. we can use Find and Replace option as well. Press the shortcut key to open the FIND dialog box.

 

image 3

 

Now follow the below steps:-

  • Enter “&” in Find What option

image 4

 

  • Click on Find all
  • We will get the detail of the dialog box below

image 1

 

  • When we click on the link, cell will get selected

In this way, we can find the character through FIND and Replace option

 

Conditional Formatting to find a character in a range

We can find the character by using Conditional formatting option. It will find the character by highlighting the cell. Let’s understand with the simple exercise to find the character.

 

  • Select the range in which we want to find the character
  • Go to Home tab > Conditional formatting > New Rule
  • Click on “Format only cells that contain”
  • Format only cells with (Specific Text) > Select Containing > “&”
  • Click on Format > in the fill tab > choose the color
  • Click on ok

image 6

 

  • Click on ok

image 7
 
In the above image, we can see the cell which contains “&” highlighted with the green color.

Below are the more example:-

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

 

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
 
 

Users are saying about us...

  1. "I perform a search for the characters 'T10' within a named range of cells. There is no 'T10', but there is 'T100'. The formula retrns true. How do I use this formula to search for exact?
    Cheers - Pete "

  2. "Hi Peter,
    My approach would be to search for 'T10' and also search for 'T100', then use a formula to check for occurances of one without the other.
    HTH,
    Alan."

Leave a Reply

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

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.

Visit Us On TwitterVisit Us On FacebookVisit Us On Youtube