Excel Conditional Formatting Formula If Cell Contains Text

We work with Excel and use formulas to get our desired results. Rather than direct using formula, we can use conditional formatting to get our outcomes. In this article, we will discuss Excel conditional formatting formula if the cell contains the text. Here, we need to use a formula with conditional formatting.

Download Practice Workbook

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

2 Methods to Apply Excel Conditional Formatting with Formula If Cell Contains Text

In the data set, we have shown the names of some students according to which standard they are studying.

1. Default Rules in Conditional Formatting If Cell Consists of Text

In this section, we will highlight if the cell contains text only with the default rules of conditional formatting.

1.1 Cells with Text Only

STEPS:

• Firstly, select all the cells to find cells with text.

• Then, go to the Home tab.
• From there, select Highlight Cells Rules from the Conditional Formatting command.
• Afterward, go to More Rules.

• Next, from the New Formatting Rules select the Format only cells that contain.
• Also, from the Format only cells with select No Blanks.
• Then, click Format.

• After that, we get a new window for formatting.
• There we have different format options. We format the Fill option.
• Then, press OK.

• Consecutively, we will get a preview look after the selected formatting.

• Finally, press OK and will see that our data is formatted.

Here, we can see that the cells that contain text are marked, and the blanks are not marked.

1.2 Cells with Specific Text

Here, we will highlight the cells with specific text.

STEPS:

• Firstly, select Text that Contains from Highlights Cells Rules.

• Secondly, get a new window named Text That Contains.
• Then, put the specific text and format.

• Next, press OK.

Here, we see that the cells with specific text are newly formatted.

1.3 Cells with Duplicate Text

Here, we will highlight the given duplicate texts.

STEPS:

• Firstly, select Duplicate Values from Highlights Cells Rules.

• Secondly, get a new window named Duplicate Values.
• Then, set Duplicate and select specific color.
• Thirdly, press OK and see the difference.

• Finally, we see that the cells with duplicate values are formatted.

Similar Readings:

• Excel Conditional Formatting If a Cell Contains Any Text
• Excel conditional formatting based on another cell text [5 ways]
• Conditional Formatting Multiple Text Values in Excel (4 Easy Ways)
• How to Change a Row Color Based on a Text Value in a Cell in Excel

2. Conditional Formatting with Self-made Formula in Excel

In this section, we will apply conditional formatting with a certain formula if the cell consists of text in Excel.

2.1 Cells with Any Text

Here, we first modify the data set. It will look like this:

The ISTEXT function is a variety of IS functions. This function returns the logical value TRUE if the value argument refers to a text; otherwise, it returns FALSE.

• Syntax:

• Argument Explanation:

Value – The value that we want to test. The value argument can be a blank (empty cell), error, logical value, text, number, or reference value, or a name referring to any of these.

Let’s follow the steps below to learn the method.

STEPS:

• Firstly, select the cells first.

• Afterward, go to the Home tab.
• Then, go to Highlight Cells Rules from Conditional Formatting.
• Further, click the More Rules.

• Instantly, we will get a window.
• Also, select Use a formula to determine which cells to format.
• Then, put the formula on the marked box.

• Moreover, select Format.

• Also, select desired color from the Fill option.

• Then, select OK.
• Instantly, we will the window with Preview.
• Then, press OK.

• Finally, we can see that the cells that contain text have been colored and the rest remains unchanged.

2.2 Cells with Specific Text (Not Case-sensitive)

The ISNUMBER function is another variety of IS Function. This function checks if there any number exists. If any number exists provide TRUE, otherwise FALSE.

• Syntax:

• Argument Explanation:

Value – This value will be tested. This argument can be an empty cell, text, or number.

The SEARCH function looks for a given text series from another text series. It also gives the starting position of the given text series.

• Syntax:

• Arguments Explanation:

find_text – The text that we want to find.

within_text – The text in which we want to search for the value of the find_text argument.

start_num – The character number in the within_text argument at which we want to start searching.

Let’s follow the steps below to learn more.

STEPS:

• Firstly, we add a box to use a specific text.
• Also, we set Jeo in that box and select the cells.

• In addition, by selecting More Rules of Conditional Formatting we get a new window.
• Then, we put a new formula to find specific text. The formula is:

• Again, select the Formate as shown previously.

• Then, press OK.

• As a result, we see that cells with the mentioned text are colored.
• Now, replace the mentioned text with John to see the effect with other text.
• You can see that colored cells also change as reference changed.

2.3 Cells with Specific Text (Case-sensitive)

The FIND function locates one text string within a second text string and returns the number of the starting position of the first text string from the first character of the second text string. It always counts each character, whether single-byte or double-byte, as 1, no matter what the default language setting is.

• Syntax:

• Arguments Explanation:

find_text – The text we want to find.

within_text – The text containing the text we want to find.

start_num – It defines from which position the function will start the operation. The first searched character of the given text sample is considered to be 1.

We can use Find Function for case-sensitive issues. For this first, modify the data set and it will look like this:

STEPS:

• Here, change the formula and set the formats as shown before. The formula will be:

• Then, press OK.

• Finally, we can see that 3 cells are showing green, and the other is not colored as the case mismatch.

Conclusion

In this article, we showed Excel conditional formatting formula if contains the text. 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 with Formula for Multiple Conditions in Excel
• Excel Conditional Formatting Formula
• Excel Conditional Formatting Based on Multiple Values of Another Cell
• How to Do Conditional Formatting Based On Another Cell Range in Excel