Excel Conditional Formatting with 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.

Data set for Excel Conditional Formatting with Formula If Cell Contains Text

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

Step 1:

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

Step 2:

  • Now, go to the Home tab.
  • Select Highlight Cells Rules from the Conditional Formatting command.
  • Then, go to More Rules.

Default Rules in Conditional Formatting if Cell Consists of Text

Step 3:

  • From the New Formatting Rules select the Format only cells that contain.
  • From the Format only cells with select No Blanks.
  • Then click the Format.

Default Rules in Conditional Formatting if Cell Consists of Text

Step 4:

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

Step 5:

  • We will get a preview look after the selected formatting.

Default Rules in Conditional Formatting if Cell Consists of Text

Step 6:

  • Now, 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.

Step 1:

  • Select Text that Contains from Highlights Cells Rules.

Default Rules in Conditional Formatting if Cell Consists of Text

Step 2:

  • Now, get a new window named Text That Contains.
  • Put the specific text and format.

Default Rules in Conditional Formatting if Cell Consists of Text

Step 3:

  • Now, press OK.

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


1.3 Cells with Duplicate Text

Here, we will highlight the given duplicate texts.

Step 1:

  • Select Duplicate Values from Highlights Cells Rules.

Default Rules in Conditional Formatting if Cell Consists of Text

Step 2:

  • Now, get a new window named Duplicate Values.
  • Set Duplicate and select specific color.

Step 3:

  • Now, press OK and see the difference.

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


Similar Readings:


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:

Conditional Formatting with Self-Made Formula in Excel

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:

ISTEXT(value)

Argument:

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.

Step 1:

  • Select the cells first.

Step 2:

  • Now, go to the Home tab.
  • Go to Highlight Cells Rules from Conditional Formatting.
  • Click the More Rules then.

Conditional Formatting with Self-Made Formula in Excel

Step 3:

  • We will get a window.
  • Select Use a formula to determine which cells to format.
  • Then put the formula on the marked box.
=ISTEXT(B5)
  • Select Format now.

Conditional Formatting with Self-Made Formula in Excel

Step 4:

  • Select desired color from the Fill option.

Step 5:

  • Then select OK.
  • We will the window with Preview.

Step 6:

  • Then, press OK.

Here, 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 Functions. This function checks if there any number exists. If any number exists provides TRUE, otherwise FALSE.

Syntax:

ISNUMBER(value)

Argument:

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:

SEARCH(find_text,within_text,[start_num])

Arguments:

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.

Step 1:

  • In the data set, we add a box to use a specific text.
  • We set Jeo in that box and select the cells.

Step 2:

  • More Rules of Conditional Formatting we get a new window.
  • We then put a new formula to find specific text. The formula is:
=ISNUMBER(SEARCH($C$11,B5))
  • Select the Formate as shown previously.

Conditional Formatting with Self-Made Formula in Excel

Step 3:

  • Then press OK.

Conditional Formatting with Self-Made Formula in Excel

Step 4:

  • We see that cells with the mentioned text are colored.
  • Now, replace the mentioned text with John to see the effect with other text.

Now, see that colored cells also change as reference changed.

Note:

This method is not case-sensitive.


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:

FIND(find_text, within_text, [start_num])

Arguments:

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:

 

Step 1:

  • Now, change the formula and set the formats as shown before. The formula will be:
=ISNUMBER(FIND($C$11,B5)

Conditional Formatting with Self-Made Formula in Excel

Step 2:

  • Now, press OK.

We can see that as 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

Alok

Alok

Hello, this is Alok. I am working as an Excel & VBA Content Developer at Exceldemy. I want to provide solutions to various Excel-based problems. I completed my study at East West University major in Telecommunications Engineering. I love traveling, reading books, playing cricket.

We will be happy to hear your thoughts

Leave a reply

ExcelDemy
Logo