Excel Conditional Formatting If a Cell Contains Any Text

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.


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.

Data set for Excel Conditional Formatting If a Cell Contains Any Text


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 No Blanks Condition

Step 1:

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

Step 2:

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

Conditional Formatting Default Rules If a Cell Has Any Text

Step 3:

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

Conditional Formatting Default Rules If a Cell Has Any Text

Step 4:

  • Select the Fill color and then click OK.

Step 5:

  • We will get a Preview of the output now.

Conditional Formatting Default Rules If a Cell Has Any Text

Step 6:

  • Finally, click OK.

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


1.2 Specific Text

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.

Step 1:

  • First, select the range.

Step 2:

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

Conditional Formatting Default Rules If a Cell Has Any Text

Step 3:

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

Conditional Formatting Default Rules If a Cell Has Any Text

Step 4:

  • Finally, press OK.

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


1.3 Duplicate Text

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.

Step 1:

  • Select the cells with data.

Step 2:

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

Conditional Formatting Default Rules If a Cell Has Any Text

Step 3:

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

Conditional Formatting Default Rules If a Cell Has Any Text

Step 4:

  • Then press OK.

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

We can also specify the cells with unique words.

Step 5:

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

Step 6:

  • Finally, press OK.

Conditional Formatting Default Rules If a Cell Has Any Text

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


Similar Readings:


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 Text Only

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

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.

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

Step 1:

  • Select the cells to apply Conditional Formatting.

Step 2:

  • Choose New Rule from Conditional Formatting.

Apply Formula in Conditional Formatting If Cell Consists of Any Text

Step 3:

  • 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:
=ISTXT(B5)
  • Then, press Format.

Apply Formula in Conditional Formatting If Cell Consists of Any Text

Step 4:

  • We will choose a color for the Fill field.
  • Then press OK.

Step 5:

  • Now, we will get a Preview.

Step 6:

  • Finally, press OK.

Apply Formula in Conditional Formatting If Cell Consists of Any Text

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


2.2 Specific Text Using Cell Reference

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:

Step 1:

  • Select the cells first.

Step 2:

  • Write down the formula on the marked box. The formula will be:
=B5=”John”

Apply Formula in Conditional Formatting If Cell Consists of Any Text

Step 3:

  • Then, choose the color and see the Preview.

Step 4:

  • Finally, select OK.

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


2.3 Specific Text Using the SEARCH Function

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.

Syntax:

SEARCH(find_text,within_text,[start_num])

Arguments:

find_text – The desired 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 – This is optional. The character number in the within_text argument at which you want to start searching.

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

Apply Formula in Conditional Formatting If Cell Consists of Any Text

Step 1:

  • Now, select the data range.

Step 2:

  • Now, write the SEARCH function in Conditional Formatting. The formula is:
=SEARCH($C$11,B5)

Apply Formula in Conditional Formatting If Cell Consists of Any Text

Step 3:

  • Now, set the Format and see the Preview.

Step 4:

  • Then, press OK.

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


2.4 Text with Case Sensitivity: the FIND Function

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.

Syntax:

FIND(find_text, within_text, [start_num])

Arguments:

find_text – It is the desired text series that will be used in searching.

within_text – We will look at find_text from the within_text. This is the given big text series.

start_num This is optional. It defines the character from which to begin the inquiry.

This Find function is case-sensitive. Here, we will insert this function with Conditional Formatting.

Step 1:

  • Select the data range first.

Step 2:

  • Write the Find function in Conditional Formatting. The formula will be:
=FIND($C$11,B5)

Apply Formula in Conditional Formatting If Cell Consists of Any Text

Step 3:

  • Configure the Format and see the Preview.

Step 4:

  • Finally, press OK.

Apply Formula in Conditional Formatting If Cell Consists of Any Text

Here, 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

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