Search for multiple text strings in multiple cells in excel, part 2
Jerome asks, in this blog post Search for multiple text strings in multiple cells in excel :
Answer:
Formula in F2:
You can change the cell reference $D$2:$D$3 to any size, as long it is referencing a single column.
How to create an array formula
- Select cell F2
- Paste formula to formula bar
- Press and hold CTRL + SHIFT
- Press Enter
- Release all keys
Download excel *.xlsx file
search-for-multiple-strings-in-multiple-cells-part2 v2.xlsx
(Excel 97-2003 Workbook *.xls)
Functions in this article:
IF(logical_test;[value_if:true];[value_if_false])
Checks whether a condition is met, and returns one value if TRUE, and another value if FALSE
COUNT(value1;[value2])
Counts the number of cells in a range that contain numbers
ROW(reference) Returns the rownumber of a reference
ISERROR(value)
Checks whether a value is an error and returns TRUE or FALSE
SMALL(array,k) Returns the k-th smallest row number in this data set.
SEARCH() Returns the number of the character at which a specific character or text string is first found, reading left to right (not case sensitive)
INDEX(array,row_num,[column_num])
Returns a value or reference of the cell at the intersection of a particular row and column, in a given range
TRANSPOSE(array)
Converts a vertical range to a horizontal range, or vice versa.
Related posts:
Search for multiple text strings in multiple cells and use in data validation in excel
Search for multiple text strings in multiple cells in excel
Search and display all cells that contain all search strings in excel
Lookup with multiple criteria and display multiple search results using excel formula, part 4
Lookup with multiple criteria and display multiple search results using excel formula, part 2
Recommended categories
Know if you whant to know what string was found in each case that one was found.
Example.
In you post above
A3 contains the text BAA
And on cell G3 you have the same value becouse it found B string from the Search_strings. Whant i will whant is for it to post B becouse that is wat was found. Thanks
or a diferent way of asking,
If A value from the list apears on cell A1, What is that value.
carlos padron,
Array formula in H2:
Copy cell H2 and paste down.
See attached file
Hi - I have a worksheet that contains a list of line items such as, "Service Tee Top Ent 1inx32mm Primofit" I want to find and extract the "32mm" portion of the text, but have some 15 different variants, like, 40mm; 20mm.
The text lengths are different in length - the common denominator is that the number always has "mm" after it.
Any ideas guys !
Paul,
are there always two numbers before "mm"?
Hi - there are always 2 or 3 numbers before the mm ...it's pipe diameter sizes, eg 75mm; 100mm; 450mm
Thanks, Paul
..forgot to add, that there will be a different length of text string before the '32mm' portion.
Paul,
Open attached file:
Paul.xls
Formula in cell B1:
Column “A” row has some TEXT Value
Example: Canada
And
Column “B” row has Some TEXT Value (Similar as “A” and also some other TEXT) .
Example: “Canada is great place”
How to Match or compare to find if Canada is present in Column “B” row
There are more than 100+ Row in column “A” have TEXT value and Column “B” has multiple values TEXTs.
Need to compare Column A and B.
How to compare (Cell ‘A’) with Column “B” to find specific TEXT value from Column “B”
Raju,
read this post: Return multiple matches with wildcard vlookup in excel
Hi,
I'm working on pivot tables. I've a unique requirement to create a measure that counts the number of rows matching the "string" in a column.
for eg:
column_value: "cancel the membership"
I would like to create a measure which search for text "cancel" and counts.
COUNTROWS(...)
could you please help?
thank you.
Shyam,
=COUNTIF(range, "*string*")
This post might be helpful:
Excel 2007 pivot table: Count unique distinct records (rows)
How can we match case in
=INDEX(Search_strings, MIN(IF(COUNTIF(G2, "*"&Search_strings&"*"), ROW(Search_strings)-MIN(ROW(Search_strings))+1, ""))) + CTRL + SHIFT + ENTER
Ram,
Try this formula:
Hey guys. I really need to solve this quickly to make my workdays less stressful! My request is similar to one above but slightly different. I have the following rows of data:
CASACA_M COLLOKY PLCS46-B BONDED 14 AZULMARINO
CAMISA_PK COLLOKY CACS27 BIPACK XL BLANCO
CHAL_FHOMB COLLOKY CHCS55 FULL ZIPPER S AZULMARINO
CHAL_FMUJ COLLOKY CHCS46-B FULL ZIPPER 10 AZULMARINO
Z_UNISEX COLLOKY 7111-01C BLACK 26-29 29
Generally...I need a string near the middle without a standardized format but are almost always 6 characters long or 7 if there is an additional dash..they will either be formatted as three letters then two numbers or 4 numbers..a dash and two more numbers. Here are examples
CHAL_FMUJ COLLOKY CHCS46-B FULL ZIPPER 10 AZULMARINO:
I need the CHC46-B and the 10
CASACA_M COLLOKY PLCS46-B BONDED 14 AZULMARINO:
I need the PLCS46-B and the 14
CHAL_FHOMB COLLOKY CHCS55 FULL ZIPPER S AZULMARINO:
I need the CHCS55
Z_UNISEX COLLOKY 7111-01C BLACK 26-29 29
I need the 7111-01 26-29 29 (I can live without the 26-29 and the 29 if I have to but I would like to atleast have the 29 as well)
I know these may require different formulas but I was wondering if someone could solve the most difficult and then explain how I can manipulate it to accomodate the simpler ones. Thanks so much for your help in advance.
Andrew,
Try "text to columns", tab "Data". Your strings would appear in column 3, 4 or 5.
I believe this is what you "need to think more to solve", but want to see if you have any answer anyways!
So far, I changed the 'list' to include 235 cells (in column A), and the 'search_string' to 241 cells (in column d). It finds 15 accounts, but is not able to list more than 15. Is there a way to get all accounts to show in columns F and G?
Thanks!
Ian,
Extend the array formulas in cell range F2:G16.
Example,
1. Select cell range F2:G20
2. Click in formula bar
3. Press and hold Ctrl + Shift
4. Press Enter
Hi all,
I have a problem i need to extract a reference from a text string, this reference is 10 numbers but sometimes the reference comes starting with :58 and others starts with -58, so i am using the formula =MID(T53,FIND("-58",T53)+1,10) and =MID(T53,FIND(":58",T53)+1,10) but how to combine them so it looks for :58 or in case it is not found look for -58.
Thank you,
Iram,
Excel 2007 and above:
=MID(T53,IFERROR(FIND("-58",T53),FIND(":58",T53))+1,10)
Hi all
From the following table my goal is to search for first cell that contains the word "Pharmacy" and return the value from the corresponding column.
Bank of Nova Scotia 1.53km
Medico Pharmacy 1.62km
CIBC ATM 1.66km
All-In-One Wood Tools 1.75km
Drugstore Pharmacy 1.76km
HSBC Bank Canada 1.76km
Sexauer Ltd 1.87km
Rona 1.97km
Thank you and much appreciated.
Kumail,
Array formula in cell E2:
Hello Oscar
Thank you so much for the help but my friend its not working.
Hey Oscar, I firgured it out. Thanks again
Hi Oscar,
I have a rather long question but will try to put it as simple as possible:
In sheet 1 I have a table with ±500.000 values distributed over 23.000 rows and 40 columns.
In column 12 there are TAG numbers (sometimes embedded in text), the same TAG numbers occur multiple times in this column and look like V-T3451.
In column 5 there are document types with the first two digits which are of importance (the rest is a sequentual number) (AB001, AC001, etc...).
In column 9 there is the status of the document (S, A, C, R, D, etc...).
In sheet 2 I have a list with approx. 500 unique TAG numbers evenly sorted out.
The formula I'm looking for is how can I find the status of a document type for each specific TAG number.
Sometimes multiple answers are possible (Approved (A), Commented (C) and Started (S)) since there are multiple documents of the same type (Genral Arrangement, Detail drawings, etc) for one unique TAG number.
Hope you can help me on this "problem" since now I'm doing it all by hand what is taking me almost a week to update the Register!!
Thanks.
KR.
Ruud
Ruud,
Can you upload a workbook with "fake" data? That would make it a lot easier.
Contact
hey there!
if i want to find let's say "number= 15214" this is what i want to search to get me the name for that person but it's not a primary key. So they have same number but different name.
15214 mazk
15214 ali
How I can do the function do this kind of search?
thank you,
mazk,
I think this is the post you are looking for:
How to return multiple values using vlookup