How to extract text between two characters in Excel

The formula, piece by piece

MID(text, start_num, num_chars) returns num_chars characters from position start_num. Between two delimiters you need two numbers:

• Start = position of the opening character + 1. In Order 10482 (Express) to Lisbon the ( is character 13, so the text starts at 14.
• Length = position of the closing character − position of the opening one − 1. The ) is character 21, so the length is 21 − 13 − 1 = 7.

=MID(A2,14,7) is Express. Because both positions come from FIND, the formula works for Same day (8 characters) or any other length without changes. The step-by-step block at the bottom of the example shows each number in its own cell, which is the quickest way to debug a formula that returns the wrong slice.

Worked example: nine extraction patterns and one formula taken apart

Column A is the raw text, column B the formula result, column C the pattern. Rows 13 to 17 split the formula of B2 into its steps. The orders and addresses are fictional.

Worked example: nine extraction patterns and one formula taken apart

	A	B	C
1	Text	Result	Pattern
2	Order 10482 (Express) to Lisbon	Express	Between ( and )
3	Order 10483 (Same day) to Porto	Same day	Same formula, longer text
4	Order 10484 to Braga	none	No brackets: IFERROR
5	EU-PT-LIS-0042	PT	1st and 2nd hyphen
6	EU-PT-LIS-0042	LIS	2nd and 3rd hyphen
7	EU-PT-LIS-0042	0042	After the last hyphen
8	maria.silva@northwind.example	northwind	Between @ and the next dot
9	Promo <b>Summer25</b> applied	Summer25	Delimiters of 3 and 4 characters
10	Ship to: Lisbon , PT	Lisbon	Between : and , with TRIM
11
12	Step by step: the formula in B2
13	Position of "("	13	FIND
14	Position of ")"	21	FIND
15	Start = position of "(" + 1	14	skip the (
16	Length = ")" − "(" − 1	7	drop the )
17	Result	Express	MID(text, start, length)

MID, LEFT and RIGHT return text, so 0042 keeps its zeros. Wrap the result in VALUE if you need a number.

When the delimiter appears twice: the start_num of FIND

Codes like EU-PT-LIS-0042 use the same character on both sides. FIND(find_text, within_text, [start_num]) starts searching at start_num, so the second hyphen is FIND("-",A5,FIND("-",A5)+1): search again, starting one character after the first hyphen. The text between the first and the second hyphen is then

=MID(A5,FIND("-",A5)+1,FIND("-",A5,FIND("-",A5)+1)-FIND("-",A5)-1) → PT

The same idea extracts an email domain: FIND(".",A8,FIND("@",A8)) finds the first dot after the @, so the dot in maria.silva is skipped and the result is northwind.

Between the nth and the next occurrence with SUBSTITUTE

Nesting FIND three or four times gets unreadable. A cleaner trick: SUBSTITUTE(text, old, new, instance_num) replaces only the nth occurrence. Replace the 2nd hyphen with a character that never appears, such as #, and FIND that: FIND("#",SUBSTITUTE(A6,"-","#",2)) is the position of the 2nd hyphen. Do the same with 3 for the 3rd hyphen and put both into MID:

=MID(A6,FIND("#",SUBSTITUTE(A6,"-","#",2))+1,FIND("#",SUBSTITUTE(A6,"-","#",3))-FIND("#",SUBSTITUTE(A6,"-","#",2))-1) → LIS

For the text after the last hyphen, count the hyphens first with LEN(A7)-LEN(SUBSTITUTE(A7,"-","")) (3 here), use that count as the instance number and take everything to the right of it. The result is 0042, with its leading zeros, because MID, RIGHT and LEFT always return text.

Delimiters longer than one character

If the opening delimiter is a word or a tag, add its length instead of 1. In Promo <b>Summer25</b> applied the opening tag has three characters:

=MID(A9,FIND("<b>",A9)+3,FIND("</b>",A9)-FIND("<b>",A9)-3) → Summer25

With the delimiter in a cell, say D1, write +LEN(D1) and -LEN(D1) so the formula adapts when the delimiter changes.

Missing delimiters, extra spaces and case

If a delimiter is missing, FIND returns #VALUE! and so does the whole formula. Wrap it in IFERROR(…,"none"), as in row 4, or test first with ISNUMBER(FIND("(",A4)). Text between delimiters often carries spaces from manual typing: Ship to: Lisbon , PT gives " Lisbon " until you wrap the result in TRIM. FIND is case-sensitive and SEARCH is not, which only matters when the delimiter is a letter or a word: SEARCH("ref:",A2) also finds "Ref:" and "REF:".

TEXTBEFORE and TEXTAFTER in Excel 365

Excel 365 and Excel 2024 have two functions that make most of this shorter. =TEXTBEFORE(TEXTAFTER(A2,"("),")") returns Express: take everything after the (, then everything before the ). Both accept an instance number, so =TEXTBEFORE(TEXTAFTER(A6,"-",2),"-") returns LIS, and a negative instance counts from the end: =TEXTAFTER(A7,"-",-1) returns 0042. They also have an if_not_found argument instead of IFERROR. Use them if everyone who opens the file has a current Excel version. MID and FIND work in every version, in Google Sheets and in LibreOffice, and they are what you need to understand when you inherit an older workbook. For splitting a whole column into several parts at once, TEXTSPLIT or Data > Text to Columns is faster than a formula per part.

Where this shows up in real work

Extracting text between delimiters is the core of most data cleaning: a colour and size out of product titles, a region out of a SKU, a domain out of an email, an order number out of a bank reference. Once the parts are in their own columns you can count and sum by them, for example with SUMIFS, or use them as keys in a lookup. If the cleaned code has to match another table exactly, wrap the extraction in TRIM so a stray space does not break the match.

Common mistakes

• Forgetting the +1 and −1. Without them the result includes a delimiter: "(Express" or "Express)". Start one character after the opening delimiter and take one character less.
• Adding 1 for a longer delimiter. For a tag like <b> or a word like "Ref: ", add and subtract its full length, or use LEN() of the delimiter cell.
• Finding the wrong occurrence. FIND(".",A8) finds the dot in maria.silva, not the one after the @. Give FIND a start position or use the SUBSTITUTE trick for the nth occurrence.
• No plan for rows without the delimiter. One missing bracket turns the cell into #VALUE! and breaks every SUM that uses the column. Wrap the formula in IFERROR with a clear label.
• Leaving spaces in the result. " Lisbon " does not match "Lisbon" in a lookup or a COUNTIF. Wrap the extraction in TRIM.
• Expecting a number. MID always returns text, so =MID(…)+0, VALUE or -- is needed before you sum extracted amounts.