TEXTBETWEEN

The TEXTBETWEEN function returns a string value consisting of all characters that appear between two given substrings in the original string value.

TEXTBETWEEN(source-string, first-search-string, second-search-string, first-occurrence, second-occurrence)

source-string: Any value.

first-search-string: The string value to search for as the first match.

second-search-string: The string value to search for as the second match.

first-occurrence: An optional value indicating which occurrence of first-search-string within source-string to match (1 for first match, 2 for second match, …, -1 for last match, -2 for second to last match, …). If omitted, set to 1.

second-occurrence: An optional value indicating which occurrence of second-search-string within source-string (after first-occurrence) to match (1 for first match, 2 for second match, …, -1 for last match, -2 for second to last match, …). If omitted, set to 1.

Notes

By default, if there are multiple occurrences of first-search-string in source-string and no value is specified for first-occurrence, TEXTBETWEEN returns text starting from the first occurrence. If there are multiple occurrences of second-search-string in source-string and no value is specified for second-occurrence, TEXTBETWEEN returns text up to the first occurrence of second-search-string after the first occurrence of first-search-string.
REGEX is permitted in search-string for more complex searches.
By default, the search isn’t case sensitive. To consider case in your search, use the REGEX function for search-string.

Examples
=TEXTBETWEEN("Apple,Banana,Strawberry", ",", ",") returns "Banana." =TEXTBETWEEN("Extract the text between the first occurrence of the and the last occurrence of text", "the", "text", 1, -1) returns " text between the first occurrence of the and the last occurrence of ". =TEXTBETWEEN("Extract the text between the first occurrence of the and the last occurrence of text", "text", "the") returns " between ". =TEXTBETWEEN("Get all the text between the two numbers 45120 and 31233", REGEX("[0-9]+"), REGEX("[0-9]+")) returns " and ".

Examples

=TEXTBETWEEN("Apple,Banana,Strawberry", ",", ",") returns "Banana."

=TEXTBETWEEN("Extract the text between the first occurrence of the and the last occurrence of text", "the", "text", 1, -1) returns " text between the first occurrence of the and the last occurrence of ".

=TEXTBETWEEN("Extract the text between the first occurrence of the and the last occurrence of text", "text", "the") returns " between ".

=TEXTBETWEEN("Get all the text between the two numbers 45120 and 31233", REGEX("[0-9]+"), REGEX("[0-9]+")) returns " and ".

See alsoTEXTAFTER TEXTBEFORE REGEX

Helpful?

Formulas and Functions Help

TEXTBETWEEN

Notes