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_at to 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 empty

In 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 within text_to_search.
• text_to_search: The text in which to search for search_for.
• starting_at (optional): The position, in number of characters in text_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 9

The 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: A1
• starting_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) + 1 returns the position of the first letter after the first space (named fs).
• SEARCH(" ", A1, fs) returns the position of the second space character (named ss).
• ss-fs returns the length of the middle name (named n).

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.

Resources

• Multiple Search Strings in a Single SEARCH Formula in Google Sheets
• Logical AND, OR Use in SEARCH Function in Google Sheets
• Formula to Reverse Text and Numbers in Google Sheets