## 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 :

*If the list of strings in column D was to increase to a large number e.g. 15, how would you tell excell 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?*

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

### 32 Responses to “Search for multiple text strings in multiple cells in excel, part 2”

### Leave a Reply

**How to add a formula to your comment:**

<code>your formula</code>

**How to add VBA code to your comment:**

[vb 1="vbnet" language=","]

VBA code

[/vb]

**How to add a picture to your comment:**

Upload picture to postimage.org

Add picture link to comment.

**Contact Oscar**

You can contact me through this webpage

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:+ CTRL + SHIFT + ENTERCopy 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,

Arrayformula 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

I have a col full of names and to the right of it there are 4 columns containing the subjects the are taking at school.

I nee a method that if I want to know everyone who is taking english will give me a list on a new work sheet. Can you help

Hello, I have one cell that contains a sentence. In the sentence I want to check make sure that two words are in the sentence. For example, a cell contains "This is a test" I tried =IF(ISNUMBER(SEARCH({"This","test"},A502,1))=TRUE,"good") But this doesn't check for both strings only the first. How can I return a value "good" if both words are in the cell?

EricDa,

Array formula:

=AND(ISNUMBER(SEARCH({"This","test"},A502)))