Author: Oscar Cronquist Article last updated on August 06, 2022

Extract multiple values between two given strings

The image above demonstrates a rather small formula in cell D3 that extracts values in cell B3 based on two given strings or delimiters.

1. Extract values between two given delimiting strings

Extract multiple values between two given strings1

Cell B3 contains phone numbers, they start with a given string "#" and end with another string "|". The formula is able to extract any value without any changes to the formula, this example demonstrates phone numbers.

The delimiting strings may contain multiple characters if needed. The dynamic array formula in cell D3 extracts each value between these strings and spills values below as far as needed automatically.

Excel 365 formula in cell D3:

=TEXTAFTER(TEXTSPLIT(B3,,"|",TRUE),"#",,,,"")

Explaining formula

Step 1 - Create an array based on the last delimiting string

The TEXTSPLIT function splits a string into an array across columns and rows based on delimiting characters.

TEXTSPLIT(Input_Textcol_delimiter, [row_delimiter], [Ignore_Empty])

TEXTSPLIT(B3, , "|", TRUE)

becomes

TEXTSPLIT("Fake phone numbers: #555-6426262|and #555-5769326| and #555-94721| or #555-79324|", , "|", TRUE)

and returns

{"Fake phone numbers: #555-6426262"; "and #555-5769326"; " and #555-94721"; " or #555-79324"}.

Step 2 - Remove characters before the first delimiting string

The TEXTAFTER function extracts a string after a specific substring in a given value.

TEXTAFTER(input_text,text_after, [n], [ignore_case])

TEXTAFTER(TEXTSPLIT(B3,,"|",TRUE),"#",,,,"")

becomes

TEXTAFTER({"Fake phone numbers: #555-6426262"; "and #555-5769326"; " and #555-94721"; " or #555-79324"},"#",,,,"")

and returns

{"555-6426262"; "555-5769326"; "555-94721"; "555-79324"; ""}.

Back to top

Extract multiple values between two given strings2

This formula is not much different from the one above, except that it allows you to specify delimiting strings in cells E2 and E3 respectively.

Excel 365 formula in cell D6:

=TEXTAFTER(TEXTSPLIT(B3, , E3, TRUE), E2,,,,"")

There are leading and trailing spaces in the example above, use the TRIM function to remove those.

Excel 365 formula in cell D6:

=TRIM(TEXTAFTER(TEXTSPLIT(B3, , E3, TRUE), E2,,,,""))

2. Extract values between two given delimiting strings in a cell range

Extract multiple values between two given strings in a cell range

The following formula lets you extract values between two given strings from a cell range.

Excel 365 formula in cell D3:

=TEXTAFTER(TEXTSPLIT(TEXTJOIN(, , B3:B10), , "|", TRUE), "#")

TEXTJOIN function character limit

Note, the TEXTJOIN function has a limit of 32767 characters. The formula returns an error if you use more characters than that.

Explaining formula

Step 1 - Merge values

The TEXTJOIN function merges values from multiple cell ranges and also use delimiting characters if you want.

TEXTJOIN(delimiterignore_emptytext1[text2], ...)

TEXTJOIN(, , B3:B10)

becomes

TEXTJOIN(, , {"Fake phone numbers: #555-6426262|and #555-5769326| and #555-94721| or #555-79324|";0;0;0;0;0;0;"Fake random phone numbers: #555-28462|and #555-19283| and #555-8883452| ? #555-6532877|"})

and returns

"Fake phone numbers: #555-6426262|and #555-5769326| and #555-94721| or #555-79324|Fake random phone numbers: #555-28462|and #555-19283| and #555-8883452| ? #555-6532877|".

Step 2 - Create an array based on the last delimiting string

The TEXTSPLIT function splits a string into an array across columns and rows based on delimiting characters.

TEXTSPLIT(Input_Textcol_delimiter, [row_delimiter], [Ignore_Empty])

TEXTSPLIT(TEXTJOIN(, , B3:B10), , "|", TRUE)

becomes

TEXTSPLIT("Fake phone numbers: #555-6426262|and #555-5769326| and #555-94721| or #555-79324|Fake random phone numbers: #555-28462|and #555-19283| and #555-8883452| ? #555-6532877|", , "|", TRUE)

and returns

{"Fake phone numbers: #555-6426262"; "and #555-5769326"; " and #555-94721"; " or #555-79324"; "Fake random phone numbers: #555-28462"; "and #555-19283"; " and #555-8883452"; " ? #555-6532877"}

Step 3 - Remove characters before the first delimiting string

The TEXTAFTER function extracts a string after a specific substring in a given value.

TEXTAFTER(input_text,text_after, [n], [ignore_case])

TEXTAFTER(TEXTSPLIT(TEXTJOIN(, , B3:B10), , "|", TRUE), "#")

becomes

TEXTAFTER({"Fake phone numbers: #555-6426262"; "and #555-5769326"; " and #555-94721"; " or #555-79324"; "Fake random phone numbers: #555-28462"; "and #555-19283"; " and #555-8883452"; " ? #555-6532877"}, "#")

and returns

{"555-6426262";"555-5769326";"555-94721";"555-79324";"555-28462";"555-19283";"555-8883452";"555-6532877"}

Back to top