How to extract text between two characters in Excel

Method 1: TEXTBEFORE inside TEXTAFTER (Excel 365 and 2024)

The sheet is a contact export where each cell holds a name, a department in parentheses and an email in angle brackets. Read the formula from the inside out. TEXTAFTER(A2,"(") returns "Finance) <[email protected]>". TEXTBEFORE then keeps everything before the first ")" in that shorter text.

Because TEXTBEFORE only looks at what is left after the opening character, a ")" earlier in the cell can never confuse it. The same pattern works for any pair: swap in "[" and "]", "{" and "}", or two different characters.

Two different characters: an email between < and >

The delimiters do not have to match. To pull the address out of "Name <email>", cut after "<" and before ">". This is the usual way to clean the "From" column of an email export.

Between two words, ignoring case

Delimiters can be words. Column B holds shipment notes, and the origin city sits between "from " and " to ". Put the spaces inside the delimiters so the result has no spaces at either end. Leave them out and you get " Leeds" with a leading space, which breaks lookups.

TEXTBEFORE and TEXTAFTER are case-sensitive by default, so on row 3 (FROM Porto TO Lyon) the basic version returns #N/A. Their fourth argument, match_mode, set to 1 makes the match ignore case.

Keep the spaces in " to " for another reason too: "Porto" contains "to". With "to" alone, row 3 returns "Por".

Method 2: MID and FIND (every version)

MID(text, start, length) needs a start position and a length. The start is one character after the "(", and the length is the distance from there to the ")".

Give the second FIND a start position, FIND(")",A2,FIND("(",A2)), so it looks for the closing character only after the opening one. Without it, a ")" earlier in the cell breaks the formula. Row 8 holds "1) Dana Kim (Finance)": the short version finds the ")" after the 1, gets a negative length and returns #VALUE!. With the start argument it returns Finance.

FIND is case-sensitive. For word delimiters in older Excel, use SEARCH instead, which ignores case.

When a cell has no brackets

Priya Shah on row 4 has no department. TEXTAFTER returns #N/A when it cannot find the delimiter, and the MID and FIND version returns #VALUE!. Wrap the formula in IFERROR and choose what to show: "" for blank, or a label you can filter on.

TEXTAFTER and TEXTBEFORE also have their own if_not_found argument (the sixth), but in a nested formula you would need it on both functions. One IFERROR around the outside is simpler. Keep in mind that IFERROR hides every error, so test the formula on a few normal rows first.

The second pair, nested brackets, or keeping the brackets

Second pair: Tom's cell has two groups, "(Ops) (contractor)". The third argument of TEXTAFTER is instance_num, so TEXTAFTER(A5,"(",2) starts after the second "(", and the formula returns contractor. In older Excel, find the second "(" with the SUBSTITUTE trick in the find nth occurrence guide.

Nested brackets: Ana's department is "(R&D (EU))". Stopping at the first ")" gives "R&D (EU". An instance_num of -1 searches from the end, so TEXTBEFORE(TEXTAFTER(A6,"("),")",-1) returns R&D (EU). Only use -1 when the cell has one outer group, because on Tom's row it returns "Ops) (contractor".

Keep the brackets: start MID at the "(" itself and add 1 to the length instead of subtracting it. The formula below returns (Finance).

No formulas: Flash Fill

For a one-off clean-up, Flash Fill copies a pattern from an example. It does not update when the source changes, and on mixed data like rows 4 to 6 check every result.

1. In the column next to the data, type the result for the first row, for example Finance.
2. Click the cell below and press Ctrl+E, or choose Data > Flash Fill.
3. Scan the results, especially rows with no brackets or with two pairs.