Conditional Formatting on Text that Contains Multiple Words in Excel

While working with Microsoft Excel, we use conditional formatting for various purposes. It is one of the essential tools of Excel. We highlight data from a worksheet with our desired requirements.Β  It made analyzing the data simpler. Sometimes, you may find yourself in a situation where you need to highlight the text that contains multiple words. In this tutorial, you will learn to apply conditional formatting on Text that Contains Multiple Words in Excel with appropriate examples and proper explanations.


Download Practice Workbook

Download this practice workbook.


How to Determine If Text Contains Multiple Words or Not

Now, the question is how can we determine a text that contains multiple words or not. We can see a text and tell whether it has multiple words or not.Β  But, it is difficult to explain in formulas.

We all know the common thing. Multiple words will always contain one or more spaces. So, we will go forward with this theory. Before we start, look at the following screenshot to have a better understanding of what we are saying:

dataset contains multiple words in excel

Here, we have a dataset consisting of some texts. You can easily see that some text has multiple words. And how did we determine that? By counting the spaces. If a text has one or more spaces, it has multiple words in it. We will consider this to apply conditional formatting in Excel.


2 Methods to Use Conditional Formatting on Text that Contains Multiple Words in Excel

Now, in this section, we are providing you with two formulas to determine whether a text contains multiple words or not. After that, you can apply conditional formatting to your dataset. We recommend you learn and apply all these methods to enrich your Excel knowledge.

To demonstrate this tutorial, we are going to use this dataset:

dataset of Conditional Formatting on Text that Contains Multiple Words in Excel

Here, we have some movies’ name and their box office collection. We will apply conditional formatting on these texts to determine whether they have multiple words or not.

1. LEN & SUBSTITUTE Functions to Use Conditional Formatting on Text that Contains Multiple Words

In this method, we are using a formula consisting of the LEN function and the SUBSTITUTE functions to determine whether the text contains multiple words or not.

The Generic Formula:

=LEN(text)-LEN(SUBSTITUTE(text,” β€œ,””))>0=TRUE

This formula basically counts spaces. So, our formula describes if there is any space, this text contains multiple words.

πŸ“Œ Steps

β‘  First, Select the range of cells B5:B9. Then, go to the Conditional Formatting option in Home Tab. Click on New Rule.

β‘‘ From the New Formatting Rules dialog box, select Use a formula to determine which cells to format.

select the option of conditional formatting for text contains multiple words excel

β‘’ Then, type the following formula in the box:

=LEN(B5)-LEN(SUBSTITUTE(B5," ",""))>0=TRUE

type the formula to find text with multiple words in excel

β‘£ After that, click on the Format

β‘€ Now, from the format cells dialog box, select Fill. Then, choose any colors. Click on Ok.

β‘₯ Now, your formula and Format are set.

LEN & SUBSTITUTE Functions to Use Conditional Formatting on Text that Contains Multiple Words

⑦ Click on Ok.

final output of conditional formatting on text contains multiple words in excel

As you can see, we have successfully applied conditional formatting on text that contains multiple words in Excel.

2. Using LEN, TRIM, and SUBSTITUTE Functions of Excel in Conditional Formatting

In this method, we are basically counting words based on spaces. We are performing this with the help of the LEN function, the SUBSTITUTE function, and the TRIM function to count multiple words. If there is more than one word, we are applying conditional formatting on that particular cell.

The Generic Formula:

=LEN(TRIM(Text))-LEN(SUBSTITUTE(text,” β€œ,””))+1>1

πŸ“Œ Steps

β‘  First, Select the range of cells B5:B9. Then, go to the Conditional Formatting option in Home Tab. Click on New Rule.

select new rule to apply conditional formatting

β‘‘ From the New Formatting Rules dialog box, select Use a formula to determine which cells to format.

select the option of conditional formatting for text contains multiple words excel

β‘’ Then, type the following formula in the box:

=LEN(TRIM(B5))-LEN(SUBSTITUTE(B5," ",""))+1>1

Using LEN, TRIM, and SUBSTITUTE Functions of Excel in Conditional Formatting

β‘£ After that, click on the Format

β‘€ Now, from the format cells dialog box, select Fill. Then, choose any colors. Click on Ok.

β‘₯ Now, your formula and Format are set.

forumulas are set

⑦ Click on Ok.

final output of excel conditional formatting text contains multiple words in excel

As you can see, we are successful in applying conditional formatting on text that contains multiple words.


Similar Readings:


Apply Conditional Formatting Search For Multiple Words In Excel

Now, you may need to search multiple words from a list. You may want to want to find these words in another dataset. You can highlight the results after it has found these. Using conditional formatting is the easiest option to perform this. The method is almost like the previous methods. But we are using a different formula to accomplish this.

To demonstrate this method, we are going to use this dataset:

dataset of search multiple words and apply conditional formatting in excel

Here, we are going to search the words Convert, Words, and Space from the column Text.

Before you start, just give the desired list a name. Select all the words. Then go to the Formulas tab. Select Define Name. After that give it a name. We are giving it search_list.

πŸ“Œ Steps

β‘  First, Select the range of cells B5:B12. Then, go to the Conditional Formatting option in Home Tab. Click on New Rule.

select range of cells and click new rule

β‘‘ From the New Formatting Rules dialog box, select Use a formula to determine which cells to format.

select the option of conditional formatting for text contains multiple words excel

β‘’ Then, type the following formula in the box:

=SUM(COUNTIF(B5,"*"&search_list&"*"))

Apply Conditional Formatting Search For Multiple Words In Excel

β‘£ After that, click on the Format

β‘€ Now, from the format cells dialog box, select Fill. Then, choose any colors. Click on Ok.

β‘₯ Now, your formula and Format are set.

formula and formats are set

⑦ Click on Ok.

Apply Conditional Formatting Search For Multiple Words In Excel

As you can see, we applied conditional formatting to find multiple words from a list to another dataset.


πŸ’¬ Things to Remember

✎ There is a catch in these formulas. If you have one word with multiple spaces, it will apply conditional formatting on those cells.

✎ If you think your data may contain unnecessary spaces, you need to remove extra spaces.


Conclusion

To conclude, I hope this tutorial has provided you with a piece of useful knowledge to apply conditional formatting on text that contains multiple words in Excel. We recommend you learn and apply all these instructions to your dataset. Download the practice workbook and try these yourself. Also, feel free to give feedback in the comment section. Your valuable feedback keeps us motivated to create tutorials like this.

Don’t forget to check our website Exceldemy.com for various Excel-related problems and solutions.

Keep learning new methods and keep growing!


Further Readings

Shanto

Shanto

Hello! I am Shanto. An Excel & VBA Content Developer. My goal is to provide our readers with great tutorials on various Excel-related problems. I hope our easy but effective tutorials will enrich your knowledge. I have completed my BSc in Computer Science & Engineering from Daffodil International University. Working with data was always my passion. Love to work with data, analyze those, and find patterns. Also, love to research. Always look for challenges to keep me growing.

We will be happy to hear your thoughts

Leave a reply

ExcelDemy
Logo