Extract values between two given delimiting 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.
Table of Contents
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"; ""}.
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"}
Extract category
The array formula in cell C3 extracts the first character from first, middle and last name. The formula works fine […]
This article demonstrates a formula that filters unique distinct single digits from a cell range containing numbers. Cell range B3:B6 […]
This article demonstrates an array formula that returns the table header based on a condition. For example, in cell C8 […]
Excel categories
Leave a Reply
How to comment
How to add a formula to your comment
<code>Insert your formula here.</code>
Convert less than and larger than signs
Use html character entities instead of less than and larger than signs.
< becomes < and > becomes >
How to add VBA code to your comment
[vb 1="vbnet" language=","]
Put your VBA code here.
[/vb]
How to add a picture to your comment:
Upload picture to postimage.org or imgur
Paste image link to your comment.