- Trending Categories
- Data Structure
- Operating System
- MS Excel
- C Programming
- Social Studies
- Fashion Studies
- Legal Studies
- Selected Reading
- UPSC IAS Exams Notes
- Developer's Best Practices
- Questions and Answers
- Effective Resume Writing
- HR Interview Questions
- Computer Glossary
- Who is Who
How To Check Or Find If A Cell Contains A Specific String/Text/Word In Excel?
One of the key content inputs in Excel is strings. Strings refer to a sequence of characters or text like letters, numbers, symbols, and spaces. An Excel string can also represent a name, address, description, or any other textual information. Excel treats it as alphanumeric data rather than just numerical data, enabling it to perform manipulations and operations as the user requires.
Looking for a particular text or word in Excel is very easy if you know how to use the right function and where. With the help of 2 examples, we demonstrate how to use the SEARCH and FIND functions to find a particular string in an Excel cell.
Method 1: Using The Search Function
With the help of the in-built Search function in Excel, you can look for a specific substring within a text string. In the following example, there is a list of books in column A, and one wants to check if a particular word or text is present in the respective cells. Follow the steps to learn how to use the SEARCH function to find if a cell contains a specific word.
Step 1: Select a blank cell where you would like to see the results. We chose cell C2 in the example.
Step 2: Enter the formula in the selected cell to check if the text in cell B2 is present in cell A2.
MS Excel search formula to check if a specific word is mentioned in the selected cell or not=SEARCH(find_text, within_text)
In this case, the formula will be:=SEARCH(B2,A2)
Step 3: Press Control + Shift + Enter to execute the function.
If the SEARCH finds the substring in the specified cell (A2), it returns the character position of the substring in the text as a number. If the substring is not found, the function returns a #VALUE! error.
Step 4: To replicate the SEARCH function on the rest of the values in the table, select and drag down the green coloured square box in the bottom right corner of the formula cell.
It is to be noted that the SEARCH function is not case-sensitive. The user may encounter errors if they want specific results based on the case of the letters, such as when searching for unique codes. For this, we suggest another method using the FIND function.
Method 2: Using The Find Function
The FIND function, like the SEARCH function, returns the position of a substring in the text as a number and an error (#VALUE!) if the substring cannot be found. However, unlike the SEARCH function, the FIND function considers letter cases.
Step 1: Select a cell where you want to apply the FIND function and enter the mentioned formula.
Formula to use the FIND function to check if a specific text exists in a cell or not: =FIND(find_text, within_text)
In this case, the formula will be: =FIND(B2,A2)
Step 2: Press Control + Shift + Enter to execute the function.
Step 3: If you want to repeat the results for the rest of the table in one go, drag the green box in the active cell D2 to the end of the table.
The final result will appear like this . . .
The FIND function is case-sensitive, so when looking for the value “Rhye” in cell A3 which does contain the word but does not start with a capital case, the value returns in #VALUE!
This tutorial shows you how to use two different functions to verify if a cell contains a specific word/text/ or substring. The FIND function gives more accurate results than the SEARCH function as it is case-sensitive. In other words, it takes account of letters when they are in capital.
Data analysts, writers, and engineers use Microsoft Excel to streamline their work and work with heavy data. If you also want to become a pro at excel, enroll in the MS Excel Online Training today!
Kickstart Your Career
Get certified by completing the courseGet Started