Excel MID Function Not Working? Causes, Errors, and Fixes

If Excel’s MID function is not working, check that its arguments are in the right order, the starting position is at least 1, and the character count is not negative. MID returns a piece of text using the pattern =MID(text, start_num, num_chars); it does not count from zero or treat the last argument as an ending position.

How the MID function works

MID is a text function in Excel for Microsoft 365. It extracts a specified number of characters from a string, beginning at a position you choose. The first character is position 1. The third argument is how many characters to return, not the position where extraction should stop.

With the text INV-2026-00481 in A2, this formula returns 2026:

With INV-2026-00481 in A2, enter: =MID(A2,5,4). The result is 2026, because the year starts at character 5 and contains four characters.

That simple distinction explains many apparently incorrect results. If you want characters 5 through 8, use a start position of 5 and a count of 4. Do not enter 8 as the third argument: that requests eight characters starting at position 5.

Text: the cell or string to read.
Start_num: the position of the first character to return, counting from 1.
Num_chars: the number of characters to return.

MID returns text, even when the extracted characters look like a number. The same function syntax works in Excel for Microsoft 365 on Windows, Mac, and the web, although your regional settings may require semicolons instead of commas between arguments.

Check argument order and character positions

The most common mistake is swapping the second and third arguments. For example, if A2 contains AB-12345, a start position of 4 and a count of 5 returns the five digits. The hyphens and letters count as characters too; Excel does not skip punctuation.

With AB-12345 in A2, enter: =MID(A2,4,5). The result is 12345.

If the result is too long, reduce num_chars. If it begins too early or too late, adjust start_num. To check where a character falls, count from the first character in the cell, including spaces and punctuation. You can also use a helper formula to report the total character count.

With the text to inspect in A2, enter: =LEN(A2). If A2 contains AB-12345, the result is 8.

LEN is useful when a string has a fixed format, but it does not reveal the position of a particular delimiter. For text whose parts vary in length, use FIND or SEARCH to locate a marker, then give MID a calculated start and count. FIND is case-sensitive; SEARCH is not.

Understand blank results and error messages

A blank-looking result does not always mean MID failed. If the start position is beyond the end of the source text, MID returns an empty string. If the requested count runs past the end, Excel returns the characters that remain. For example, requesting six characters from a three-character string starting at position 2 returns only the two characters available from that point.

Check the arguments if you see #VALUE!. A start position below 1 or a negative character count causes this error. Arguments that Excel cannot interpret as numbers can also cause problems. A start position of 0 is not a way to get the first character; use 1.

Other errors may originate elsewhere in the formula. If the text argument refers to a deleted cell, the formula can show #REF!. If MID is nested inside FIND, SEARCH, or another function, the nested function can be the source of the error. Evaluate the parts separately: first confirm that the source cell contains the expected text, then check each calculated position and count.

Unexpectedly blank: confirm the source cell and see whether the start position exceeds its length.
#VALUE!: check for a zero or negative start, a negative count, or a nonnumeric argument.
#REF!: inspect the cell references, especially after deleting or moving columns.
#NAME?: check the function spelling and whether the formula uses separators accepted by your Excel settings.

In many regional settings, Excel uses semicolons between arguments instead of commas. If a formula pasted from a website is rejected, replace the separators with the ones your installation expects. This changes formula punctuation, not MID’s logic.

Check what is actually stored in the cell

MID reads the cell’s underlying value or text, not necessarily the way that value looks on the worksheet. This matters with dates, formatted numbers, and leading zeros. A date may display as 2026-07-14 because of its number format while the stored value is a serial number. Applying MID directly to that date may therefore extract digits from the serial value rather than from the displayed date.

To extract the month from a date in A2, convert the date to a controlled text format first. With a valid date in A2, enter: =MID(TEXT(A2,"yyyy-mm-dd"),6,2). For a date displayed as July 14, 2026, the result is the text 07.

Leading zeros can disappear when an identifier is entered as a number. If a cell contains the numeric value 1234, Excel cannot know whether the original entry was 1234, 01234, or 001234. If the intended identifier always has six digits, format it as text for extraction.

With the numeric value 1234 in A2 and a known six-digit format, enter: =MID(TEXT(A2,"000000"),1,2). The result is 00. This only works because the required width is known; it cannot recover zeros when the original width is unknown.

Also look for spaces or invisible characters. A space counts as a character, so a leading space shifts every later position. TRIM removes ordinary extra spaces at the beginning and end, but a nonbreaking space imported from a webpage may need to be replaced first. Cleaning the source data can be more reliable than trying to compensate for hidden characters in every extraction formula.

Extract text when the positions vary

Hard-coded positions are appropriate for consistent codes, such as a fixed-width product ID. They are fragile when the text before or after the desired section changes. In those cases, use a delimiter such as a colon, slash, or hyphen to calculate where MID should begin and end.

Suppose A2 contains Order: 58317 / shipped. The colon and slash mark the boundaries, so the formula can find them instead of assuming the order number always appears at the same position.

With that order text in A2, enter: =TRIM(MID(A2,FIND(":",A2)+1,FIND("/",A2)-FIND(":",A2)-1)). The result is 58317. FIND locates each delimiter, MID returns the text between them, and TRIM removes the surrounding spaces.

This formula assumes that both delimiters exist and appear in the expected order. Otherwise, FIND returns an error. To show a useful message when a delimiter is missing, wrap the extraction in IFERROR.

With the same expected layout and source text in A2, enter: =IFERROR(TRIM(MID(A2,FIND(":",A2)+1,FIND("/",A2)-FIND(":",A2)-1)),"Check delimiters"). It returns 58317 for the example text and displays Check delimiters if the extraction encounters an error.

For newer versions, Excel for Microsoft 365 and Excel 2024 include functions such as TEXTBEFORE and TEXTAFTER that can make delimiter-based extraction easier to read. MID remains useful when you need a precise character count, must support older workbooks, or are working with a fixed-position format.

Fix formulas that change when copied

If a formula works in one row but not the next, check whether the reference moved as intended. A reference such as A2 is relative, so copying the formula down changes it to A3, A4, and so on. That is usually right when each row has its own source text. If every row should read from the same cell, use an absolute reference such as $A$2.

When copying across columns, review the references as well as the start positions. A formula can remain syntactically valid while pointing at a neighboring column or extracting the wrong section. Select the result cell and inspect the formula bar, or use Excel’s formula-editing view to check which cells the references highlight.

Another common surprise is that an extraction displays digits but cannot be used in arithmetic as expected. MID returns text. If A2 contains ID-125, the extracted 125 is text, not a numeric value. Convert it explicitly only when calculations are needed.

With ID-125 in A2, enter: =VALUE(MID(A2,4,3)). The result is the number 125, rather than text. VALUE can fail if the extracted text is not a number in a format Excel recognizes, so keep the extraction as text for codes, postal codes, and identifiers where leading zeros matter.

Choose MID or another text function

MID is the right choice when you know the character position and how many characters to return. It is not automatically the best function for every text-cleaning task. Choosing the function that matches the structure makes formulas easier to maintain.

MID: returns characters from a specified position within the text.
LEFT: returns characters from the beginning of the text.
RIGHT: returns characters from the end of the text.
FIND or SEARCH: locates a delimiter or phrase so another formula can use its position.
TEXTBEFORE or TEXTAFTER: extracts text around a delimiter in Microsoft 365 or Excel 2024.

For instance, if an identifier always has a three-character prefix followed by a variable-length code, MID with a fixed count may not be enough. Locating a separator with FIND, or using TEXTAFTER in a supported version, better reflects the data’s structure. Conversely, if a code’s format is strictly fixed, a straightforward MID formula is often clearer than a longer delimiter-based formula.

Frequently Asked Questions

Why does MID return blank in Excel?
If the starting position is greater than the number of characters in the source text, MID returns an empty string. Check the referenced cell, count its characters, and verify that the start position is not calculated from an unexpected blank or missing delimiter.

Does MID start counting at zero or one?
It starts at 1. The first character is position 1, so use =MID(A2,1,1) to return the first character when A2 contains the source text. A start position of 0 produces an error rather than the first character.

Why does MID return the wrong number of characters?
The third argument is a character count, not an ending position. To return characters 4 through 8, set the start to 4 and the count to 5. Include spaces, punctuation, and other characters when calculating those positions.

Can MID extract part of a date or number?
Yes, but the result depends on the value Excel reads, which may differ from the displayed format. Convert a date or number to a deliberate text representation with TEXT before using MID when you need predictable characters. Remember that MID’s result is text.

Why does Excel reject my MID formula with commas?
Your regional settings may use semicolons as argument separators. Try replacing commas with semicolons, while keeping the function name and argument order unchanged. The MID function works the same way in Excel on Windows, Mac, and the web.

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

More in Errors & fixes

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

Spotted an error in this guide? Let us know.