Extract Text Between Two Characters in Excel

To extract text between two characters in Excel, such as a code inside brackets or a year between two dashes, you find where each character sits and take the text in between. In Microsoft 365, TEXTBEFORE and TEXTAFTER do this in one short formula. In every other version, MID and FIND do the same job. This guide explains both, step by step, with the edge cases that trip people up.

All the examples use a product list like this:

The quick way: TEXTBEFORE and TEXTAFTER#

TEXTAFTER returns everything after a character, and TEXTBEFORE returns everything before one. Put one inside the other and you get the text between two characters:

=TEXTBEFORE(TEXTAFTER(A2,"("),")")

Read it from the inside out:

1. TEXTAFTER(A2,"(") returns SKU-4471), everything after the opening bracket.
2. TEXTBEFORE(...,")") takes that and returns SKU-4471, everything before the closing bracket.

This is the easiest formula to read and fix. It needs Microsoft 365, Excel 2024 or Excel for the web. In older versions it shows #NAME?, so use MID and FIND instead. The TEXTBEFORE and TEXTAFTER lesson covers both functions in detail.

The classic way: MID and FIND#

MID takes a piece of text from the middle of a cell, and FIND tells it where to start and how much to take. This works in every version of Excel:

=MID(A2,FIND("(",A2)+1,FIND(")",A2)-FIND("(",A2)-1)

MID needs three things: the text, the start position, and the number of characters. Here is how the formula works them out for "Laptop Stand (SKU-4471)":

The -1 in the length is there because you want the characters strictly between the two brackets, not including either one. Fill the formula down and every row works out its own positions, even though the product names have different lengths. See the MID function guide and the FIND function guide for each function's full syntax.

Extract text between two of the same character#

When both characters are the same, such as the dashes in "INV-2024-0042", the second FIND must start searching after the first dash. Otherwise it finds the first dash again.

With TEXTBEFORE and TEXTAFTER, this just works:

=TEXTBEFORE(TEXTAFTER(A2,"-"),"-")

TEXTAFTER returns "2024-0042", and TEXTBEFORE returns 2024.

With MID and FIND, use FIND's optional third argument, the start position:

=MID(A2,FIND("-",A2)+1,FIND("-",A2,FIND("-",A2)+1)-FIND("-",A2)-1)

The first dash is at position 4. FIND("-",A2,5) starts looking at position 5 and finds the second dash at 9. MID then takes 9 - 4 - 1 = 4 characters from position 5: 2024.

For text after the second or third copy of a character, TEXTAFTER has an instance number: =TEXTAFTER(A2,"-",2) returns 0042.

Handle rows that don't have the characters#

If a cell is missing one of the characters, FIND returns #VALUE! and TEXTAFTER returns #N/A. In a real list, a few rows usually break the pattern. Wrap the formula to show a blank instead:

=IFERROR(MID(A2,FIND("(",A2)+1,FIND(")",A2)-FIND("(",A2)-1),"")

TEXTAFTER and TEXTBEFORE also have their own sixth argument, if_not_found, but IFERROR around the whole formula is simpler and covers both functions. To flag the rows instead of hiding them, test first with IF and ISNUMBER, as shown in IF with text conditions.

FIND vs SEARCH#

FIND is case-sensitive, and SEARCH is not. For symbols such as brackets, dashes and @ signs, it makes no difference. When the boundary is a word, such as the text between "Ref:" and "Date:", SEARCH is safer because it matches "ref:" and "REF:" too. SEARCH also accepts wildcards. The guide to FIND and SEARCH in Excel compares them in detail.

Extract text between two words#

The same method works when the boundaries are words rather than single characters. Say A2 holds "Invoice Ref: 7781, due 30 June". To pull out the reference number, use the label and the comma as the two boundaries:

=TEXTBEFORE(TEXTAFTER(A2,"Ref: "),",")

The result is 7781. Include the space after the colon in the first boundary, or the result starts with a space. In older Excel, use SEARCH to find "Ref: " and add its length, 5, to get the start position for MID.

Turn the result into a number#

Every text function returns text, even when the result looks like a number. If you extract "2024" or "4471" and want to add it up or compare it, wrap the formula in VALUE:

=VALUE(TEXTBEFORE(TEXTAFTER(A2,"-"),"-"))

This returns the number 2024. Text numbers sit on the left of the cell by default; real numbers sit on the right, which is a quick way to tell them apart.

When to use Text to Columns instead#

If you need to split every part of the text, not just one piece in the middle, Text to Columns or TEXTSPLIT may be faster. They cut the text at every delimiter in one go. The guide to Text to Columns shows the menu method, and Text to Columns vs TEXTSPLIT compares the two. Formulas are better when the source data keeps changing, because they update automatically. For the common case of email addresses, see how to extract the domain from an email.

Practise it#

The MID and FIND formula looks long, but once you've built it a couple of times it becomes routine. Practise TEXTBEFORE and TEXTAFTER in a live spreadsheet: you type the formula and it checks the result instantly. Then practise FIND and SEARCH to get confident with positions, the part most people find tricky.