13 Ways to Search for Text in Range in Excel

In the corporate world, we need to deal with a lot of data every day. We also work with data for the betterment. But the fact is we don’t need the same data every day. On the other hand, every day new data is imputed. To find particular data we need to search for text or value in a range of datasets in Excel or a Spreadsheet. Excel is the most used software to deal with data. In this article, we are going to demonstrate how to search for text in range in Excel.

📁 Download Excel File

To practice and get the overview, please download the excel file:

Learn to Search for Text in Range with 13 Examples in Excel

Here in this article, we find out 13 ways to search for text in range in Excel. We consider a dataset of Nottingham City containing winners of 2021 and 2022 with their IDs. In the dataset, there are 6 columns and 11 rows. We are going to use SEARCH, ISTEXT, ISNUMBER, CELL, IFERROR, MATCH, COUNTIF, and INDEX functions to search for text in range in Excel.

Using the Find option from Home Menu you can easily search anything in the dataset. Follow the steps,

 ⬇️⬇️ STEPS ⬇️⬇️

• First, click on the Home Menu from Top Ribbon.
• Secondly, click on the Search
• Thirdly, click on the Find

• Afterward, a box of Find and Replace will appear.
• Then, From Find what, choose Werner.
• Next, click on Find All. Therefore, all the data containing Werner will show.

Here we got 3 data containing Werner. Clicking from the Find and Replace box will be marked on the dataset.

📕 Read More: 7 Ways to Find and Replace Multiple Values in Excel

Sometimes in the dataset, the text is absent. To detect text ISTEXT function can be a key. Please follow the necessary steps,

 ⬇️⬇️ STEPS ⬇️⬇️

• Primarily, Navigate a blank cell i.e. D6.
• Then, Insert the formula in D6 containing ISTEXT function choosing the range from B6 to B13.

• Finally, We get the output with TRUE-FALSE. TRUE means text exists.

Suppose you have a set of records of Nottingham City. One tells you to find matching from history. What to do? Using the SEARCH function can be your cup of tea in this case. Follow the below procedure.

 ⬇️⬇️ STEPS ⬇️⬇️

• Firstly, Select a blank cell i.e. D6.
• Secondly, Input the formula containing IF, SEARCH, and IFERROR in D6 to match cell B6 to find from the text of C6.

• By getting the result at D6 and using Fill Handle we finally achieve our result.

📕 Read More: 3 Easy Ways to Find and Replace in Multiple Excel Files

Using IF, SEARCH and ISNUMBER functions, you can also check the winner of 2022 from 2021 history. Check the below steps,

 ⬇️⬇️ STEPS ⬇️⬇️

• Primarily, select a cell i.e. D6.
• Secondly, Input the formula containing IF, SEARCH, and ISNUMBER in D6 to match cell C6 to find from the text of B6.

• By getting the result at D6 and auto-filling, we finally obtain the final result.

Let’s say, you need to match 2021 winners to 2022 winners just to make sure if there is anyone who won last year, also this year. The COUNTIF function can be a key to comparing between columns and searching for texts.

 ⬇️⬇️ STEPS ⬇️⬇️

• Initially, Select a cell i.e. D6.
• Then, Input the formula containing COUNTIF and IF in D6 to match cell to column.

• By obtaining the result at D6 and using Fill Handle we get the final result.

You can also search for specific text from some lines from another column. Comparing two columns first we will get our specific text and return it to another cell using LEFT, RIGHT, and MID functions. But the LEFT and RIGHT functions only match with the first word and last word respectively. But MID function along with a SEARCH function searches the text accurately.

 ⬇️⬇️ STEPS ⬇️⬇️

• Primarily, Navigate a cell i.e. D6.
• Then, Insert the following formula that counts words.

• Finally, we get the result at D6, and we finally get the total result using Fill Handle.

 But, Unlike LEFT or RIGHT, the Use of the MID function gives us the best results.

📕 Read More: 3 Quick Ways to Replace Text After Specific Character

If you need to search multiple specific texts from the dataset. You can find it by yourself by following the below steps,

 ⬇️⬇️ STEPS ⬇️⬇️

• Initially, Navigate a cell i.e. D6.
• Secondarily, Insert the following formula.

• Finally, we get the result at D6, and we finally desired the result using Fill Handle.

Finding the specific texts you can also write specific texts in a cell joining a comma(,). Let’s follow the procedure.

 ⬇️⬇️ STEPS ⬇️⬇️

• First, get a blank cell i.e. D6.
• Then, Write down the specific texts under the Specific Text column (F6:F10).
• Next, Insert the following formula.

• Lastly, we get the result at D6, and we finally obtain the result using Fill Handle.

📕 Read More: 5 Tricks to Insert Comma Between Words in Excel

Using IF and COUNTIF functions combined you can search specific multiple texts in a range. Follow the below steps,

 ⬇️⬇️ STEPS ⬇️⬇️

• Firstly, select a blank cell i.e. D6.
• Secondly, Write down the specific texts under the Specific Text column(F6:F11)
• Thirdly, Insert the following formula.

• Finally, at D6, we get the result, and using Fill Handle we get the total output.

VLOOKUP function dedicates to looking for a value within a specific array. To compare columns one can use VLOOKUP along with the UPPER function.

 ⬇️⬇️ STEPS ⬇️⬇️

• Initially, navigate a blank cell i.e. J6.
• After that, Input the following formula in J6.

• After all, we get the result at D6, and we finally get the output using Fill Handle.

Suppose you need to know the text position of repetitive value. Following the steps, you will be able to determine the positions.

 ⬇️⬇️ STEPS ⬇️⬇️

• Initially, navigate a blank cell i.e. E6.
• After that, Input the following formula in E6.

• Lastly, Getting the result at E6 and auto-filling we got 3 rows where Hector is located.

Suppose you need to know the cell reference of a particular value. Following the steps, you will be able to determine.

 ⬇️⬇️ STEPS ⬇️⬇️

• Initially, navigate a blank cell i.e. E6.
• After that, Input the following formula in E6.

• Finally, Get the result of the cell $B$11 at E6.

The simplest way is to search manually selecting a table. Follow the below steps,

⬇️⬇️ STEPS ⬇️⬇️

• Initially, select the entire dataset.
• Then, Right-click on the mouse.
• Next, click on the Filter and select Filter by Selected Cell’s Value.

• After that, a filter menu box will appear beside the column heading.
• Then, select the name, you wish.
• Next, click on OK

• Finally, by Clicking Payne, you will also get the details in column C.

Also, watch carefully, Information about Payne is in the 8th column. Here only the 8th column appears using this method.

📄 Important Notes

🖊️  Combination of MID along with SEARCH function search data from the entire line. However, Be cautious while using LEFT or RIGHT functions. These two functions only consider the character of the beginning and end of the lines.

🖊️  Inspect functions and syntax carefully during the use of the formulae.

🖊️  CELL and ISNUMBER functions are only available on Microsoft 360 version.

📝 Takeaway from This Article

📌  TEXTJOIN function uses to join text in a single cell. 

📌  ISTEXT function uses to identify texts in a dataset.

📌  IF is a logical function that deals with statements based on TRUE-FALSE.

📌  IFERROR function eliminates Error with a value.

📌  EXACT function considers case-sensitivity.

📌  VLOOKUP Function looks up the values in a specified area.

📌  In this article, we also used SEARCH, COUNTIF, MID, UPPER, and INDEX functions to search for text in the dataset.

📌  We demonstrated every formula briefly so that it may also help during other applications.

Conclusion

In this article, we tried to demonstrate every possible way to search for texts in a range of single or multiple columns. Hope that you enjoyed your learning. For queries and any kind of suggestions please comment below. Also for more, please visit www.ExcelDen.com.

Related Article

• How to Find and Replace Using Wildcards in Excel