Search for a text string in a data set and return multiple records
This article explains different techniques that filter rows/records that contain a given text string in any of the cell values in a record. To filter records based on a condition read this: VLOOKUP - Extract multiple records based on a condition, that article also demonstrates how to filter records using the new FILTER function only available in Excel 365.
Example, the image above shows the data in cell range B3:E6, the condition is in cell C9. The formula extracts records from B3:E6 if at least one cell contains the string.
Row 3 is extracted above because string AA is found in cell C3. Row 4 and 5 are also extracted because string AA is found in cell D4 and E5 respectively. Row 6 is not extracted, there is no cell containing the search string.
Read section 1.1 for a detailed explanation of how this formula works. I have built a formula that matches two criteria and return multiple records.
What is on this page?
- Search for a text string in a data set and return multiple records [Array formula]
- Search for a text string in a data set and return multiple records [Excel 365]
- Search for a text string in a data set and return multiple records [Excel defined Table]
- Search for a text string in a data set and return multiple records [Advanced Filter]
- Get Excel file
1. Search for a text string in a data set and return multiple records [Array formula]
This example demonstrates a formula that extracts records if any cell on the same row contains a specific value specified in cell C9.
This means also that the formula returns the same record multiple times if multiple cells contain the search value.
Array formula in B13:
To enter an array formula, type the formula in cell B13 then press and hold CTRL + SHIFT simultaneously, now press Enter once. Release all keys.
The formula bar now shows the formula with a beginning and ending curly bracket telling you that you entered the formula successfully. Don't enter the curly brackets yourself.
Copy cell B13 and paste to cell range B13:E16. Replace FIND function with SEARCH function if you don't want the formula to perform a case-sensitive search.
Search for a text string in a data set and return multiple records (no duplicates)
Array formula in cell B13:
The array formula above returns unique distinct records meaning no duplicate records if more than one cell matches the search string.
1.1 Explaining array formula in cell B13
Step 1 - Identify cells containing the search string
The FIND function returns the starting point of one text string within another text string, it returns a number representing the position of the found string. If not found the function returns the #VALUE! error.
FIND($C$9, $B$3:$E$6)
returns
{#VALUE!, 1, #VALUE!, #VALUE!; #VALUE!, #VALUE!, 4, #VALUE!; #VALUE!, #VALUE!, #VALUE!, 1; #VALUE!, #VALUE!, #VALUE!, #VALUE!}
The ISNUMBER function returns TRUE if the value in the array is a number and FALSE if not a number, it returns FALSE even if the value is an error value which is handy in this case.
ISNUMBER(FIND($C$9, $B$3:$E$6)) creates this array displayed in cell B11:E14:
Column B has no cells containing string "AA".
Column C has 1 cell containing string "AA". Cell C3
Column D has 1 cell containing string "AA". Cell D4.
Column E has 1 cell containing string "AA". Cell E5.
Step 2 - Return row number
We need to calculate the row number for each cell in order to replace TRUE in the array with the corresponding row number. To create the array we need we use the MATCH function and the ROW function.
MATCH(ROW(Table1), ROW(Table1)) returns this array displayed in cell range B11:E14:
If string "AA" is found in a cell in the table the corresponding row number is returned.
Step 3 - Replace boolean values with row numbers
The IF function has three arguments, the first one must be a logical expression. If the expression evaluates to TRUE then one thing happens (argument 2) and if FALSE another thing happens (argument 3).
IF(ISNUMBER(FIND($C$9, Table1)), MATCH(ROW(Table1), ROW(Table1)), "")
becomes
IF({#VALUE!, 1, #VALUE!, #VALUE!; #VALUE!, #VALUE!, 4, #VALUE!; #VALUE!, #VALUE!, #VALUE!, 1; #VALUE!, #VALUE!, #VALUE!, #VALUE!}, MATCH(ROW(Table1), ROW(Table1)), "")
becomes
IF({#VALUE!, 1, #VALUE!, #VALUE!; #VALUE!, #VALUE!, 4, #VALUE!; #VALUE!, #VALUE!, #VALUE!, 1; #VALUE!, #VALUE!, #VALUE!, #VALUE!}, {1; 2; 3; 4}, "")
and returns the following array shown in cell range B11:E14:
Step 4 - Sort the row numbers from smallest to largest
To be able to return a new value in a cell each I use the SMALL function to filter column numbers from smallest to largest.
The ROWS function keeps track of the numbers based on an expanding cell reference. It will expand as the formula is copied to the cells below.
SMALL(IF(ISNUMBER(FIND($C$9, Table1)), MATCH(ROW(Table1), ROW(Table1)), ""), ROWS($A$1:A1))
returns 1.
Step 5 - Return a value at the intersection of a particular row and column
The INDEX function returns a value based on a cell reference and column/row numbers.
INDEX(Table1, SMALL(IF(ISNUMBER(FIND($C$9, Table1)), MATCH(ROW(Table1), ROW(Table1)), ""), ROWS($A$1:A1)), COLUMNS($A$1:A1))
becomes
INDEX(Table1, 1, COLUMNS($A$1:A1))
becomes
INDEX(Table1, 1, 1)
and returns "East" in cell B13.
Step 6 - Remove errors
The IFERROR function allows you to display a blank if the formula returns an error.
2. Search for a text string in a data set and return multiple records [Excel 365]
The image above demonstrates an Excel 365 formula that extracts records based on a condition. The record is extracted if any cell in a record contains the condition.
Dynamic array formula in cell B13:
This formula works only in Excel 365, it returns an array of values that spills to cells below and to the right automatically. The formula contains the new FILTER function.
2.1 Explaining formula in cell B13
Step 1 - Identify cells containing the given search string
The FIND function returns the position of a specific string in another string, reading left to right. Note, the FIND function is case-sensitive.
FIND(C9, Table1)
becomes
FIND("AA", {"East", "AA BB", "II JJ", "PP QQ AA";"North", "CC DD", "KK AA", "RR SS";"South", "EE FF", "LL MM", "AA TT";"West", "GG HH", "NN OO", "UU VV"}))
and returns
{#VALUE!, 1, #VALUE!, 7;#VALUE!, #VALUE!, 4, #VALUE!;#VALUE!, #VALUE!, #VALUE!, 1;#VALUE!, #VALUE!, #VALUE!, #VALUE!}.
The FIND function returns a #VALUE! error if no string is found.
Step 2 - Check if value in array is a number
The ISNUMBER function returns a boolean value TRUE or FALSE. TRUE if the value is a number and FALSE for anything else, also an error value.
ISNUMBER(FIND(C9, Table1))
becomes
ISNUMBER({#VALUE!, 1, #VALUE!, 7;#VALUE!, #VALUE!, 4, #VALUE!;#VALUE!, #VALUE!, #VALUE!, 1;#VALUE!, #VALUE!, #VALUE!, #VALUE!})
and returns {FALSE, TRUE, FALSE, TRUE;FALSE, FALSE, TRUE, FALSE;FALSE, FALSE, FALSE, TRUE;FALSE, FALSE, FALSE, FALSE}.
Step 3 - Convert boolean values to numerical equivalents
The asterisk lets you multiply a value or array, this action converts boolean values to numbers automatically. This step is required because the MMULT function can't work with boolean values.
ISNUMBER(FIND(C9, Table1))*1
becomes
{FALSE, TRUE, FALSE, TRUE;FALSE, FALSE, TRUE, FALSE;FALSE, FALSE, FALSE, TRUE;FALSE, FALSE, FALSE, FALSE} * 1
and returns
{0, 1, 0, 1; 0, 0, 1, 0; 0, 0, 0, 1; 0, 0, 0, 0}.
Step 4 - Create a number sequence
The ROW function calculates the row number of a cell reference.
ROW(ref)
ROW(Table1)
returns {3; 4; 5; 6}.
Step 5 - Change numbers to 1
The power of or exponent character is able to convert each number if number to the power of zero is calculated.
ROW(Table1)^0
becomes
{3; 4; 5; 6}^0
and returns {1; 1; 1; 1}.
Step 6 - Consolidate numbers
The MMULT function calculates the matrix product of two arrays, an array as the same number of rows as array1 and columns as array2.
MMULT(array1, array2)
MMULT(ISNUMBER(FIND(C9, Table1))*1, ROW(Table1)^0)
becomes
MMULT({0, 1, 0, 1; 0, 0, 1, 0; 0, 0, 0, 1; 0, 0, 0, 0}, {1; 1; 1; 1})
and returns {2; 1; 1; 0}.
Step 7 - Filter values based on array
The FILTER function lets you extract values/rows based on a condition or criteria.
FILTER(array, include, [if_empty])
FILTER(Table1, MMULT(ISNUMBER(FIND(C9, Table1))*1, ROW(Table1)^0))
becomes
FILTER(Table1, {2; 1; 1; 0})
and returns the following array in cell B13:
{"East", "AA BB", "II JJ", "PP QQ AA"; "North", "CC DD", "KK AA", "RR SS"; "South", "EE FF", "LL MM", "AA TT"}
3. Search for a text string in a data set and return multiple records - Excel Table
This example demonstrates how to filter records if any of the cells on a row contains a specific string using an Excel Table. You need a formula and a helper column to accomplish this task.
You can't do this using the "Custom Autofilter" built-in to the Excel Table, there is no way to use OR logic between filters across columns, you need a formula to do this. As far as I know.
3.1 Convert dataset to an Excel defined Table
- Select any cell within the dataset.
- Press CTRL + T
- Press with left mouse button on checkbox if your dataset contains headers for each column.
- Press with left mouse button on OK button.
3.2 Add formula to Excel defined Table
- Select cell F3.
- Type formula: =COUNTIF(Table13[@[January]:[March]],"*AA*")
- Press Enter.
Excel fills the remaining cells in the table for you and creates a header name for your new column automatically.
3.3 Explaining formula in cell F3
Step 1 - COUNTIF function
The COUNTIF function calculates the number of cells that is equal to a condition.
COUNTIF(range, criteria)
Step 2 - Populate arguments
COUNTIF(Table13[@[January]:[March]],"*AA*")
range - Table13[@[January]:[March]] is a structured reference to data in columns January to March in table Table13. The at sign @ before the header names indicate that the reference is to values on the same row.
criteria - "*AA*" The asterisk character is a wildcard character that matches 0 (zero) to any number of characters. When we use a leading and trailing asterisk the criteria matches cells that contain "AA".
Step 3 - Evaluate formula
COUNTIF(Table13[@[January]:[March]],"*AA*")
becomes
COUNTIF({"AA BB", "II JJ", "PP QQ"}, "*AA*")
and returns 1 in cell F3. String AA was found once in cell range C3:E3. Note that the formula evaluates only cells on the same row, this is why the formula doesn't return an array of values.
3.4 Filter Excel Table
To filter the records containing string AA at least once follow these steps:
- Press with left mouse button on black arrow next to the header name "April".
- Press with left mouse button on checkbox next to 0 (zero) to deselect it.
- Press with left mouse button on OK button.
April is not the correct header name, I changed it to Condition.
4. Search for a text string in a data set and return multiple records [Advanced Filter]
The Advanced Filter is a powerful feature in Excel that allows you to perform OR-logic between columns. The asterisk lets you do a wildcard lookup meaning that a record is filtered if the text string is found somewhere in the cell value.
4.1 Add columns
- Copy column headers and paste to cells above or below the dataset. Note, if you place them next to the dataset they may become hidden when the filter is applied.
- Type the search condition and add an asterisk before and after the text string.
- Add another search condition, make sure they are in a row each in order to perform OR-logic.
- Repeat with the remaining criteria.
4.2 Apply filter
- Select the dataset.
- Go to tab "Data" on the ribbon.
- Press with left mouse button on the "Advanced" button.
- Press with left mouse button on the radio button "Filter the list, in-place".
- Select Criteria range:"
- Press with left mouse button on "OK button.
To delete the filter applied simply select a cell within the filtered dataset, then go to tab "Data" on the ribbon and press with left mouse button on "Clear" button.
5. Excel file
How do I extract rows that contain a string in a data set or table?
You can use a formula to extract records based on a search value, it also returns multiple records if there are any that match. The advantage of using a formula is that it is dynamic meaning the result changes as soon as a new search value is entered. The downside with the formula is that it may become slow if you have lots of data to work with.
How do I filter rows that contain a string using Advanced Filter?
You also have the option to filter records using an Advanced Filter, it allows you to perform multiple search values using OR-logic across multiple columns. This article explains how to set it up, jeep in mind that it needs a small amount of manual work in order to apply new filters.
How do I filter rows that contain a string using an Excel defined Table?
The Excel defined Table needs the COUNTIF function to accomplish the task which may slow down the calculation considerably if your data set is huge. I recommend using the Advanced Filter if speed is an issue.
Read more articles about this topic
The following article shows you how to VLOOKUP and return multiple values, the lookup value must match the entire cell value.
Recommended articles
This post explains how to lookup a value and return multiple values. No array formula required.
Search for a cell value in a dataset:
Recommended articles
Question: How do I identify rows that have a specific cell value in an Excel defined table? Array formula in […]
How to look for values in a column that contain two text strings:
Recommended articles
This article demonstrates formulas that let you perform partial matches based on multiple strings and return those strings if all […]
Search two columns for two text strings. The strings can be anywhere in these two columns but both text strings have to be found in different cell values or a single cell value and be on the same row to match:
Recommended articles
Question: I want to search two columns with two search strings? The strings can be anywhere in these two columns […]
Search for three text strings in three columns, one text string in each column. Return values in which all three cases are found:
Recommended articles
Question: Can expand this equation set into more than two colums of data, say if I had a first, middle […]
Search for multiple text strings in multiple columns, one text string in each column. Return values in which all text strings match:
Recommended articles
Question: I second G's question: can this be done for more than 3? i.e. (Instead of last name, middle, first) […]
Search for a single text string in a single column and return multiple matches.
Recommended articles
This article demonstrates array formulas that search for cell values containing a search string and returns corresponding values on the […]
Search for a text string in a single column and return multiple corresponding values.
Recommended articles
This article demonstrates array formulas that search for cell values containing a search string and returns corresponding values on the […]
Search for multiple text strings in a single column and return multiple corresponding values.
Recommended articles
This article demonstrates array formulas that search for cell values containing a search string and returns corresponding values on the […]
Search for a text string in multiple columns and return corresponding values.
Recommended articles
This article demonstrates array formulas that search for cell values containing a search string and returns corresponding values on the […]
Filter records category
This article demonstrates how to extract records/rows based on two conditions applied to two different columns, you can easily extend […]
Lookup with criteria and return records.
Question: I second G's question: can this be done for more than 3? i.e. (Instead of last name, middle, first) […]
Excel categories
53 Responses to “Search for a text string in a data set and return multiple records”
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.
Dear Oscar,
Do you know if there is a formula (or combination) that results in a text value I want to use with "vlookup"
Something like the "like" function in Access.
Example:
text = "Kn1263-Techstore-MrFixit-12-11-2011"
I want to Find the part "Tech".
Then with Vlookup I want to find a related account in an Table.
such a Tech is related to "200 Repare account"
So I can keep controle over my Bankaccount for example.
Maybe in VBA?
Thanks in advance for your reply.
Kind regards,
Lourens van 't Wout
The Netherlands
Lourens van 't Wout,
Yes, I believe it is possible.
Can you describe the two tables and how they are related?
Lourens,
This post describes how to search for multiple textstrings and return a match from another table: https://www.get-digital-help.com/2010/11/22/return-multiple-matches-with-wildcard-vlookup-in-excel/
Hi Oscar,
Request your help for a similar kind of a query. I'm in a fix and can't really figure out how to get the required output.
I have two columns A & B both containing string values. The value in Column A is basically a reference number and value in Column B contains detailed text containing that number itself...
For e.g. Row 1 for Column A has a value 00125465.. Now in column B I have a value like (without qoutes) "With reference to the record number 00125465, we would like to..". This value of column A might exist in multiple rows of Column B.
In my actual data there are around 160,000 unique values for column A and 97,000 values for column B. I need to search for all values of column A in column B and have some kind of an identifier in say Column C to know what values for column A are present in Column B. Please note that a value for Column A might exist on row # 10 and then row # 1000 in column B.
Hope you can help me with this.
Cheers !
Hasan,
Check out the attached file:
Hasan.xlsx
Hi Oscar,
Many thanks for the file. Apparently it looks to be exactly what I was looking for. This is simply great :)..
Having said that the code seems to be really complex. Can I please have your email address so that I can request for any clarifications if required for the code ?
Once again thanks a lot.
God Bless !!
Hi Oscar,
Sorry for bothering you again for this. Will you be please kind enough to define the formula in two three lines in simple words for me.. Can't really understand how these functions are working.. specially ' _xlfn.IFERROR ' :(..
Apologies for the bother but would really appreciate your help.
God Bless !
Hasan,
You can find an explanation here:
https://www.get-digital-help.com/2010/11/22/return-multiple-matches-with-wildcard-vlookup-in-excel/
IFERROR function removes errors.
hi, i was wondering how can i make this search formula to work in excel 2003 i get an error #name! in the search result box. thanks
ali,
Array formula:
=INDEX(Table1, SMALL(IF(ISNUMBER(FIND($C$9, Table1)), ROW(Table1)-MIN(ROW(Table1))+1, ""), ROW(Table1)-MIN(ROW(Table1))+1), COLUMN(Table1)-MIN(COLUMN(Table1))+1)
search-for-a-string-in-an-excel-table.xlsx
ple seam file find transfar to sheet no 2
Hello Oscar,
If I do not want to use table in the formular for ranges in 2003?
My search is in range a1:a20 and its looking for the text in b2:b500
Array formula:
=INDEX(Table1, SMALL(IF(ISNUMBER(FIND($C$9, Table1)), ROW(Table1)-MIN(ROW(Table1))+1, ""), ROW(Table1)-MIN(ROW(Table1))+1), COLUMN(Table1)-MIN(COLUMN(Table1))+1)
James,
I believe this post answers your question:
https://www.get-digital-help.com/2010/04/07/lookup-values-in-a-range-using-two-or-more-criteria-and-return-multiple-matches-in-excel-part-2/
Array formula:
Hi Oscar,
This formula works great for what I'm trying to achieve.
I have a project data sheet capturing names of people involved. I'm using this to allow people to search for a particular name, and for all relevant projects to be displayed.
Have modified the formula to use a SEARCH function instead of FIND for the closest match, but I need to identify all the column numbers in the data matrix where there's a match for an index function against another matrix of the same size.
I've figured out how to retrieve the row numbers, but am stuck at getting the right columns to be displayed.
Wenyong,
but I need to identify all the column numbers in the data matrix where there's a match for an index function against another matrix of the same size.
Can you explain in greater detail?
Thanks Oscar, for the prompt reply.
Is it possible for me to attach or send you an example of what I'm working on?
I'll try explaining in more detail:
I have a datasheet on different projects, names of colleagues involved in the project are organized in 12 columns (each cell may contain more than one name. For this reason, I've modified your function to use SEARCH instead of FIND), and their involvement in the project captured in another array of 12 columns.
Purpose of the spreadsheet is for any user to enter a search name (e.g. Peter) and for the spreadsheet to display all projects this person is involved in. For this, the function you've developed works beautifully. This is also why I'm using SEARCH, as the user may enter only a first name, and the function can capture the closest match from a cell that may contain 3 names. FIND can only return an exact match.
Besides returning the name of the project as a result, I'm looking to develop this further by displaying the role of the person for this project. For example, Peter is involved in Project X as a Sales Manager. His name is captured in Resource_Name_Column7 and his role is captured in Role_Column7.
The current function can return Project X as a result when user searches for 'Peter'. However, I do not want to index out all 12 columns that matches the Row at which his name is found.
I'd like to display the role he plays in this project by indexing the x and y coordinates at which his name is found in the Names array, against the Roles array which is of the same size (both 12 columns).
I've manage to display and return the result for Row number of the project, but couldn't find a way to capture the column number at which his name is found
Wenyong,
You can use this contact form.
The above example is excellent one.
But my requirement is something different.
I need to print only column1 contents when the pattern AA matches.
Also if "AA" presents twice in same row this particular cloumn1
content should be repeated twice.
Thanks
senthil,
Read this: Search for a text string in a column and return multiple adjacent values corresponding to the number of matching values
Oscar - Thanks so much for providing this! It's really helpful.
I've implemented the array formula and it works great except that if the search string occurs 2x in row of data, the row is returned twice. If it occures 3x, the row is returned three times.
I'd like the results to show only 1 instance of each row no matter how many times the search string appears in that row. How can that be done?
Thx!
Richard,
See this file:
search-for-a-string-in-an-excel-table-return-unique-rows.xlsx
Thanks, Oscar. The function as is in the file was returning the same text string in all cells in the array - and not the search string. I changed the end part of the function from "...ROW(A1)),COLUMN(A1)),"")" to "...ROW(Table1)),COLUMN(Table1)),"") and it is working... mostly. I'm no longer getting duplicate rows. However, the function is failing to return some rows that include the text string. I can't quite figure out what is unique about the rows returned vs those that should be and are not (or vis-versa).
What do you think the fix is?
Thx!
Richard,
Thanks, Oscar. The function as is in the file was returning the same text string in all cells in the array - and not the search string. I changed the end part of the function from "...ROW(A1)),COLUMN(A1)),"")" to "...ROW(Table1)),COLUMN(Table1)),"")
However, the function is failing to return some rows that include the text string. I can't quite figure out what is unique about the rows returned vs those that should be and are not (or vis-versa).
You have entered the same array formula in all cells. Enter the formula in the first cell and then copy the cell (not the formula) to the right. Copy the entire row and paste down.
The array formula contains relative cell references. They change when you copy the formula.
HELLO,
i have a column with many rows containing select queries.
e.g
query_column
select * from abc where ...
select * from efgh where ...
select * from pqrst where .....
i want output in next column as
abc
efgh
pqrst
how can this be done?
please help.
Hey, I want to use one command, i searched in but i didn't found. please help if you have the solution.
My question is: In a EXCEL 2007 work book I want to find the cells only containing the word (STRING). for example:
refuse 6 to 2 accept 6 this 4 responsibility 14 which 5 the 3 god 3 of 2 the 3 universe 8
blade 5 are 3 pressed 7 against 7 the 3 upper 5 grinders. 8 ) 1 what 4 shall 5 our 3
the 3 passage 7 of 2 air. 3 ) 1 the 3 aspirate 8 quality, 7 or 2 whisper, 7 is 2
alone 5 in 2 the 3 room, 4 you 3 will 4 sound 5 very 4 silly 5 if 2 you 3
you 3 if 2 he 2 11 let 3 me 2 see 3 his 3 programme, d. 1 hark
me 2 recommend that 4 of 2 the 3 effective 9 speaking 8 voice 5 which 5 deals with 4
the 3 death 5 of 2 molly cass, the 3 little 6 cripple s 1 garden, the 3
laugh never 5 does 4 anyone any 3 harm I 1 those 5 dangerously dynamic 7 british
offensively healthy 7 english 7 families, 8 ever 4 since 5 mr. 2 britling we 2 have 4 been 4
grandmothers, who 3 all 3 get 3 out 3 at 2 in 2 the 3 morning and 3 play 4
which 5 the 3 sisters until 5 they 4 begin to 2 have 4 children along 5 in 2
this is my work book, only i copied a part of the workbook. the words are with numbers. I want to find / search a word which is not with a number (the word is what ever it may be / random word).
please help me.
JAFAR,
I don´t understand, can you upload a picture of our worksheet?
Upload picture to postimage.org
The File Image is not uploading. I will copy and paste here. In this down side file the some cells contains both number and text, and some cells contains only the text. In this work book I want to find the text containing cells. Please help me.
ESTABLISH 9 A 1 HOME 4 RAISE 5
all 3 about 5 your 4 own 3 mental 6 capacity, 8 from 4 a 1 hasty 5 glance 6 through 7 the 3 various 7 tests 5 i-figure 7 it 2 out 3 that 4 i 1 would 5 be 2 classified 10 in 2 group 5 b, 1 indicating 10 low 3 average 7 ability, 7 reserved 8 usually 7 for 3 those 5 just 4 learning 8
to 2 speak 5 the 3 english 7 language 8 and 3 preparing 9 for 3 a 1 career 6 of 2 holding 7 a 1 spike 5 while 5 another 7 man 3 hits 4 it. 2 if 2 they 4 ever 4 adopt 5 the 3 menti 5 meter 5 tests 5 on 2 thisjournal 11 i 1 shall 5 lastjust 8 about 5 #NAME? minutes, 7 and 3 the 3
trouble 7 is 2 that 4 each 4 test 4 starts 6 off 3 so 2 easily, 6 you 3 begin 5 to 2 think 5 that 4 you 3 are 3 so 2 good 4 that 4 no 2 has 3 ever 4 appreciated 11 you. 3 there 5 is 2 for 3 instance, 8 a 1 series 6 of 2 twenty 6 pictures 8 (very badly 5 drawn 5 too, 3 mr. 2 frank 5
parker 6 stock 5 bridge. 6 you 3 think 5 you 3 are 3 so 2 smart, 5 picking 7 fiaws 5 with 4 people 6 s 1 intelligence, 12 if 2 i 1 couldn 6 t 1 draw 4 a 1 better 6 head 4 than 4 the 3 on 2 page 4 i 1 would 5 throw 5 up 2 the 3 whole 5 business), at 2 any 3 rate, 4 in 2 each 4 of 2
these 5 pictures 8 there 5 is 2 something 9 wrong 5 (wholly a 1 from 4 the 3 drawing), you 3 are 3 supposed 8 to 2 pick 4 out 3 the 3 incongruous 11 feature, 7 and 3 you 3 have 4 seconds 7 in 2 which 5 to 2 tear 4 the 3 twenty 6 pictures 8 to 2 pieces, 6 the 3 first 5
is 2 easy, 4 the 3 rabbit 6 has 3 human 5 ear. 3 in 2 unpardonable sin of 2 hitting the 3 nail on 2 the 3 head, 4 he 2 might 5 almost 6 have 4 seen 4 an 2 advance copy of 2 the 3 honours 7 list, tranto. 6 he 2 hadn 4 t. 1 nor 3 had 3 who 3 s 1 in 2 it 2 culver, 6 you 3
might 5 ask 3 who 3 isn 3 t 1 in 2 it. 2 (taking a 1 paper 5 from 4 his 3 pocket.) well, 4 gentleties in 2 it. 2 he 2 gets 4 a 1 knighthood, tranto. 6 never 5 heard 5 of 2 him 3 who 3 is 2 he 2 hiidegarde. oh, 2 yes, 3 you 3 ve 2 heard 5 of 2 him. 3 (John glances at 2
her 3 severely.) he 2 s 1 m.p. for 3 some 4 earthly paradise or 2 other 5 in 2 the 3 south riding, tranto. 6 oh 2 culver, 6 perhaps 7 i 1 might 5 read 4 you 3 something 9 writ 4 by 2 my 2 private 7 secretary 9 he 2 s 1 of 2 these 5 literary 8 wags, you 3 see 3
there 5 s 1 been 4 a 1 demand that 4 the 3 government 10 should 6 state 5 clearly, in 2 every 5 case 4 of 2 an 2 honour, 6 exactly 7 what 4 services the 3 honour is 2 given 5 for. 3 this 4 (taking paper 5 from 4 his 3 pocket) is 2 supposed 8 to 2 be 2 the 3 stuff 5
sent 4 round 5 to 2 the 3 press 5 by 2 the 3 press 5 bureau, (reads.) mr. 2 gentletie has 3 gradually 9 made 4 a 1 solid 5 reputation #NAME? himself 7 as 2 the 3 dullest man 3 in 2 the 3 house 5 of 2 commons, whenever 8 he 2 rises to 2 his 3 feet 4 the 3
house 5 empties as 2 if 2 by 2 magic, in 2 cases of 2 inconvenience, when 4 the 3 government 10 wishes abruptly to 2 close 5 a 1 debate 6 by 2 counting 8 out 3 the 3 house, 5 it 2 has 3 invariably 10 put 3 up 2 mr. 2 gentletie to 2 speak, the 3 device has 3
never 5 been 4 known 5 to 2 fail, nobody 6 can 3 doubt 5 that 4 mr. 2 gentletie s 1 patriotic devotion to 2 the 3 allied cause 5 well 4 merits the 3 knighthood which 5 is 2 now 3 bestowed on 2 him. 3 John 4 (astounded.) stay 4 me 2 with 4 ftagons
tranto. 6 so 2 that 4 s 1 that 4 and 3 who 3 else 4 culver, 6 another 7 of 2 your 4 esteemed uncles, 6 tranto. 6 well, 4 that 4 s 1 not 3 very 4 startling, seeing that 4 my 2 uncle 5 s 1 chief 5 daily 5 organ 5 is 2 really 6 a 1 de 2 ment 4 of 2 the 3 government. John, 4
what 4 isay is 2 hiidegarde (simultaneously with 4 John), wouldn 6 t 1 it 2 be 2 more 4 correct (continuing alone 5 ) 1 wouldn 6 t 1 it 2 be 2 more 4 correct to 2 say 3 that 4 the 3 government 10 is 2 really 6 a 1 de 2 ment 4 of 2 your 4 uncle 5 s 1 chief 5 daily 5
organJohn, hilda, old 3 girl, 4 i 1 wish 4 you 3 wouldn 6 t 1 interrupt, cookery 7 s 1 your 4 line, 4 hiidegarde. sorry,Johnnie, isee 4 i 1 was 3 in 2 danger of 2 becoming 8 unsexed. culver 6 (tojohn). yes 3 you 3 were 4 about 5 to 2 say 3 John, 4 oh, 2
nothing, 7 culver 6 (to tranto). shall 5 iread thepassage on 2 your 4 uncle 5 tranto. 6 don 3 t 1 trouble, who 3 S the 3 next 4 culver, 6 the 3 next 4 is 2 ullivant, munitions manufacturer, let 3 me 2 see. 3 (reads.) by 2 the 3 simple 6 means 5 of 2
front 5 of 2 the 3 book 4 for 3 the 3 use 3 of 2 this 4 poem, 4 and 3 only 4 rightly 7 too, 3 for 3 without 7 it 2 the 3 story 5 could 5 never 5 have 4 been 4 writ), he 2 goes 4 out 3 into 4 the 3 ocean, 5 but 3 there 5 we 2 mustn 5 t 1 give 4 too 3 much 4 of 2 the 3 plot 4 away, 4
all 3 that 4 need 4 know 4 is 2 that 4 luke 4 or 2 sir 3 nigel, 5 as 2 you 3 wish 4 (and what 4 reader 6 offlorence 10 ba 2 relay 5 wouldn 6 t 1 prefer 6 sir 3 nigel?), was 3 so 2 cultured 8 that 4 he 2 said, 4 nobody 6 in 2 the 3 whole 5 world 5 knows 5 it, 2 save 4 you 3 and 3 i, 1
and 3 referred 8 to 2 fiotsam 7 andjetson 9 as 2 he 2 was 3 swimming 8 out 3 into 4 the 3 path 4 of 2 the 3 rising 6 sun. 3 jetsam 6 is 2 such 4 an 2 ugly 4 word, 4 it 2 is 2 only 4 fitting 7 that 4 on 2 his 3 tombstone 9 lady 4 tintagel 8 should 6 have 4 had 3 inscribed 9 an 2
impressive 10 and 3 high 4 sounding 8 misquotation 12 from 4 the 3 bible. 5 I 1 measure 7 your 4 mind 4 measure 7 your 4 mind 4 by 2 m.r. 2 traube 6 and 3 frank 5 parker 6 stockbridge, 11 is 2 apt 3 to 2 be 2 a 1 very 4 discouraging 12 book 4 if 2 you 3 have 4 any 3 doubt 5
at 2 saying 6 that 4 the 3 cost price 5 of 2 shells was 3 een shillings 9 and 3 pence each, 4 whereas 7 it 2 was 3 in 2 fact 4 only 4 shillings 9 and 3 pence, mr. 2 Joshua ullivant has 3 made 4 a 1 fortune of 2 million 7 pounds during 6 the 3 war. 3 he 2 has 3
given 5 a 1 hundred 7 thousand 8 to 2 the 3 prince of 2 wales s 1 fund, a 1 hundred 7 thousand 8 to 2 the 3 red 3 cross, 5 and 3 a 1 hundred 7 thousand 8 to 2 they 4 funds, total net profit 6 on 2 the 3 war, 3 million 7 hundred 7 thousand 8 pounds, 6 not 3
counting 8 the 3 peerage which 5 is 2 now 3 bestowed upon 4 him, 3 and 3 which 5 it 2 must 4 be 2 admitted 8 is 2 ajust reward 6 for 3 his 3 remarkable business 8 acumen, tranto. 6 very 4 agreeable 9 fellow 6 ullivant is, 2 nevertheless, culver, 6 oh, 2
he 2 is. 2 they 4 re 2 most 4 of 2 them 4 too 3 damned 6 agreeable 9 for 3 anything, another 7 prominent name 4 is 2 orlando bush, tranto. 6 ah 2 mrs. 3 culver, 6 ive 3 met 3 his 3 wife, 4 she 3 dances beautifully at 2 charity matinees, culver, 6 no 2
doubt, 5 but 3 apparently that 4 s 1 not 3 the 3 reason, 6 tranto. 6 i 1 know 4 orlando. ive 3 Just bought 6 the 3 serial rights of 2 his 3 book, 4 culver, 6 have 4 you 3 pa 2 id 2 him 3 tranto. 6 no. 2 culver, 6 how 3 wise of 2 you 3 (reads ). 1 mr. 2 orlando
bush has 3 writ 4 a 1 historical sketch, with 4 many 4 circumstantial details, of 2 the 3 political 9 origins of 2 the 3 present 7 government, fir 3 his 3 forbearance in 2 kindly consenting to 2 with 4 old 3 publication until 5 the 3 end 3 of 2 the 3 war 3
mr. 2 bush receives a 1 well 4 earned tranto. 6 what 4 culver, 6 knighthood, tranto. 6 cheap but 3 what 4 a 1 sell 4 for 3 me 2 culver, 6 now, 3 ladies 6 and 3 gentlemen, the 3 last 4 name 4 with 4 which 5 i 1 will 4 trouble 7 you 3 is 2 that 4 of 2 mr. 2 James
brill, tranto. 6 notjimmy brill the 3 second 6 the 3 woman 5 s 1 eye 3 is 2 in 2 her 3 hair, 4 pretty 6 soft, 4 you 3 say 3 to 2 yourself 8 in 2 the 3 third 5 the 3 bird 4 has 3 legs, 4 it 2 looks 5 like 4 a 1 cinch, 5 following 9 in 2 quick 5 succession 10 come 4 a 1 man 3 with 4
his 3 mouth 5 in 2 his 3 forehead, 8 a 1 horse 5 with 4 cows 4 horns, 5 a 1 mouse 5 with 4 rabbit 6 s 1 ears, 4 etc. 3 you 3 will 4 have 4 time 4 for 3 a 1 handspring 10 before 6 your 4 seconds 7 are 3 up. 2 but 3 then 4 they 4 get 3 tricky, 6 there 5 is 2 a 1 post 4 card 4
CONFIDENSIAL 12 324
JAFAR,
Have you read this post:
Search for multiple text strings in multiple cells
Oscar,!! Brilliant formula, !!! However, Id like to get any and all matching value occurances in the dataset/table.
So it would return row1find,row2find,row3find etc - ideally illl like to get the row&column number of all the matches.
I've managed to do it for the 1st (and in this case, only) retrieval : by doing this in an adjecent column:
=MATCH(G47,INDIRECT("'Sheet1'!$D$"&X47&":$M$"&X47),0) where my grid of data to look at and search is in sheet1D7:M51 and X47 is the result of your formula on the data. Ideally would like to have your result be row1|row2|....|rown depending how many matches there are (the occurances of the value-string to find its occurances).
Yes, I could cocatenate the original data grid d7:m51,
so it reads in 1 column, d7|e7|f7|g7|jh7|i7|j7|k7|l7|m7
drag down to row 51, and do multi value return match against that,
similar to https://www.get-digital-help.com/2012/03/28/search-for-a-text-string-and-return-multiple-adjacent-values/#ab
but wondered if it could be done on the grid/table as it stands based on your original formula on this page.
That would be powerful.
Try this array formula:
=IFERROR(INDEX(Table1&(ROW(Table1)-MIN(ROW(Table1))+1)&","&COLUMN(Table1)-MIN(COLUMN(Table1))+1,SMALL(IF(ISNUMBER(FIND($C$9,Table1)),ROW(Table1)-MIN(ROW(Table1))+1,""),ROW(Table1)-MIN(ROW(Table1))+1),COLUMN(Table1)-MIN(COLUMN(Table1))+1),"")
I knew this was going to be super hard....
These the names im searching for:
B C # of times they appear in my table to Search in
Phillip Pettus 1
Lynn Greer 1
Marcel Black 1
Parker Moore 3
Danny Crawford 1
Phil Williams 3
Ken Johnson 2
Terri Collins 1
Ed Henry 1
Mike Ball 1
Randall Shedd 1
Corey Harbison1
Connie Rowe 1
Tim Wadsworth1
Allen Farley 1
Kyle South 1
for Wadworth, which appears in row 47 in the table, (and only once throughout) it returns: 4743||1
(ive changed the delimator to || ).
for Ball, I get 73||1
and for WIlliams (appers 3 times, first time in row 51), i get 5147||1
I cant make heads or tails of the output, further to fist 1 or 2 characters.
If the delimator is , or ||, the numbers its returning are just all 1 . Williams and Johnson should have 3 and 2 after it.
In anycase, you have helped me to think about this more logically, but the formula doesnt work.
The output ive got for the datA above is,
3935||1
2319||1
73||1
3531||1
1511||1
5147||1
3127||1
1511||1
2723||1
73||1
4339||1
2319||1
4339||1
4743||1
1915||1
4339||1
doent make sense given what I want it to do/identify. But we are getting somewhere...I believe.
I do thank you for your help regardless. Totally. You have no obligation to.
Total star. I just want to let you know that. Thank you.
David Wooley,
I believe the formula is working, 47 are the two first digits in 4743. 51 -> 5147 and so on. Are the two last digits perhaps column numbers?
What does the following formula return?
=IFERROR(INDEX(Table1&(ROW(Table1)-MIN(ROW(Table1))+1),SMALL(IF(ISNUMBER(FIND($C$9,Table1)),ROW(Table1)-MIN(ROW(Table1))+1,""),ROW(Table1)-MIN(ROW(Table1))+1),COLUMN(Table1)-MIN(COLUMN(Table1))+1),"")
P.s. I can live without getting the column nunbers, I can get those myself, If i get the apperaances per row, like row x, row y, row z , and or (perhaps as I think you were doing, highlighting the number of times the object-string appears in the table), I can work out the rest myself.
In anycase. Absolutely Fantastic world stopping, jaw-dropping Formula regardless !!! (just need the number of times the string appears! Not just the first time!) !!!!
No, Those other 2 numbers are not the columns.
South, located at row 43 column 10, returns "4339".
Johnson, located at row 31, column 2 and column 3, returns "3126"
Black, located in row 7, column 9, returns "72"
Garrett, located in row 23, column 9, returns "2318"
Rogers. row 34, Column 3, retuns "4338"
Martin, row 35 column 7, returns "3530"
I need to rest! So the first 2 (or 1) digits give me what your orignal formula did - the row number. But the last 2 (two). I have no idea. i need to rest and try to figure this out
I think you already have the answer Oscar, its :
=IFERROR(INDEX(Table1,SMALL(IF(FREQUENCY(IF(ISNUMBER(FIND($C$9,Table1)),ROW(Table1)-MIN(ROW(Table1))+1,""),ROW(Table1)-MIN(ROW(Table1))+1)>0,ROW(Table1)-MIN(ROW(Table1))+1,""),ROW(B1)),COLUMN(B1)),"")
but the results & its application, all in one cell..
Likewise, & I think this is better,
=IFERROR(INDEX(Table1,SMALL(IF(ISNUMBER(FIND($C$9,Table1)),ROW(Table1)-MIN(ROW(Table1))+1,""),ROW(Table1)-MIN(ROW(Table1))+1),COLUMN(Table1)-MIN(COLUMN(Table1))+1),"")
But need to adapt it to return only the row number of the occurances, and put them in the same output cell, concatonated.
Thank you Oscar! This was helpful.
Ash,
Thanks!
Hi Oscar,
Thank you for this post.
I have a somewhat related question, if you don't mind:
I have very large amount of text in a single cell, and I would like to extract multiple instances of text that appear between two specific words.
For example, here is the sample text in one cell:
{"date": 5/7/19 headline:"GE Posts Profit" source:"CNBC"}{"date": 5/8/19 headline:"GE Dividend Shrink" source:"MSN"}{"date": 5/9/19 headline:"GE Bankrupt" source:"WSJ"}
This following formula does a good enough job of extracting the first headline:
=MID(C2,SEARCH("headline",C2)+2,SEARCH("source:",C2)-SEARCH("headline",C2)-4)
However it only extracts the first headline and nothing after it.
If possible, I would like to extract all of headlines within the text in that cell, and generate a vertical array of those headlines so that it looks like this:
GE Posts Profit
GE Dividends Shrink
GE Bankrupt
Is this possible?
Thanks very much.
Hi Blake,
I built a user defined function, please see this article:
https://www.get-digital-help.com/2019/05/13/extract-text-between-words-udf/
Hi there
Hi there,
Wondering how can I extract all rows that where one of the cells that contains a specific text. Please refer to the url below.
I would like to extract the row that contains 'Lee' in the assistant column but with the 'tooth' status.
https://imgur.com/AnjB0Wi
Addelyn,
did you try the Advanced filter?
https://www.get-digital-help.com/search-for-a-text-string-in-an-excel-table/#adv
Use the asterisk * to filter rows that contain the search value. In your case: *Lee*
Hi Oscar,
I am prisilla tan and i have just started my excel learning journey (self learning) out of interest.
Firstly, thank you for this very interesting article.
I am intrigued to know.. what if there are multiple search strings if each table 1 cell has a unique value in it?
Search table
A123 A456 A789 A1011
B123 B456 B789 B1011
C123 C456 C789 C1011
D123 D456 D789 D1011
Search string 1: A456
search string 2: D789
search string 3: C1011
Search String 4:
Search Result 1: A123 A456 A789 A1011
Search Result 2: D123 D456 D789 D1011
Search Result 3: C123 C456 C789 C1011
Search Result 4:
Looking forward to understand/solve my queries from you :)
Please share how it would be done will be a great whenever you are available
Thank you
Hello Oscar,
Using the Advanced Filter option and your example data above, how would you find and select all rows containing either AA or DD (I have a sheet containing known misspellings of a person's name in any of three columns and I would like to select all the records together.)? You noted the use of OR-logic which I believe would work, but I do not know how to implement it in the criteria table.
Regards,
Jason
Hi Oscar,
Whenever I type a word into my search box the data doesn't refresh. It keeps the data from the first search I typed in. The one that I made the table with. Is there a way to get it to refresh after each search or is it supposed to be automatic and I'm missing something? Any help would be useful. Thank you!
-Kaitlin
Kaitlin
Yes, the data should refresh automatically unless you are using the Advanced Filter?
Is Automatic recalculation enabled?
1. Go to tab "Formulas" on the ribbon.
2. Press with left mouse button on the "Calculation Options" button.
3. Verify that Automatic is selected.
Hi Oscar,
I have an issue with this formula. When i put on array on a range of cells and let them calculate similarities with the cell C11 happens to duplicate the results from nowhere.
=SI.ERROR(INDICE(SENTENCIADOS!$A$1:$F$30000;K.ESIMO.MENOR(SI(ESNUMERO(HALLAR($C$11;SENTENCIADOS!$A$1:$F$30000));COINCIDIR(FILA(SENTENCIADOS!$A$1:$F$30000);FILA(SENTENCIADOS!$A$1:$F$30000));"");FILAS($A$1:A1));COLUMNAS($A$1:A1));"")Good Evening Oscar- thank you for putting this helpful formula together. Just wanted to send an note of appreciation from Boston, MA, USA.
Thank you, Emily.
Hi
Is possible to sum all WA11?
(A1) WA11 4
(A2) AdBlue 1, WA11 223
(A3) AdBlue 3, WA11 32, shift 4
... and everything is in one column.
Thanks you very much for your help.
Sincerely Marko