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.

Preview of excel conditional formatting formula if cell contains text


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.

dataset


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.

selecting data

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

using conditional formatting option

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

new formatting rule window

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

Default Rules in Conditional Formatting if Cell Consists of Text

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

excel conditional formatting formula if cell contains text result

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.

Default Rules in Conditional Formatting if Cell Consists of Text

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

highlight cell with specific text

  • Next, press OK.

highlighted cells with specific text

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.

Default Rules in Conditional Formatting if Cell Consists of Text

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

highlight cells with duplicate text

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

highlight cells with duplicate text result


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:

formula to highlight cells having text

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 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.

selecting data

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

Conditional Formatting with Self-Made Formula in Excel

  • 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.
=ISTEXT(B5)
  • Moreover, select Format.

Conditional Formatting with Self-Made Formula in Excel

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

excel conditional formatting formula if cell contains text result


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:
=ISNUMBER(value)
  • 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:
=SEARCH(find_text,within_text,[start_num])
  • 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.

selecting data

  • 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:
=ISNUMBER(SEARCH($C$11,B5))
  • Again, select the Formate as shown previously.

Conditional Formatting with Self-Made Formula in Excel

  • Then, press OK.

highlighted cells having text with formula

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

highlighted cells having text using formula

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 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:

Cells with Specific Text (Case-sensitive)

STEPS:

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

Conditional Formatting with Self-Made Formula in Excel

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

Cells with Specific Text (Case-sensitive) result


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 Paul

Alok Paul

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

5 Excel Hacks You Never Knew

Genius tips to help you unlock Excel's hidden features

FREE EMAIL BONUS

ExcelDemy
Logo