Excel MID and FIND Functions: How to Extract Text in Excel

With the text INV-2026-047 in A2, use =MID(A2,FIND("-",A2)+1,4) to extract the year; the result is 2026. MID returns characters from a specified position, while FIND locates the position of text, so combining them lets you extract content relative to a delimiter such as a hyphen, colon, or parenthesis.

What MID and FIND do

MID and FIND solve different parts of a text-extraction task. MID takes a text value, a starting position, and a character count, then returns that portion of the text. FIND searches for one piece of text inside another and returns the position where the match begins. A position is a number: the first character is position 1, the second is position 2, and so on.

On their own, the functions are useful in different situations. MID works well when the relevant text always occupies the same positions, such as a four-character year in a consistently formatted code. FIND is useful when a marker can move but remains identifiable, such as the colon after a variable-length label. Together, they can locate that marker first and use its position to tell MID where to begin.

Both functions return text, even when the extracted characters look like a number. For example, a year extracted from an ID may appear as 2026 but remain text. That is usually fine for displaying an identifier; if you need to calculate with the result, convert it to a number with an appropriate method such as VALUE.

How their positions and arguments work

MID has three arguments: the text to inspect, the starting character number, and the number of characters to return. Its starting position is counted from the beginning of the text, not from a delimiter unless you calculate that position yourself. Requesting zero characters returns an empty result. A start position below 1 or a negative character count produces an error.

FIND takes the text to locate, the text in which to search, and an optional starting position. If you omit the last argument, it begins at the first character. If you provide a start position, the result is still the position in the complete text, not a count restarted from that position. FIND is case-sensitive: searching for lowercase text does not match the same letters in uppercase. It also looks for literal text rather than treating a question mark or asterisk as a wildcard.

Spaces and punctuation count as characters. In a string such as AB-123, the hyphen is character 3 and the first digit is character 4. This simple detail matters when you set a fixed starting position or add 1 to the position returned by FIND.

Use MID for text at a fixed position

When every entry follows the same format, a fixed MID position may be all you need. Suppose A2 contains INV-2026-047. The year begins at character 5, and it is four characters long. With that value in A2, enter =MID(A2,5,4); the result is 2026.

This approach is straightforward, but it depends on the format staying consistent. If some IDs have a shorter prefix, an extra space, or a different delimiter position, character 5 might no longer be the beginning of the year. Before filling a fixed-position formula down a column, check several records, including the shortest and longest examples.

Fixed-position extraction is appropriate for structured codes, consistently formatted dates stored as text, and product numbers with defined character slots. It is less reliable for descriptions or labels assembled from variable-length parts. For those, finding a delimiter is generally safer.

Combine FIND and MID to extract after a delimiter

For the same value, INV-2026-047, FIND can locate the hyphen rather than assuming its position. With that value in A2, enter =FIND("-",A2); the result is 4, because the first hyphen is the fourth character. Adding 1 gives the position immediately after it.

To return the four characters after that first hyphen, with INV-2026-047 in A2, use =MID(A2,FIND("-",A2)+1,4). The result is 2026. Read the formula from the inside out: FIND returns 4, adding 1 gives a starting position of 5, and MID returns four characters from that position.

This pattern is helpful whenever the text before a target value varies but a separator marks the boundary. For instance, if A2 contains Department: Finance, FIND can identify the colon. The number of spaces after the colon is part of the data layout, though, so the formula must account for it. If the text always has one space after the colon, start two characters after the colon to skip both the colon and the space.

To return everything after the colon and its following space, with Department: Finance in A2, use =MID(A2,FIND(":",A2)+2,LEN(A2)). The result is Finance. LEN supplies a character count long enough to reach the end of the cell; MID simply returns the remaining characters. If spacing is inconsistent, this fixed two-character offset may need adjustment or a more deliberate cleanup step.

Extract text between two delimiters

A common task is returning the characters between an opening and closing marker. For example, with Product (Maya) - blue in A2, the opening parenthesis identifies where the wanted text begins, and the closing parenthesis identifies where it ends. MID needs both a starting position and a length. The length is the closing-parenthesis position minus the opening-parenthesis position minus 1, because the characters inside do not include either parenthesis.

With Product (Maya) - blue in A2, enter =MID(A2,FIND("(",A2)+1,FIND(")",A2)-FIND("(",A2)-1). The result is Maya. FIND locates the two parentheses, the first FIND plus 1 points to M, and the calculated length counts the four letters up to but not including the closing parenthesis.

This formula assumes that both delimiters exist and that the closing marker comes after the opening one. If some rows do not follow that pattern, the formula can return an error. When you have a stable delimiter and Microsoft 365 or Excel 2024, TEXTBEFORE and TEXTAFTER may offer a more readable alternative. For more complicated or irregular text, first consider whether a consistent source format or a dedicated import-cleaning step would be more dependable.

Common errors and practical fixes

The most common error is #VALUE!. FIND returns it when the text being searched for is absent, and it can also result from an invalid starting position. MID can return it for an invalid start position or a negative character count. Check for small differences in the source, such as an en dash instead of a hyphen, an unexpected space, or a missing parenthesis. Two marks that look similar on screen may be different characters.

If a delimiter might be missing, you can choose to show a blank rather than an error. With values in A2 and a pair of parentheses expected around the text, enter =IFERROR(MID(A2,FIND("(",A2)+1,FIND(")",A2)-FIND("(",A2)-1),""). For Product (Maya) - blue, the result is Maya; if the formula encounters an error, it returns a blank cell result. Use this carefully: IFERROR also hides errors caused by problems other than a missing delimiter, which can make a faulty formula harder to notice.

Another frequent issue is including or removing the wrong spaces. FIND returns the position of the delimiter itself, so adding 1 starts at the next character. Adding 2 skips the delimiter plus one more character, commonly a space. That is correct only if the source consistently contains that space. Test the formula on examples with and without spaces before applying it to a large range.

If an extracted value looks right but will not behave like a number, remember that MID returns text. A number stored as text may sort differently or fail in some calculations. Convert it only when numeric behavior is required; preserve it as text when leading zeros are meaningful, as they often are in account numbers, postal codes, and product IDs.

FIND versus SEARCH, and when to use alternatives

SEARCH is the closest counterpart to FIND. Both return a position, but SEARCH is not case-sensitive and supports wildcard characters. Choose FIND when capitalization should matter or when you want to search for literal characters. Choose SEARCH when capitalization is irrelevant or a wildcard pattern is useful. They are not interchangeable in every formula, so consider the exact data you expect.

LEFT and RIGHT can be simpler when you need characters only from the beginning or end of text. For example, a fixed prefix is often easier to return with LEFT than with MID. MID is the better fit when the desired section begins somewhere within the text. When a delimiter is involved, MID plus FIND provides flexible positioning, while newer text functions may make some boundary-based tasks easier to read.

TEXTBEFORE and TEXTAFTER are available in Microsoft 365 and Excel 2024, but not in every older Excel release. They can express tasks such as returning text on one side of a delimiter more directly. MID and FIND remain useful because they are widely supported and can handle cases where you need exact character counts or more control over the starting and ending positions.

In Excel for Microsoft 365 on Windows, these formulas use commas between arguments in the examples above. The functions are also available in current Excel for Mac and Excel for the web, although regional settings can affect formula separators. If Excel rejects a formula that appears correctly formed, check the list separator used by your Excel installation and whether your source text contains the expected delimiter.

Frequently Asked Questions

Is FIND case-sensitive in Excel?
Yes. FIND distinguishes uppercase and lowercase letters. If you need a case-insensitive search, use SEARCH instead.

Why does FIND return #VALUE!?
Most often, the exact text you asked Excel to find is not present in the searched cell. Check punctuation, spaces, capitalization, and the start position. FIND is case-sensitive and does not treat asterisks or question marks as wildcards.

Does MID count spaces and punctuation?
Yes. MID counts each character in the text, including spaces, hyphens, colons, and parentheses. Those characters affect both the starting position and the number of characters to return.

Can MID and FIND extract text between two characters?
Yes. Use FIND to locate the opening and closing characters, then use those positions to set MID’s starting position and character count. The formula must account for excluding the delimiters, and it will need error handling if either character may be missing.

Why is a MID result not recognized as a number?
MID returns text. If the extracted characters represent a number and you need to perform arithmetic, convert the result to a numeric value. Keep it as text instead when leading zeros or identifier formatting must be preserved.

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.