A.N.M. Mohaimen Shanto
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:
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:
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:
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.
③ Then, type the following formula in the box:
=LEN(B5)-LEN(SUBSTITUTE(B5," ",""))>0=TRUE
④ 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.
⑦ Click on Ok.
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:
📌 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.
③ Then, type the following formula in the box:
=LEN(TRIM(B5))-LEN(SUBSTITUTE(B5," ",""))+1>1
④ 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.
⑦ Click on Ok.
As you can see, we are successful in applying conditional formatting on text that contains multiple words.
Similar Readings:
- Excel Conditional Formatting If a Cell Contains Any Text
- Conditional Formatting Multiple Text Values in Excel (4 Easy Ways)
- Excel conditional formatting based on another cell text [5 ways]
- Excel Conditional Formatting with Formula If Cell Contains Text
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:
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.
② From the New Formatting Rules dialog box, select Use a formula to determine which cells to format.
③ Then, type the following formula in the box:
=SUM(COUNTIF(B5,"*"&search_list&"*"))
④ 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.
⑦ Click on Ok.
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!
