How to extract text between two characters or words in Excel and Google Sheets

The complete guide on how to find and extract text from string between two characters or words in Excel and Google Sheets.

When working with long strings, you may often want to extract a specific part of them for closer examination. In Microsoft Excel and Google Sheets, there are several ways to get text between two characters or substrings. In this article, we'll discuss the fastest and most effective ones.

How to extract text between two characters in Excel

To extract text between two different characters, you can use this generic formula:

MID(cell, SEARCH(char1, cell) + 1, SEARCH(char2, cell) - SEARCH(char1, cell) - 1)

For example, to get text between parentheses from the string in A2, the formula is:

=MID(A2, SEARCH("(", A2)+1, SEARCH(")", A2) - SEARCH("(", A2) -1) Extracting text between two characters in Excel

In a similar manner, you can extract text between braces, square brackets, angle brackets, and so on.

Useful tip! If you are dealing with numbers and want the result to be a number and not a numeric string, then additionally perform some arithmetic operation that does not change the result, e.g. add 0 or multiply by 1.

For example:

=MID(A2, SEARCH("(", A2)+1, SEARCH(")", A2) - SEARCH("(", A2) -1) *1

Please notice the default right alignment of the extracted values in cells typical for numbers: Getting number between two characters in Excel

How this formula works

The base of this formula is the MID function that pulls a given number of characters from a string, starting at a specific position:

MID(text, start_num, num_chars)

Text is the cell containing the original string (A2).

The starting position (start_num) is the character that immediately follows the opening parenthesis. So, you find the position of "(" using the SEARCH function and add 1 to it:

SEARCH("(", A2) +1

To figure out how many characters to extract (num_chars), you locate the position of the closing parentheses and subtract the position of the opening parentheses from it. Additionally, you subtract 1 to leave out the second parentheses:

SEARCH(")", A2) - SEARCH("(", A2) -1

Having all the necessary details, the MID function brings you exactly what you want – text between parentheses in our case:

MID(A2, 19, 2)

As MID is a text function, it always produces a string, even if the extraction only includes numbers. To get a number as the final result, we multiply MID's output by one or add zero to it. If you are extracting text, this operation is not required.

Extract text between two strings / words in Excel

To pull text between two strings or words, the formula is quite similar to the one discussed above. Only a couple of different adjustments are needed: to remove the delimiter-words from the final result, you add and subtract the length of the words themselves returned by the LEN function:

MID(cell, SEARCH(word1, cell) + LEN(word1), SEARCH(word2, cell) - SEARCH(word1, cell) - LEN(word1))

For example, to extract substrings between the words "start" and "end", the formula is:

=IFERROR(MID(A2, SEARCH("start ", A2) + LEN("start "), SEARCH(" end", A2) - SEARCH("start ", A2) - LEN("start ")), "") Formula to extract text between two words in Excel

Tips:

  • To avoid leading and trailing spaces in the results, include a space after word 1 such as "start " and before word 2 such as " end". Alternatively, you can use the TRIM function to get rid of extra spaces.
  • If one or both of the specified words are not found in the original string, the formula will return the #VALUE! error. To catch that error and replace it with an empty string (""), use the IFERROR function like shown in the above example.

Get text between two instances of the same character in Excel

To extract text from a string between two occurrences of the same character, the generic formula is:

MID(cell, SEARCH(char, cell) +1, SEARCH (char, cell, SEARCH (char, cell) +1) - SEARCH (char, cell) -1)

For example, to extract text between double quotes from the string in A2, you enter this formula in B2:

=MID(A2, SEARCH("""", A2) +1, SEARCH("""", A2, SEARCH("""",A2) +1) - SEARCH("""", A2) -1)

Please pay attention to the way you search for a double quote in Excel. Between the outer quotes, another set of quotes is entered. The first quote is used to escape a special meaning of the second quote so that "" in between the outermost quotes stands for a single double quote.

Another way to supply a double quote (") to an Excel formula is by using the CHAR function with the code number 34.

=MID(A2, SEARCH(CHAR(34), A2) +1, SEARCH(CHAR(34), A2, SEARCH(CHAR(34),A2) +1) - SEARCH(CHAR(34), A2) -1) Get text between double quotes.

How this formula works

The first two arguments of this MID formula raise no questions:

  • A2 is the text string to search, and
  • SEARCH("""", A2) +1 is the starting number, i.e. the position of the first quote +1.

The trickiest part is calculating the number of characters to extract (num_chars):

First, we find the position of the second quote by nesting one SEARCH function within another.

SEARCH("""", A2, SEARCH("""",A2) +1)

From the position of the 2nd quote (in A2 it's 27), you subtract the position of the 1st quote (in A2 it's 10), and then subtract 1 to exclude the quote itself from the result:

SEARCH("""", A2, SEARCH("""",A2) +1) - SEARCH("""", A2) -1

The above formula returns 16, which is the last missing piece of a puzzle the MID function needs:

=MID(A2, 10+1, 16)

Simply put, MID searches the cell A2 starting from the character after the 1st quote (10+1) and returns the next 16 characters.

Case-sensitive Excel formula to extract text between characters

As you probably know, in Microsoft Excel there are two functions to search strings: SEARCH (case-insensitive) and FIND (case-sensitive).

In situation when the delimiter is a letter in a specific case, just use FIND instead of SEARCH. To illustrate the difference, let's compare the two formulas below.

From the string in A2, suppose you want to extract a number between two uppercase letters "X".

The SEARCH function works incorrectly in this case because it does not distinguish between "x" and "X":

=MID(A2, SEARCH("X", A2) +1, SEARCH("X", A2, SEARCH("X",A2) +1) - SEARCH("X", A2) -1) +0

As the result, the text between "x" is extracted, and not between "X" that we are looking for: Case-insensitive formula to get text between two characters

While the case-sensitive FIND function works beautifully:

=MID(A2, FIND("X", A2) +1, FIND("X", A2, FIND("X",A2) +1) - FIND("X", A2) -1) +0

And brings exactly the result we need: Case-sensitive formula to extract text between two characters

Extracting text between characters in Excel 365

In Excel 365, you can get text between characters more easily by using the TEXTBEFORE and TEXTAFTER functions together.

TEXTBEFORE(TEXTAFTER(cell, char1), char2)

For example, to extract text between parentheses, the formula is as simple as this:

=TEXTBEFORE(TEXTAFTER(A2, "("), ")") Simple formula to extract text between parentheses

This formula also works nicely for extracting text between two occurrences of the same character.

For instance, to get text between double quotes, the formula takes this form:

=TEXTBEFORE(TEXTAFTER(A2, """"), """") Excel 365 formula to get text between double quotes

By default, both the TEXTAFTER and TEXTBEFORE functions are case-sensitive. To disable case-sensitivity, you set the 4th argument (match_mode) to 1 or TRUE.

For example, the case-insensitive formula below recognizes both the lowercase "x" and uppercase "X" as the delimiter:

=TEXTBEFORE(TEXTAFTER(A2, "x ", ,1), " x", ,1) +0 Case-insensitive formula to extract text between characters in Excel 365

How this formula works

Working from the inside out, you use the TEXTAFTER function to extract text after the opening parentheses:

TEXTAFTER(A2, "(")

And serve the returned substring to TEXTBEFORE, asking it to find the closing parentheses in that substring and return the text before it.

TEXTBEFORE("Unexpected error)", ")")

Done :)

Get text between two characters in Google Sheets

To find text between two characters in Google Sheets, you don't need to reinvent the wheel :)

The MID SEARCH combinations used in Excel work flawlessly in Google spreadsheets too.

For instance, to get text between brackets, the formula is:

=MID(A2, SEARCH("[", A2)+1, SEARCH("]", A2) - SEARCH("[", A2) -1) Getting text between brackets

To extract text between two commas, this is the formula to use: Extracting text between two commas

That's how to extract text between two characters or words in Microsoft Excel and Google spreadsheets. I thank you for reading and hope to see you on our blog next week!

Practice workbooks

Extract text between characters in Excel (.xlsx file)
Get text between characters in Google Sheets (online sheet)

You may also be interested in: