Excel Conditional Formatting If a Cell Contains Any Text

Conditional Formatting is one of the significant features of Excel. Using Conditional Formatting, we can specify cells or rows based on different criteria. In this article, we will discuss Conditional Formatting if a cell contains any text in Excel with suitable examples and proper illustrations.

Below we have shown how you can highlight cells that has blank values using the conditional formatting.

Download Practice Workbook

Download this practice workbook to exercise while you are reading this article.

2 Ways with Conditional Formatting If a Cell Contains Any Text

We will discuss 2 methods and their sub-methods here to apply conditional formatting if a cell contains any text in Excel. We used a data set of students’ names who are studying in different standards.

1. Conditional Formatting Default Rules If a Cell Has Any Text

In this section, we will apply Conditional Formatting default rules to highlight cells if it consists of any text.

1.1 Conditional Formatting of Cells with No Blanks Condition

Steps:

• First, select the whole or the relevant part of the dataset.

• Now, go to the Home tab.
• Choose Conditional Formatting from the commands.
• Select More Rules from the Highlight Cells Rules option.

• Choose Format only cells that contain the Rule Type.
• Select No Blanks from Rule Description.
• Then, click Format.

• Select the Fill color and then click OK.

• We will get a Preview of the output now.

• Finally, click OK.

Here, we see that cells are formatted as our mentioned color.

1.2 Use Specific Text as Condition to Format Cells

We can search for a cell containing specific text by applying Conditioning Formatting. For example, we will search for John here to highlight the name.

Steps:

• First, select the range.

• Go to Highlight Cells Rules from the Conditional Formatting command.
• Then choose Text that Contains.

• After that, we will get a Pop-Up window.
• Put John in the box named Format cells that contain the text.
• Also, set the color format here.

• Finally, press OK.

Here, we see those cells containing our given word are colored as our given choice.

1.3 Highlight Duplicate Cells Using Conditional Formatting

In this section, we will search if a cell contains duplicate texts. All cells containing duplicate text will be identified one time by applying this method.

Steps:

• Select the cells with data.

• We will get Duplicate Values from the Highlight Cells Rules.

• After clicking on Duplicate Values, we will get a Pop-up.
• Choose Duplicate here.
• Also, select the format color.

• Then press OK.

Here, we find that all the cells containing duplicate words are colored.

We can also specify the cells with unique words.

• Follow the previous steps and select Unique and corresponding colors.

• Finally, press OK.

Here, we see that cells with duplicate and unique words are colored as per our given color.

Similar Readings:

• Apply Conditional Formatting to the Selected Cells in Excel (6 Ways)
• Excel Conditional Formatting Based on Multiple Values of Another Cell
• Excel conditional formatting based on another cell text [5 ways]
• How to Change a Row Color Based on a Text Value in a Cell in Excel

2. Apply Formula in Conditional Formatting If Cell Consists of Any Text

In this section, we will use the formula with Conditioning Formatting.

2.1 Filter Cells with only Text Value by Setting Formula as Condition

The ISTEXT function refers to the IS function. It checks the specified value and returns TRUE or FALSE if the value is text.

Steps:

Here, we will apply Conditional Formatting to find cells with text only. We will use the ISTEXT function here.

• At first,select the cells to apply Conditional Formatting.

• Choose New Rule from Conditional Formatting.

• We will get a new window.
• Select Use a formula to determine which cells to format as Rule Type.
• Write the formula on the marked box. The formula is:

• Then, press Format.

• Therefore, we will choose a color for the Fill field.
• Then press OK.

• Now, we will get a Preview.

• Finally, press OK.

Here, we can see that cells containing any text are colored and the rest containing numeric values and blanks are not colored.

2.2 Filter Out Cell with Specific Text Using Cell Reference as Condition

Here, we will try to find out cells with specific text using text reference. We also modify the data set, and the data set will be like:

Steps:

• At first, select the cells first.

• Write down the formula on the marked box. The formula will be:

• Then, choose the color and see the Preview.

• Finally, select OK.

Here, we see those cells containing John are formatted. But this method is case-insensitive.

2.3 Highlight Cells with Specific Text Using the SEARCH Function as Condition

The SEARCH function returns the number of the character at which a specific character or text string is first found, reading from left to right.

Now, we add a row in the data set to find that word in the data set. Here, we chose John.

Steps:

• At first, select the data range.

• Now, write the SEARCH function in Conditional Formatting. The formula is:

• After that, set the Format and see the Preview.

• Then, press OK.

Now, see that all cells containing John are formatted. Also mentioned is that this method is also case-insensitive.

2.4 Deploying the FIND Function as Condition Extract Text with Case Sensitivity

The FIND function looks one text series among a big text series. It also provides the position of our desired text series from the given big text series. It looks upon each character of the given text series.

Steps:

• At first, select the data range first.

• Then write the Find function in Conditional Formatting. The formula will be:

• Then configure the Format and see the Preview.

• Finally, press OK.

• Finally, we noticed that case-mismatch words are not marked.

Conclusion

In this article, we described 2 methods to explain conditional formatting if a cell contains any text in Excel. I hope this will satisfy your needs. Please have a look at our website Exceldemy.com and give your suggestions in the comment box.

Related Articles

• Conditional Formatting Multiple Text Values in Excel (4 Easy Ways)
• Conditional Formatting for Blank Cells in Excel (2 Methods)
• Excel Conditional Formatting with Formula If Cell Contains Text
• Conditional Formatting Based On Another Cell in Excel (6 Methods)