Using MID and FIND in Excel to Extract Text

Formulas & functions · Updated November 20, 2026

Yes. In Excel, combine FIND with MID to locate a delimiter in a text value and return characters beside it. With a code such as ORD-5831-Q in A2, this formula returns the text between the first and second hyphens: =MID(A2,FIND("-",A2)+1,FIND("-",A2,FIND("-",A2)+1)-FIND("-",A2)-1). The key idea is that FIND supplies character positions, while MID uses a starting position and a character count to extract the portion you want.

What MID and FIND do

MID and FIND solve different parts of a text-extraction problem. MID returns a specified number of characters from inside a text string. Its syntax is MID(text, start_num, num_chars): text is the cell or text to inspect, start_num is the position of the first character to return, and num_chars is how many characters to return. Character positions start at 1, not 0.

FIND locates one text string inside another and returns the position where the match begins. Its syntax is FIND(find_text, within_text, [start_num]). The first argument is what to look for, the second is the text to search, and the optional third argument tells Excel where to begin searching. For example, a hyphen in the fourth character position has a FIND result of 4.

Used together, FIND can calculate where a variable-length field begins or ends, and MID can extract that field. This is useful when codes, file names, product identifiers, or imported text use consistent separators but the pieces have different lengths. It is less suitable when the text has no predictable structure, or when the part you want is always at a fixed position and a simpler function will do.

Understanding the positions and arguments

The most important detail is that MID needs both a starting position and a number of characters. FIND returns a position, not a character count. If a separator sits at position 4, the first character after it is at position 5, so the start argument is the FIND result plus 1. To extract up to another separator, subtract the first separator position from the second one, then subtract 1 to exclude the separator characters.

For a value like ORD-5831-Q, the first hyphen is at position 4 and the second is at position 9. The digits begin at position 5, and there are four digits before the next hyphen. MID therefore starts at 5 and returns 4 characters. This calculation is why the formula can handle a field whose length changes from row to row, unlike a hard-coded formula such as MID(A2,5,4).

FIND searches from left to right and returns the first match at or after its start_num. If you omit start_num, Excel starts at position 1. To locate a later occurrence of a repeated separator, provide a start position after the earlier match. Nesting one FIND inside another is a common way to calculate that position.

Extracting text between two delimiters

Suppose A2 contains ORD-5831-Q and you want the middle component, regardless of how many characters it contains. The formula below finds the first hyphen, begins just after it, then finds the next hyphen and calculates the number of characters between the two. With that value in A2, the result is 5831.

With the complete code in A2, use:

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

The inner FIND locates the first hyphen. The second FIND starts its search one character after that first hyphen, so it returns the next one rather than finding the same character again. The length calculation subtracts the first hyphen’s position from the second hyphen’s position and removes one separator character. If your data uses slashes, colons, or another delimiter, replace the hyphen in each FIND argument with the character used in the cell.

This pattern assumes each value has at least two delimiters in the expected order. If a row is missing one, FIND cannot locate it and the formula returns an error. Check the consistency of the source data before copying a delimiter-based formula down a large range.

Extracting text after a delimiter

To return everything after the first hyphen, you do not need to calculate the position of a second delimiter. With ORD-5831-Q in A2, the formula starts one character after the first hyphen and asks for enough characters to cover the remainder of the cell. The result is 5831-Q.

With the full code in A2, use:

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

LEN returns the total number of characters in the cell. That total is at least as large as the remaining portion, so MID simply returns characters through the end of the text. If instead you need only the final component after the second hyphen, start after the second occurrence.

With ORD-5831-Q in A2, this formula returns Q:

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

Here, the inner FIND locates the first hyphen, and the outer FIND begins after it to locate the second. Adding 1 moves the start point to the character after that second hyphen. This is a useful pattern for extracting a final field when the number of characters in the earlier fields varies.

Using MID for fixed-position text

FIND is not necessary when the part you need always occupies the same positions. For instance, if A2 contains AB2026XY and the four-digit year is always characters 3 through 6, MID alone is simpler. With AB2026XY in A2, =MID(A2,3,4) returns 2026.

Fixed-position extraction is dependable only when the source format really is fixed. If a prefix gains a character or some records contain shorter codes, the formula may return the wrong portion without showing an error. When a field is separated by a clear delimiter and its length can change, the FIND-and-MID pattern is generally more adaptable.

Before filling a formula down, test it against short and long examples, records with repeated delimiters, and any unusual values that occur in the source. A formula that works on one sample may not reflect the full range of real data.

Entering and filling the formula

In Microsoft Excel for Microsoft 365 on Windows, enter the formula in the result cell and press Enter. If the source value is in A2, put the formula in another cell on row 2, then use the fill handle at the lower-right corner of that result cell to copy it down. Excel adjusts the relative reference from A2 to A3, A4, and so on.

A practical workflow is:

  • Confirm which character separates the fields and whether it appears consistently.
  • Identify whether you want the first field, the last field, or the text between two occurrences.
  • Work out which FIND result marks the beginning or ending position.
  • Test the formula on several representative rows before filling it through the data.
  • Review errors and unusual results instead of assuming every row follows the same pattern.

On Excel for Mac, the functions and formula syntax are the same, although ribbon placement and keyboard shortcuts can differ. Excel for the web also supports MID and FIND. If your Excel regional settings use semicolons as argument separators, formulas copied from a comma-separator example may need semicolons instead.

Common errors and how to handle them

The most common error is #VALUE!, which FIND returns if it cannot find the specified text. A formula that searches for a hyphen will fail on a row that has no hyphen, and a formula looking for a second hyphen will fail if the value contains only one. Inspect the input and decide whether missing delimiters should be corrected, flagged, or handled with a fallback result.

If a missing delimiter is an expected possibility, IFERROR can display a chosen fallback instead of the error. With source text in A2 and a request to return everything after its first hyphen, this version displays Not found when no hyphen is present:

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

IFERROR can make a report easier to read, but it can also conceal unexpected data problems. Use a clear fallback and consider checking the source values separately so a malformed record does not silently look like a normal result. Also note that extra spaces in the source remain part of the extracted text; MID does not automatically trim or clean characters.

Another common issue is searching for the wrong occurrence. FIND without a start_num returns the first matching delimiter. When a separator appears several times, explicitly start the next search after the previous match, as in the examples above. Finally, remember that FIND is case-sensitive: searching for cat does not match Cat. For case-insensitive position searches, Excel provides SEARCH, which otherwise has a similar role.

Alternatives and when to choose them

MID with FIND is useful when you need a formula compatible with a broad range of Excel versions or when you want to understand and control each character position. It also works well for data with a known delimiter but variable-length fields. Its drawback is that formulas can become nested and difficult to maintain when there are many fields or complicated text patterns.

In Microsoft 365 and Excel 2024, TEXTBEFORE and TEXTAFTER can express some delimiter-based tasks more directly. For example, they can return text before or after a specified delimiter without manually calculating the number of characters. These functions are not available in every older Excel version, so use MID and FIND when compatibility with older installations matters. Check the version used by the people who will open the workbook before choosing a newer function.

For one-time cleanup, Excel’s Text to Columns feature may be more convenient than formulas: it can split a selected column using a delimiter. Unlike a formula, that operation creates separate values rather than a result that updates automatically when the source changes. Choose a formula for repeatable, refreshable extraction; choose a one-time split when you want to transform the data and do not need the extraction logic to recalculate.

Frequently Asked Questions

Can MID and FIND extract text after the first space?
Yes. Use a space as the find_text in FIND, then start MID one character after the returned position. If the source may have multiple spaces or inconsistent spacing, inspect and clean the data first, because the first space may not separate the field you intend.

Is FIND case-sensitive in Excel?
Yes. FIND distinguishes uppercase and lowercase letters. Use SEARCH when you need a case-insensitive search. Both return a character position when they find a match, but SEARCH also supports wildcard characters while FIND treats the search text literally.

Why does my MID and FIND formula return #VALUE!?
Most often, FIND cannot locate the delimiter or occurrence specified in the formula. Check that the character is present, that it is the expected character rather than a visually similar one, and that the formula starts its later search at the right position.

Does FIND count the first character as zero?
No. Excel text positions begin at 1. If a delimiter is the first character in a cell, FIND returns 1; the character immediately after it is at position 2.

Can I use MID and FIND to extract a number?
The functions return text, even when the extracted characters look like digits. Excel may interpret a text result as a number in some contexts, but if you need numeric calculations, convert the result deliberately and confirm that the extracted characters are a valid number. Keeping identifiers such as product codes as text is often preferable because leading zeros matter.

Related

Try it: the Formula Explainer: paste any formula and see each function and reference in plain English.

More in Formulas & functions

Sources: Function behaviour and menu paths: Microsoft Support documentation for Excel. Method & sources

Spotted an error in this guide? Let us know.