Search and display all cells that contain multiple search strings
Jerome asks, in this blog post Search for multiple text strings in multiple cells in excel :
If the list of strings in column D was to increase to a large number e.g. 15, how would you tell excel to select the range of strings, so that you don't have to select each string "SEARCH($D$3" in the search parameter, as it seems is the case at the moment?
Array Formula in G3:
Change the following in order to add more conditions:
- cell reference $E$2:$E$3 if you want more conditions
- the number after the second equal sign =2 to as many conditions you have in the formula
- {1; 1} to as many conditions you have. For example, 4 conditions -Â {1; 1; 1; 1}
How to create an array formula
- Select cell F2
- Type formula in formula bar
- Press and hold Ctrl + Shift
- Press Enter
- Release all keys
Explaining formula in cell
Step 1 - Search for multiple strings
The SEARCH function allows you to find a string in a cell and it's character position. It also allows you to search for multiple strings in multiple cells if you arrange values in a way that works. That is why I use the TRANSPOSE function to transpose the values.
SEARCH(TRANSPOSE($E$2:$E$3), $B$3:$B$13)
becomes
SEARCH(TRANSPOSE({"B";"f"}), $B$3:$B$13)
becomes
SEARCH({"B","f"}, $B$3:$B$13)
and returns
{#VALUE!, 1; 1, #VALUE!; 2, #VALUE!; #VALUE!, #VALUE!; #VALUE!, #VALUE!; 1, #VALUE!; #VALUE!, 1; 1, 2; 2, #VALUE!; #VALUE!, 3; 3, 1}
Step 2 - Convert values into boolean values
The ISNUMBER function returns TRUE if value is number and FALSE for everything else including errors.
ISNUMBER(SEARCH(TRANSPOSE($E$2:$E$3), $B$3:$B$13))
becomes
ISNUMBER({#VALUE!, 1; 1, #VALUE!; 2, #VALUE!; #VALUE!, #VALUE!; #VALUE!, #VALUE!; 1, #VALUE!; #VALUE!, 1; 1, 2; 2, #VALUE!; #VALUE!, 3; 3, 1})
and returns
{FALSE, TRUE;TRUE, FALSE;TRUE, FALSE;FALSE, FALSE;FALSE, FALSE;TRUE, FALSE;FALSE, TRUE;TRUE, TRUE;TRUE, FALSE;FALSE, TRUE;TRUE, TRUE}
Step 3 - Convert boolean values
The MMULT function can't work with boolean values so we need to convert them to their numerical equivalents.
--(ISNUMBER(SEARCH(TRANSPOSE($E$2:$E$3), $B$3:$B$13)))
becomes
--({FALSE, TRUE;TRUE, FALSE;TRUE, FALSE;FALSE, FALSE;FALSE, FALSE;TRUE, FALSE;FALSE, TRUE;TRUE, TRUE;TRUE, FALSE;FALSE, TRUE;TRUE, TRUE})
and returns
{0,1;1,0;1,0;0,0;0,0;1,0;0,1;1,1;1,0;0,1;1,1}.
Step 4 - Add numbers in array row-wise
MMULT(--(ISNUMBER(SEARCH(TRANSPOSE($E$2:$E$3),$B$3:$B$13))),{1;1})=2
becomes
MMULT({0,1;1,0;1,0;0,0;0,0;1,0;0,1;1,1;1,0;0,1;1,1}, {1;1})=2
becomes
{1;1;1;0;0;1;1;2;1;1;2}=2
and returns
{FALSE; FALSE; FALSE; FALSE; FALSE; FALSE; FALSE; TRUE; FALSE; FALSE; TRUE}.
Step 5 - Prevent duplicates in the list
The next COUNTIF function counts values based on a condition or criteria, the first argument has this cell reference: $G$2:G2. It expands as you copy the cell and paste to cells below.
(COUNTIF($G$2:G2, $B$3:$B$13)=0)
becomes
{0;0;0;0;0;0;0;0;0;0;0}=0
and returns
{TRUE; TRUE; TRUE; TRUE; TRUE; TRUE; TRUE; TRUE; TRUE; TRUE; TRUE}
Step 6 - Multiply arrays
We apply AND logic if we multiply the arrays, this means both values must be TRUE in order to return TRUE.
(COUNTIF($G$2:G2, $B$3:$B$13)=0)* (MMULT(--(ISNUMBER(SEARCH(TRANSPOSE($E$2:$E$3), $B$3:$B$13))), {1; 1})=2)
becomes
{TRUE; TRUE; TRUE; TRUE; TRUE; TRUE; TRUE; TRUE; TRUE; TRUE; TRUE}*{FALSE; FALSE; FALSE; FALSE; FALSE; FALSE; FALSE; TRUE; FALSE; FALSE; TRUE}
and returns
{0;0;0;0;0;0;0;1;0;0;1}
Step 6 - Divide 1 with array
The LOOKUP function ignores error values. Divide 1 with zero and we get #DIV/0! error.
1/((COUNTIF($G$2:G2, $B$3:$B$13)=0)* (MMULT(--(ISNUMBER(SEARCH(TRANSPOSE($E$2:$E$3), $B$3:$B$13))), {1; 1})=2))
becomes
1/{0;0;0;0;0;0;0;1;0;0;1}
and returns
{#DIV/0!; #DIV/0!; #DIV/0!; #DIV/0!; #DIV/0!; #DIV/0!; #DIV/0!; 1; #DIV/0!; #DIV/0!; 1}.
Step 7 - Return values
LOOKUP(2, 1/((COUNTIF($G$2:G2, $B$3:$B$13)=0)*(MMULT(--(ISNUMBER(SEARCH(TRANSPOSE($E$2:$E$3), $B$3:$B$13))), {1; 1})=2)), $B$3:$B$13)
becomes
LOOKUP(2, {#DIV/0!; #DIV/0!; #DIV/0!; #DIV/0!; #DIV/0!; #DIV/0!; #DIV/0!; 1; #DIV/0!; #DIV/0!; 1}, $B$3:$B$13)
becomes
LOOKUP(2, {#DIV/0!; #DIV/0!; #DIV/0!; #DIV/0!; #DIV/0!; #DIV/0!; #DIV/0!; 1; #DIV/0!; #DIV/0!; 1}, {"F"; "BAA"; "DBB"; "ADD"; "DAD"; "BDA"; "FDA"; "BFA"; "ABA"; "DAF"; "FDB"})
and returns "FDB" in cell G3.
Get Excel *.xlsx file
Search and display all cells that contain all search strings.xlsx
Search and return multiple values category
This article demonstrates array formulas that search for cell values containing a search string and returns corresponding values on the […]
This article demonstrates a formula that extracts a row or record from a data table using two strings as criteria. […]
This article demonstrates formulas that let you perform partial matches based on multiple strings and return those strings if all […]
Excel categories
20 Responses to “Search and display all cells that contain multiple search strings”
Leave a Reply
How to comment
How to add a formula to your comment
<code>Insert your formula here.</code>
Convert less than and larger than signs
Use html character entities instead of less than and larger than signs.
< becomes < and > becomes >
How to add VBA code to your comment
[vb 1="vbnet" language=","]
Put your VBA code here.
[/vb]
How to add a picture to your comment:
Upload picture to postimage.org or imgur
Paste image link to your comment.
Can you do this by displaying an adjacent column instead of the column that was searched?
if that is not possible then can you do this formula by having List (A2:A12) start at A3 and go to A13?
okay I figured it out! thanks and I love your site!!
Man how long have you been working with excel?... i have just 2 years and I didn't have the slightest idea (till know ) you can do this only with formulas, I usually solve this kind of issues with VBA macros.
What can you recommend me to be able to do this? review each one of the formulas and its examples , or reading a lot of excel books or what?
I have learned a lot just by starting an excel blog.
Review others formulas and reading books is a good start. Enjoy what you are doing and solutions come easily into mind.
Oscar,
Thanks again. Would you help me with replaced search string1 with replace string1 and search string2 with replace string2....etc.... thanks,
James
Great! although it's unable to correctly find the 02590 string. What could it be?
Andres,
02590?
Can you provide an example?
Hello Oscar,
My worksheet is failing not yours (blush). I put new strings on yours and it works just as I expected. Need to dig into it a bit more, he he.
Thanks for your valuable attention and congrats for your portal.
- Andres.
Hello Oscar,
Here I have an example (https://www.yourfilelink.com/get.php?fid=830240) for the functions of this page. As you can see in this example I am telling the spreadsheet to find what is in cell A3 but it show the content for row 3775 instead. I used hardcoded ranges aswell as named ranges with no change. When I put the content of the Example.xls spreadsheet in your spreadsheet it works but refuses to work in mine, perhaps due because I am using Excel 2003 but I am not fully convinced.
Thank you!
- Andres.
Andres,
Use this array formula in cell K7:
Hi Oscar,
I need to combine row and text columns into one "text" and suppress errors. Excel complaints that I exceeded the nested formulas.
Much appreciated..
Sorry Oscar,
Forgot to mention, only one search string is required, and may contain letters and numbers.
Best regards.
Carl,
combine row and text columns into one "text" and suppress errors
Can you provide an example?
I have two types of cells in the worksheet. Some are with green background and some are with plain white background. Some of these cells have a string of syntax in regular expression "ABC".
Each cell could have 1 or more strings according to regular expression above separated by ","(comma).
Could experts guide me on a formula
How to count the number of "ABC" in a every green/white cells to have an over all total in a given range.
For example, if cell 1 have 3 strings, cell 2 has 5 strings. The total is 8
G8 work bro, can we get the results in column wise rather then rows ??
It works horizontally as well. Use the same formulas as above but enter it in cell H2.
Then copy cell H2 and paste it to I2 and as far as needed.
=LOOKUP(2, 1/((COUNTIF($G$2:G2, $B$3:$B$13)=0)*(MMULT(--(ISNUMBER(SEARCH(TRANSPOSE($E$2:$E$3), $B$3:$B$13))), {1; 1})=2)), $B$3:$B$13)
The bolded cell reference above is important in order to get values horizontally: $G$2:G2
You need to change it so it points to the cell to the left of the current cell. Example, if you are going to enter the formula in cell L5 then change the cell reference to $K$5:K5.
Hi,
I have a list of movies into a column, and I want to list all cells that contains a specific word/s.
It is similar than doing a filter and looking for some chain "text", and I want to know and list all the cells that match that chain text.
Is that possible?
Joaquin,
The easiest way to list all cells containing "text" would be to apply a filter.
1. Select any cell in your data set.
2. Go to tab "Home".
3. Press with left mouse button on "Sort & Filter" button.
4. Press with left mouse button on "Filter".
The header names now have arrows.
1. Press with left mouse button on an arrow based on the column you want to filter.
2. Press with left mouse button on "Text Filters".
3. Press with left mouse button on "Contains..".
4. Type the text string you want to match.
5. Press with left mouse button on ok button.
The data is now filtered.
Is there a way to change TRANSPOSE($E$2:$E$3) from a column of data to a row?
I've tried $D$2:$E$2 and neither work in the cell but in the Function Arguments box it shows the expected value.
I think it is an issue with circular references but Excel can't show the problem.