Microsoft Excel Tutorial; Does an Excel Cell Contain Certain Text?

Welcome to another informative video from MrExcel! Today, we will be tackling a common question that many Excel users have - how to use the IF function to check if a cell contains certain text. This may seem like a simple task, but the answer may surprise you. I will be sharing my own experience and the solution that I found to be the most efficient.

So, the question is, how do we use the IF function to check if a cell contains a specific word or phrase? Many people would assume that using the asterisk wildcard character would work, just like in the SUMIF and COUNTIF functions. However, when we try this method, it doesn't seem to work. This can be frustrating, especially when we know that it works in other functions.

My go-to solution for this problem has always been the FIND function. However, this function has its limitations, such as being case-sensitive and returning a #Value! error if the text is not found. To work around these limitations, I found myself using additional functions like SIGN and IFERROR, which made the formula quite clunky and inefficient.

But, after some research, I stumbled upon the SEARCH function and a clever solution from the MrExcel Message Board. This function works just like FIND, but it is case-insensitive and returns a number if the text is found. By combining this with the ISNUMBER function, we can easily check if a cell contains a specific word or phrase, and the result is either TRUE or FALSE. This is a much more efficient and elegant solution compared to my previous methods.

In conclusion, if you need to use the IF function to check if a cell contains certain text, the combination of SEARCH and ISNUMBER is the way to go. It is efficient, versatile, and can handle both uppercase and lowercase text. However, if you don't need a formula, you can simply use the filter function to quickly find all the cells that contain the desired text. Thank you for watching, and I hope this video has been helpful. Don't forget to subscribe to our channel for more useful Excel tips and tricks. See you next time!

Buy Bill Jelen's latest Excel book: [ Ссылка ]

You can help my channel by clicking Like or commenting below: [ Ссылка ]


Table of Contents
(0:00) Question
(0:22) Check for equality
(0:41) Wildcards fail in IF
(1:05) Using COUNTIF with wildcard
(1:39) Using FIND in Excel
(2:23) Adding SIGN, IFERROR, LOWER
(2:59) Using SEARCH in Excel
(3:25) Using ISNUMBER with SEARCH
(4:22) Excel IF contains solution
(4:44) Finding Contains using Filter
(5:03) Clicking Like really helps the algorithm

#excel #microsoft #microsoftexcel #exceltutorial #exceltips #exceltricks #excelmvp #freeclass #freecourse #freeclasses #excelclasses #microsoftmvp #walkthrough #evergreen #spreadsheetskills #analytics #analysis #dataanalysis #dataanalytics #mrexcel #spreadsheets #spreadsheet #excelhelp #accounting #tutorial

This video answers these common search terms:
Case sensitivity in Excel FIND function
Case sensitivity in Excel SEARCH function
Excel cell contains certain text
Excel COUNTIF function
Excel FIND function
Excel IF statement
Excel SEARCH function
Excel SUMIF function
Filtering cells in Excel based on text criteria
Using IF statement in Excel to check for equality
Using IF statement in Excel to check for partial match
Using ISNUMBER function in Excel
Excel IF Contains
Excel IF cell contains text
Excel IF contains
excel like wildcard
excel wildcard
excel like wildcard if

Join the MrExcel Message Board discussion about this video at [ Ссылка ]



How can you test which cells in Excel contain text? Can you use an IF statement in Excel to find which cells contain certain text?

This video looks at COUNTIF, FIND, SEARCH, LOWER, SIGN, ISNUMBER, IFERROR functions.

свернуть