Excel MID Formula Not Working? Fix the Common Causes

If Excel’s MID formula is not working, first check that its arguments are in the right order: =MID(text,start_num,num_chars). MID starts counting at position 1, and a start position below 1 or a negative character count returns #VALUE!; an incorrect position or unexpected cell content can also make the result look wrong.

How the MID formula works

MID extracts a chosen number of characters from the middle of a text value. Its first argument is the text or cell to read, its second is the position where extraction begins, and its third is how many characters to return. The starting position is counted from the left and begins at 1, not 0. MID counts spaces and punctuation as characters too.

For example, assume A2 contains the text AB-12345. The first character is A, the second is B, and the hyphen is the third, so the digits begin at position 4. With that value in A2, use =MID(A2,4,3) to return 123. If you begin at position 5 instead, the result is 23, because Excel follows the position you supplied exactly.

The function returns text, even when the extracted characters look like a number. That distinction matters if you later need to add, sort, or compare the result as a numeric value. MID is useful for fixed-width codes and consistent text patterns, but it does not identify a meaningful section by itself: you must give it the correct starting position and length.

Check the starting position and character count

Off-by-one errors are among the most common reasons a result is shifted or incomplete. Write out the characters in a sample value and number them from 1 before adjusting the formula. Remember to count delimiters such as hyphens, slashes, and spaces. Also check whether the desired section has a fixed length; if it does not, a hard-coded character count may work on one row and fail on another.

Assume A2 contains INV-4827. The hyphen is character 4 and the first digit is character 5. To return the three digits 482, use =MID(A2,5,3). To return everything after the hyphen, the length can be based on the number of characters in the cell: with the same value in A2, use =MID(A2,5,LEN(A2)-4), which returns 4827.

Check the length argument as carefully as the starting position. A count that is too small truncates the answer; one that is larger than the remaining text simply returns the remaining characters. A count of zero returns an empty string. A starting position beyond the end of the text also returns an empty string, not an error. Those results can make a valid formula seem broken if the position or count is calculated from another cell.

Common errors and what they mean

#VALUE!: The start position is less than 1, or the number of characters is negative. Review any formulas or cell references that calculate those arguments. For instance, subtracting a prefix length from a short value can accidentally produce a negative count.
An empty result: The starting position may be past the end of the text, or the requested character count may be zero. The source cell may also be blank, or a preceding calculation may be returning an empty string.
The wrong characters: Recount from the left, including spaces and punctuation. Confirm that the cell contains the pattern you expect on every row; a formula designed for a fixed-width code cannot reliably handle values with different prefixes or separators.
#NAME? or a formula entry problem: Check that the function is spelled MID, that the parentheses and commas are present, and that text arguments use straight quotation marks. Excel installations configured for a different regional list separator may require semicolons instead of commas when entering formulas.
A number or date that looks unexpected: MID may be reading the stored value rather than the display you see in the cell. Number formatting changes how a value appears; it does not necessarily change the underlying text being extracted.

Excel formulas begin with an equals sign. If you see the formula itself in the cell rather than its result, the cell may be formatted as Text, or the formula may have been entered with an apostrophe before the equals sign. Change the cell’s format to General, then edit and re-enter the formula. Simply changing the format after entry may not make Excel recalculate text that it has already stored as a literal string.

A practical way to debug a MID formula

Work from the source value outward rather than changing several parts of the formula at once. Select a problem cell and inspect the formula bar to see the actual formula and the source cell’s contents. If your formula calculates the start or length, temporarily inspect those calculations too. That reveals whether the problem is the extraction itself or an argument that is wrong for a particular row.

Choose one row that gives the wrong result and copy its source value into your notes, preserving visible spaces and punctuation.
Count the characters from the left and identify the exact first and last characters you want to return.
Check that the start position is at least 1 and the character count is zero or greater.
Test a simple MID formula with fixed numbers on that row. If it works, focus on the formulas or references that calculate the arguments.
Check other rows for different lengths, missing delimiters, blank cells, or extra spaces before filling the formula down.

In Microsoft 365 for Windows, Formulas > Evaluate Formula can step through a formula’s calculation and help identify the argument producing an unexpected result. On Mac or Excel for the web, the formula bar and a separate helper cell can be useful alternatives if that command is not available in the same place. For example, with a source value in A2, put the start calculation in one helper cell and the character count in another, then compare those results with the positions you counted manually.

Spaces, hidden characters, dates, and leading zeros

A value can look clean on screen while containing characters that affect extraction. Ordinary spaces count as characters, including spaces at the beginning or end. TRIM can remove extra ordinary spaces, but pasted data may contain a nonbreaking space or other control characters that TRIM does not remove. CLEAN removes certain nonprinting characters; it does not remove every unusual Unicode character.

Suppose A2 contains a code with an unwanted nonbreaking space. To replace that character with an ordinary space before extraction, use =SUBSTITUTE(A2,CHAR(160)," "). This formula assumes the text to clean is in A2 and returns a cleaned version; it does not itself select a substring. If the data may contain multiple kinds of unwanted characters, inspect a few source cells before choosing a cleanup method rather than assuming every blank-looking gap is the same character.

Dates are another source of confusion. A date cell may display as 14-Mar-2025, while its underlying value is a serial number. MID does not automatically extract the characters from the visible date format. If A2 contains a date and you want the day from a specifically formatted date string, use =MID(TEXT(A2,"mm/dd/yyyy"),4,2). This assumes A2 is a valid date; for March 14, 2025, the TEXT result is 03/14/2025, and MID returns 14. Choose a format string that represents the date consistently for your data.

Leading zeros can also disappear if an identifier was imported or entered as a number. MID cannot recover zeros that were never stored as text unless you know the identifier’s intended width. If A2 contains the number 123 and the ID should always have five digits, use =TEXT(A2,"00000") to produce the text 00123. This assumes the fixed five-digit width is correct for the identifiers; it is not a safe general repair when original lengths vary.

When to use MID, FIND, or newer text functions

MID is a good choice when the required start and length are fixed, such as extracting characters 5 through 8 from consistently formatted product codes. LEFT is simpler when the desired characters are at the beginning, and RIGHT is simpler when they are at the end. When a section is defined by a delimiter rather than a fixed position, combine MID with FIND or consider a function designed to split text.

Assume A2 contains AB-12345 and the hyphen separates a prefix from a code. To find the first character after the hyphen and return three characters, use =MID(A2,FIND("-",A2)+1,3). It returns 123. This adjusts for the delimiter’s location, but FIND returns an error if the hyphen is missing; check that the source data has the delimiter before relying on the formula for a whole column.

In Microsoft 365 or Excel 2024, TEXTBEFORE and TEXTAFTER can make delimiter-based extraction easier to read. With AB-12345 in A2, =TEXTBEFORE(A2,"-") returns AB, while =TEXTAFTER(A2,"-") returns 12345. These functions need Microsoft 365 or Excel 2024; they are not available in every older Excel release. MID remains useful when you need a specific number of characters or must support a version without those newer functions.

Frequently Asked Questions

Why does MID return a blank instead of an error?
If the start position is beyond the end of the text, or the character count is zero, MID returns an empty string. Check calculated arguments and confirm the source cell is not blank.

Does MID start counting at zero?
No. The first character is position 1. A start position of 0 is invalid and returns #VALUE!.

Why does MID return the wrong part of a date?
A date is stored as a numeric value and displayed according to cell formatting. Convert it to a consistent text format with TEXT first, then use MID on that formatted text.

Can MID extract text after a changing delimiter?
Yes. FIND can locate the delimiter, and MID can start immediately after it. If the delimiter may be missing, handle that case explicitly; otherwise FIND can return an error.

Why does MID show the formula instead of the answer?
The cell may be formatted as Text, or the entry may begin with an apostrophe. Set the cell to General, then edit and re-enter the formula so Excel evaluates it.

Does MID preserve leading zeros?
MID returns text, so zeros that are present in the source text are retained. If an identifier was stored as a number, its leading zeros may already be gone; restore them only when the intended fixed width is known.

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.