This tutorial explains how to use the MID and SEARCH functions standalone as well as in combination in Google Sheets.
Understanding their roles helps explain why they are covered together in one tutorial. Here are their functions and roles:
The SEARCH function returns the position of a string where it is first found within text. The MID function returns a segment of a string starting from a specified position. By using SEARCH, we can dynamically determine the starting point for MID.
The SEARCH function is also useful for filtering data when you want to apply a partial match, especially when the function in use doesn’t support wildcards. We will demonstrate this in the example section below.
SEARCH is a case-insensitive function. If you need a case-sensitive search, use its counterpart, the FIND function.
MID Function in Google Sheets
Syntax of the MID Function:
MID(string, starting_at, extract_length)Arguments:
string: The string to extract a segment from.starting_at: The starting point, in number of characters from the left of the string, from which to begin extracting.extract_length: The length of the segment to extract.
What if the starting_at or extract_length is out of bounds?
- The formula will return an empty string if the starting point is out of bounds.
- If the starting point is valid but the length to extract extends beyond the end of the string, it will return the characters from
starting_atto the end of the string.
Examples:
=MID("Invoice #121", 10, 3) // returns "121"
=MID("Invoice #121", 10, 10) // returns "121"
=MID("Invoice #121", 25, 3) // returns emptyIn these examples, if the ‘string’ is in cell A1, you can replace “Invoice #121” with A1 in the formulas.
We will see the real-life use of the MID function when we use it with the SEARCH function in the examples below.
SEARCH Function in Google Sheets
Syntax:
SEARCH(search_for, text_to_search, [starting_at])Arguments:
search_for: The string to search withintext_to_search.text_to_search: The text in which to search forsearch_for.starting_at(optional): The position, in number of characters intext_to_search, from which to start the search. The default is 1.
Functionality:
Performs a case-insensitive search for a segment within a string.
Examples:
=SEARCH("#", "Invoice #121") // returns 9The formula may return a #VALUE! error if search_for is not found within text_to_search. To handle errors, use IFERROR with the SEARCH function:
=IFERROR(SEARCH(",", "Invoice #121"))Note: The SEARCH function returns the starting position of search_for in the text_to_search. The count will always start from the very first character of the text_to_search, not from the starting_at.
Example using SEARCH as a condition in the FILTER function:
=FILTER(A1:A, SEARCH("apple", A1:A))
The formula filters rows in column A where the text contains ‘apple’. As shown in the screenshot, column A contains combined data of fruit names and grades.
This demonstrates using SEARCH in an array context. While typically used with ARRAYFORMULA for array operations, functions like FILTER, INDEX, and SORT do not require ARRAYFORMULA.
This explanation clarifies the usage and capabilities of the SEARCH function in Google Sheets.
MID and SEARCH Functions Combo
The purpose of introducing MID and SEARCH functions in one tutorial is to show you how powerful this combination can be.
We often use SEARCH within MID, and here are a few examples.
If cell A1 contains the first and last name of a person, you can use the following formula to return their last name:
=MID(A1, SEARCH(" ", A1)+1, 100)In terms of the MID function:
string: A1starting_at:SEARCH(" ", A1) + 1(finds the position of the first space and adds 1)extract_length: 100 (a sufficiently large number)
If cell A1 contains the name “Sandra Burns”, the formula will return “Burns”.
If cell A1 contains the first, middle, and last name of a person, to extract the middle name use this formula:
=LET(fs, SEARCH(" ", A1)+1, ss, SEARCH(" ",A1, fs), n, ss-fs, MID(A1, fs, n))Where:
SEARCH(" ", A1) + 1returns the position of the first letter after the first space (namedfs).SEARCH(" ", A1, fs)returns the position of the second space character (namedss).ss-fsreturns the length of the middle name (namedn).
The LET function is used to assign names to these value expressions and avoid repeating them in the formula. These named values are then used in the MID function to extract the middle name.
You can find more information about these functions here: Extract First, Last, and Middle Names in Google Sheets (Formula Options)
Note: For advanced text extraction, consider using the REGEXEXTRACT function.
Brilliant Prashanth. I did see the corrections in your linked tutorial and noted the missing SUM. thank you!
Dear Prashanth. Am I correct that the following code is equivalent? It seems
=if(IFERROR(search("water",A3)>0,0) OR IFERROR(search("air",A3)>0,0),"Environment","Not Environment")Equivalent to:
=if( ArrayFormula( iferror( search({"water";"air";"soil";"sky";"earth"},
A3)))>0, "Environment","Not Environment")
In this way, I can easily include more words separated by semicolons…
Hi, Jason,
The second formula is missing the SUM after the IF. It was not your mistake. In my linked tutorial (shared via my previous reply), in the first example, I forgot to include that. Corrected there and also updated your example sheet.
Dear Prashanth, I am trying to combine your powerful tutorials on IF and SEARCH.
If I have text in column O this searches the text and determines whether the word similar to water is present (water, waterfall, waterman)… and outputs Environment (if this is true)
=if(IFERROR(search("water",O3)>0,0),"Environment","Not Environment")but I want to search whether there is the word “water” or “air” in column O like this:
=if(IFERROR(search("water",O3)>0,0) OR IFERROR(search("air",O3)>0,0),"Environment","Not Environment")Hi, Jason,
Using the SEARCH function;
=if(iferror(search("water",O3))+iferror(search("air",O3))>0,"Environment","Not Environment")The above formula won’t be practical if you have to test more words in a cell. In the above example, there are only two words.
If you use REGEXMATCH, you can easily include more words separated by the Pipe as below.
=if(REGEXMATCH(O3,"water|air"),"Environment","Not Environment")If there is one more word and that is “tree”, you can include it as “water|air|tree”.
VERY CLEVER, Prashanth (SO elegant and practical…) thank you! darn… but it seems the SEARCH function can pick up “waterpick”, “waterfall”, & “underwater”… while REGEXMATCH cannot.
On a related note, can one OUTPUT whether EITHER/BOTH water or air words are present as follows?
=if(IFERROR(search("water",O3)>0,0),"water","no water") +IFERROR(search("air",O3))>0,0,"air","no air")and ideally, output “air and water” if both are present???
Hi, Jason,
The first issue with Regex you can solve by using either of the functions Lower or Upper with O3 as below.
LOWER:-
=if(REGEXMATCH(lower(O3),"water|air"),"Environment","Not Environment")UPPER:-
=if(REGEXMATCH(upper(O3),"WATER|AIR"),"Environment","Not Environment")Regarding your new requirement, try this one.
=if(and(isnumber(SEARCH("water",O3))=TRUE,isnumber(SEARCH("air",O3))=FALSE),
"water",if(and(isnumber(SEARCH("water",O3))=FALSE,
isnumber(SEARCH("air",O3))=TRUE),
"air",if(and(isnumber(SEARCH("water",O3))=TRUE,
isnumber(SEARCH("air",O3))=TRUE),
"air and water","Custom Message")))
To learn more, you may please go through this tutorial.
Logical AND, OR Use in SEARCH Function in Google Sheets.