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