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.

Dataset of excel search for text in range,

Method

1. Using Find and Replace Option to Search Text in Range

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

find option to search text .

  • 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.

search texts in dataset.

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

Method

2. Using ISTEXT Function to Check Text in Range

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.
=ISTEXT(B6:B13)
  • Finally, We get the output with TRUE-FALSE. TRUE means text exists.

text searching using ISTEXT function.

Method

3. Check If Cell Contains Specific Text

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.
=IFERROR(IF(SEARCH(B6,C6),”Match”),”Mismatch”)

🔨 Formula Breakdown

👉  General logical expression of the formula, =IFERROR(IF(SEARCH(cell_1,cell_2),”statement_1″),”statement_2″)

👉  SEARCH function search particular data within a cell or in a set of data. Here, SEARCH(B6,C6) means in B6 is finding similar data to C6.

👉  If data is found in C6, then IF signals to make a statement as Match.

👉  Else it may signal an Error. But IFERROR makes the statement Mismatch instead of Error.

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

IFERROR, IF and SEARCH function to search texts in range.

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

Method

4. Search for Text in Cell in Excel

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.
=IF(ISNUMBER(SEARCH(C6,B6)),”FOUND”,”NOT FOUND”)

🔨 Formula Breakdown

👉  Logical expression of the formula, =IFERROR(IF(SEARCH(cell_1,cell_2),”statement_1″),”statement_2″)

👉  SEARCH function search particular data within a cell or in a set of data. Here, SEARCH(B6,C6) means in B6 is finding similar data to C6.

👉  ISNUMBER(SEARCH(C6,B6))= FALSE for C6 which means C6= Hector not found in B6.

👉  If data is found in C6, then IF signals to make a statement as FOUND.

👉  Else it makes the statement NOT FOUND.

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

Use of ISNUMBER to search for text in excel.

Method

5. Check If Values of Two Column Matches or Not

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.
=IF(COUNTIF($C:$C,$B6)=0,”No match in C”,””)

🔨 Formula Breakdown

👉  Combining IF and COUNTIF, Let’s get the logical expression of this formula, =IF(COUNTIF(column_by_range,Cell_Index)=0,”statement_1″,”statement_2″)

👉  COUNTIF function counts the cell number based on certain criteria.

👉  B6 finds out values like itself in column-C with a 0 returns value.

👉  Finally, IF function dictates in cell D6, if B6 is unable to match to column C, deliver No match in C.

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

Use of IF and COUNTIF to search for text in a range in excel.

Method

6. Check If Cell Contains Text from List

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.
=IF(D6=”Match”,MID(C6,SEARCH(B6,C6),LEN(B6)),”Not Available”)

🔨 Formula Breakdown

👉  General logical expression of the formula, =IF(D6=”Match”,MID(ref_cell,SEARCH(range),LEN(Cell_1)),”Not Available”)

👉  LEN returns the no of characters in the string. Therefore, it returns the same no of characters that exist in B6 and finds from the line using the MID function; where the LEFT and RIGHT function finds the value at the beginning and at end of the line respectively.

👉  ALso IF function works if a column like D6 gets a Match value. Otherwise, it dictates Not Available.

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

Use of MID fuction to search texts.

 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

Method

7. If Range of Cells Contains Multiple Specific Text

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.
=IF(SUMPRODUCT(–ISNUMBER(SEARCH($F$6:$F$8,C6))),”Yes”,”No”)

🔨 Formula Breakdown

👉  Here, General logical expression of the formula, IF(SUMPRODUCT(–ISNUMBER(SEARCH(range_with_return,ref_cell))),”Yes”,”No”)

👉  SEARCH($F$6:$F$8,C6) indicates to find $F$6:$F$8= {“best”;”pianist”;”footballer”} in C6.

👉  SUMPRODUCT(–ISNUMBER(SEARCH(range_with_return,ref_cell)))= 0 means no data found. IF function writes NO.

👉  IF function signals to write Yes if it is able to match with specific texts in C6. Otherwise, it signals to write No.

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

Searching specific texts.

Method

8. Text Joining After Searcing for Multiple Text in Range in Excel

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.
=TEXTJOIN(“, “, TRUE, IF(COUNTIF(C6, “*”&$F$6:$F$10&”*”), $F$6:$F$10, “”))

🔨 Formula Breakdown

👉  COUNTIF(C6, “*”&$F$6:$F$10&”*”) means search specific texts in C6.

👉  COUNTIF(C6, “*”&$F$6:$F$10&”*”)= {0;0;0;0;0} means there is no specific texts in C6.

👉  But in C7, COUNTIF(C7, “*”&$F$6:$F$10&”*”)= {1;0;0;1;0} means {“best”;”pianist”} matches in C7.

👉  IF function leaves the cell blank if it unable to match the values.

👉  TEXTJOIN function finally gather matched text in a cell by comma(,).

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

specific texts searcing.

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

Method

9. Using IF and COUNTIF Functions to Search Specific Text in Range

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.
=IF(COUNTIF($C$6:$C$11,”*”&F6&”*”)>0,”Found”,”Not Found”)

🔨 Formula Breakdown

👉  In the formula, COUNTIF($C$6:$C$11,”*”&F6&”*”)>0=TRUE means F6 exits in the range with returned value of $C$6:$C$11.

👉  IF function writes Found for TRUE; Else Not Found for FALSE.

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

Method

10. Use VLOOKUP Function to Search for Text in Range in Excel

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.
=VLOOKUP(UPPER(H6),E6:F11,2)

🔨 Formula Breakdown

👉 Formula syntax, =VLOOKUP(UPPER(lookup_value),range,by_column)

👉 UPPER is also used to find the data of H6 in the specified data range E6 to F11.

👉 VLOOKUP function indicates to lookup the data from the range and takes from the 2nd column.

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

VLOOKUP function to search similar texts.

Method

11. Search for Repeated Text with Position

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.
=SMALL(IF($E$5=$B$6:$B$17,ROW($B$6:$B$17)-ROW($A$1)+1),ROW(1:1))

🔨 Formula Breakdown

👉  (IF($E$5=$B$6:$B$17,ROW($B$6:$B$17)-ROW($A$1)+1)= {FALSE;FALSE;FALSE;9;FALSE;FALSE;12;FALSE;FALSE;15;FALSE;FALSE} dictates that repeated value present at row 9,12,15.

👉  $E$5=$B$6:$B$17 indicates to find $E$5=Hector from $B$6:$B$17 with return value.

👉  SMALL(IF($E$5=$B$6:$B$17,ROW($B$6:$B$17)-ROW($A$1)+1),ROW(1:1))= {9} means Hector present in 9th row.

👉  Similarly, SMALL(IF($E$5=$B$6:$B$17,ROW($B$6:$B$17)-ROW($A$1)+1),ROW(2:2))= {12} means Hector present in 12th row.

👉  And, SMALL(IF($E$5=$B$6:$B$17,ROW($B$6:$B$17)-ROW($A$1)+1),ROW(3:3))= {15} means Hector present in 15th row.

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

Search position for specific tetxt.

Method

12. Search Text and Get Cell Reference

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.
=CELL(“address”,INDEX(B6:B17,MATCH(“*”&E5&”*”,B6:B17,0)))

🔨 Formula Breakdown

👉  Logical expression of the formula is, =CELL(“address”,INDEX(range,MATCH(“*”&cell_no&”*”,range,0)))

👉  MATCH(“*”&E5&”*”,B6:B17,0) means E5= Jammy presents in the 6th cell of the range B6:B17.

👉  INDEX(B6:B17,MATCH(“*”&E5&”*”,B6:B17,0))= “Jammy” ; Here INDEX function recognize the 6th cell value.

👉  CELL function denotes the cell number that is $B$11 which is with the returned value.

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

search Cell reference with text.

Method

13. Search Option to Search for Text in a Table

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.

Search selecting entire dataset.

  • 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.

Filtered data.

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

(Visited 135 times, 2 visits today)
MD Tanvir Rahman

MD Tanvir Rahman

Hello, I am Tanvir. Graduated from BUET. Had a year-long Experience at Operation of Vitacan Industries Ltd. I found Excel is the most unique software to reduce both time and paper. Now, I am an Excel enthusiast, love to play with data and publish content.

We will be happy to hear your thoughts

Leave a reply

ExcelDen
Logo