Excel Formula to Extract Text Between Two Strings

Updated 5 November 2026 · Standard formula, shown below

To extract text between two texts in Excel, use TEXTBEFORE and TEXTAFTER together: =TEXTBEFORE(TEXTAFTER(A1,"start"),"end"). Replace A1, start, and end with your cell and the exact boundary strings; the formula returns the text after the first boundary and before the next one.

How the formula works

TEXTAFTER first removes everything through the starting text. TEXTBEFORE then takes the part of what remains that comes before the ending text. For example, if A1 contains Order: 5837; Status: packed, this formula extracts the order number:

=TEXTBEFORE(TEXTAFTER(A1,"Order: "),";")

The result is 5837. The boundary texts are not included in the result. This approach is useful when a value is surrounded by labels or separators that are consistent, even if the content between them varies in length.

Another example: if A1 contains Product [blue-large] available, use =TEXTBEFORE(TEXTAFTER(A1,"["),"]") to return blue-large. The delimiters can be words, spaces, punctuation marks, or other character sequences.

Steps to build and adapt it

Identify the cell containing the full text.
Copy the exact text that comes immediately before the section you want. This is the starting boundary.
Copy the exact text that comes immediately after the section you want. This is the ending boundary.
Enter =TEXTBEFORE(TEXTAFTER(A1,"start"),"end"), replacing the sample cell and boundary strings.
Press Enter, then fill or copy the formula down if you need results for other rows.

For instance, to pull a username from text formatted as User: robin | Region: West, use =TEXTBEFORE(TEXTAFTER(A1,"User: ")," |"). The space before the pipe is part of the ending boundary, so it is excluded along with the pipe and everything after it.

If the boundary text itself contains quotation marks, double each quote inside the formula string. In ordinary formulas, text values must be enclosed in straight double quotation marks, such as "[". Cell references, by contrast, are written without quotes.

Excel version and compatibility

TEXTBEFORE and TEXTAFTER are available in newer Excel versions, including Microsoft 365 and Excel 2024. If Excel does not recognize either function and displays a function-name error, your edition may not include them. Check the Excel version or use a compatible alternative below.

These functions also have optional arguments for choosing which occurrence of a boundary to use and whether matching should be case-sensitive. The basic formula uses the first occurrence and is not case-sensitive by default. For example, =TEXTBEFORE(TEXTAFTER(A1,"<"),">") extracts the text inside the first matching pair of angle brackets.

When case matters, the functions’ optional arguments can change their matching behavior. The full syntax is TEXTAFTER(text, delimiter, [instance_num], [match_mode], [match_end], [if_not_found]) and TEXTBEFORE(text, delimiter, [instance_num], [match_mode], [match_end], [if_not_found]). In both, a match mode of 1 makes the search case-sensitive; the default is case-insensitive. Optional arguments must be supplied in order, so use commas to leave an earlier argument blank.

Extracting between repeated or multiple delimiters

If the starting or ending text appears multiple times, the default behavior may not select the pair you expect. Use the instance_num argument to specify which occurrence to search for. A positive number counts from the beginning; a negative number counts from the end. For example, to extract what follows the second colon and ends at the next semicolon, you can write:

=TEXTBEFORE(TEXTAFTER(A1,":",2),";")

Test this with representative data before filling a column, particularly if some rows contain extra delimiters. If the desired section is between the last opening marker and last closing marker, choosing negative occurrence numbers may be more suitable, but confirm that both boundaries are counted as intended.

For more complex layouts, such as nested brackets, “between” can be ambiguous. A simple formula finds matching occurrences according to their position; it does not parse nested structures or understand that one opening bracket belongs to a particular closing bracket.

Older Excel alternatives

In versions without TEXTBEFORE and TEXTAFTER, combine MID, SEARCH, and LEN. For the first occurrence of two boundaries, use:

=MID(A1,SEARCH("start",A1)+LEN("start"),SEARCH("end",A1,SEARCH("start",A1)+LEN("start"))-SEARCH("start",A1)-LEN("start"))

This formula locates the starting text, moves past its characters, then measures the distance to the ending text and returns that many characters. Replace both boundary strings and A1. SEARCH is not case-sensitive; use FIND instead if you need case-sensitive matching. This alternative is longer and harder to maintain, so use the newer functions when available.

For repeated extraction across many rows, Power Query can also split text by delimiters and transform a whole column. It is worth considering when the source format is irregular or the extraction is part of a repeatable data-cleaning workflow. For a single cell or a straightforward column, a worksheet formula is usually easier to inspect.

Common errors and fixes

Boundary not found: The new functions return #N/A if a required delimiter is absent, unless you provide an if_not_found value. Check spaces, punctuation, and spelling. For example, "Name:" and "Name: " are different delimiters.
Ending boundary appears before the start: The formula cannot find the end delimiter in the remaining text. Confirm the order and that both boundaries occur in the same cell.
Wrong section returned: A delimiter may occur more than once. Specify an occurrence with instance_num or choose a more distinctive boundary.
Unexpected extra spaces: If spaces are outside the selected boundaries, wrap the result in TRIM, as in =TRIM(TEXTBEFORE(TEXTAFTER(A1,"["),"]")). Note that TRIM also reduces repeated internal spaces to one.
Formula displayed as text: Check that the cell is not formatted as Text, and that the formula begins with =. If needed, change the format to General and re-enter the formula.

Do not assume that every row has the same format. A formula that works for one example can return an error or the wrong text when a row is blank, a delimiter is missing, or the source includes an unexpected extra marker. If missing boundaries are normal, the optional if_not_found argument can return a chosen value, such as an empty string: =TEXTBEFORE(TEXTAFTER(A1,"[",,,,""),"]",,,," "). Because optional arguments are positional, verify the commas carefully; simpler error handling can be easier to read with IFERROR.

Frequently Asked Questions

Can I extract text between two different words?
Yes. Put the starting word or phrase in TEXTAFTER and the ending word or phrase in TEXTBEFORE, for example =TEXTBEFORE(TEXTAFTER(A1,"from ")," to").

Does the formula include the two boundary texts?
No. It returns only the characters between them. To include a boundary, concatenate it to the result or adjust the formula to take a larger section.

How do I return blank instead of an error?
Wrap the expression with IFERROR, as in =IFERROR(TEXTBEFORE(TEXTAFTER(A1,"["),"]"),""). This returns an empty string when the extraction fails.

How can I extract between the second pair of delimiters?
Use the optional occurrence argument in both functions as needed. For example, TEXTAFTER(A1,"[",2) starts after the second opening bracket; then TEXTBEFORE finds the next closing bracket.

Can the formula distinguish uppercase and lowercase?
Yes. Set the match_mode argument to 1 in the relevant function to make its delimiter search case-sensitive. The default search ignores case.

What if my Excel does not have TEXTBEFORE?
Use the MID, SEARCH, and LEN formula shown above, or use Power Query for a larger or repeatable text-cleaning task.

Method: the standard formula for this calculation, rounded as each result states. See how the calculators work or report an error.