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

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:

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_Text, col_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

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:

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

Excel 365 formula in cell D6:

2. Extract values between two given delimiting 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:

TEXTJOIN function character limit

Explaining formula

Step 1 - Merge values

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

TEXTJOIN(delimiter, ignore_empty, text1, [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_Text, col_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

Extract category

How to create name initials
The array formula in cell C3 extracts the first character from first, middle and last name. The formula works fine […]

Sort and return unique distinct single digits from cell range
This article demonstrates a formula that filters unique distinct single digits from a cell range containing numbers. Cell range B3:B6 […]

Extract table headers based on a condition
This article demonstrates an array formula that returns the table header based on a condition. For example, in cell C8 […]

Excel categories

Leave a Comment