## 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.

**Contact Oscar**

You can contact me through this contact form