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: - 1^st is Formula, 2^nd is Find option and 3^rd 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.

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

 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.

Now follow the below steps:-

• Enter “&” in Find What option

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

• 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

• Click on ok

 
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

 
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