Excel MID Formula Not Working? Common Fixes That Work

If Excel’s MID formula is not working, first check that its starting position and character count are correct: MID counts from the first character, starting at 1. Also check for hidden spaces, an incorrect cell reference, or a value stored differently from how it appears on the worksheet.

MID is a text function: it returns a specified number of characters from inside a text value. The sections below explain how its arguments work, how to diagnose errors and unexpected results, and what to do when the source is a number or date.

What MID does and how its arguments work

MID takes three arguments in this order: the text to examine, the position of the first character to return, and the number of characters to return. The first position is 1, not 0. For example, in “CAT-4821,” C is position 1, the hyphen is position 4, and 4 is position 5.

With the text CAT-4821 in A2, =MID(A2,5,4) returns 4821. This is useful when the part you want always occupies the same positions, such as a fixed-width product code. The function returns text, even when the extracted characters look like a number.

A starting position beyond the end of the text generally returns an empty string, as does a character count of zero. A negative character count or a starting position below 1 produces a #VALUE! error. If the requested count extends past the end, MID returns the remaining characters rather than reporting an error.

Check the start position and character count

The most common cause of a plausible but incorrect result is counting from the wrong place. Count every character, including spaces, punctuation, hyphens, and other separators. If the source contains “INV-5827,” the first digit is at position 5 because the hyphen occupies position 4.

With INV-5827 in A2, =MID(A2,5,4) returns 5827. If the result instead includes the hyphen or drops the final digit, revisit both numbers: the second argument sets where extraction begins, while the third sets how many characters Excel returns.

When the desired part follows a delimiter that can move, a fixed starting position is fragile. For example, “ORD-5827-X” and “ORDER-5827-X” have different positions for the digits. With either value in A2, =MID(A2,FIND("-",A2)+1,4) returns the four characters after the first hyphen. This approach works when the delimiter is present and the target always has four characters; otherwise, the formula needs to account for the actual data pattern.

Look for spaces, hidden characters, and inconsistent text

Spaces count as characters. A leading space shifts every later position, and a trailing space can make a result appear to contain an extra blank. Data copied from websites, reports, or other systems may also include nonbreaking spaces or control characters that are difficult to see.

To inspect the source length, with the text in A2 enter =LEN(A2). Compare the result with the number of characters you can see. LEN counts spaces and other characters too, so an unexpectedly high count is a useful clue, but it does not identify which character is extra.

If ordinary extra spaces are the issue, TRIM can remove leading and trailing spaces and reduce repeated internal spaces to a single space. With “ AB 123” in A2, =MID(TRIM(A2),4,3) returns 123. Be careful: TRIM changes the text before MID counts positions. If the spaces are meaningful, or if you need to preserve the original spacing, do not apply it blindly. TRIM also does not remove every kind of invisible character; nonbreaking spaces may need to be replaced explicitly.

For a quick check, click the source cell and inspect the formula bar, or use the arrow keys to move through the text while watching the cursor position. Do not judge character positions only by how a cell looks: column width, alignment, and number formatting can conceal details.

Diagnose #VALUE!, blanks, and unexpected output

A #VALUE! result usually points to an invalid argument. Check that the start position is at least 1, the character count is not negative, and the arguments contain usable numbers where positions and counts are required. If a position or count comes from another cell, inspect that cell too; text that looks numeric may not behave as expected in every expression.

A blank result is not necessarily a formula failure. It can mean that the starting position is beyond the end of the source, that the requested count is zero, or that the referenced source cell is blank. Verify the reference by selecting the formula cell and tracing which cell it points to. A copied formula may have shifted from A2 to A3, for instance, and may now be reading an empty row.

If Excel displays the formula itself instead of a result, check whether the cell is formatted as Text or the formula was entered with a leading apostrophe. Change the cell format to General, then edit the formula and press Enter so Excel evaluates it. Also check whether Show Formulas is enabled; in Microsoft 365 for Windows, toggle it with Ctrl+` (the grave-accent key, usually above Tab). On Mac, the shortcut can differ by keyboard and Excel version; use the Formulas tab’s Show Formulas command if needed.

If a correct formula seems not to update after the source changes, go to Formulas > Calculation Options and select Automatic. Press F9 to recalculate. Calculation mode can affect the workbook, so check it before assuming MID itself is broken.

Numbers, leading zeros, and dates need special care

A cell can display something different from the value MID is reading. Number formats can add visible leading zeros or currency symbols without storing those characters in the underlying value. For example, a numeric value of 1234 displayed with a six-digit format may look like 001234, but the zeros may not be part of the cell’s stored content.

If A2 contains the number 1234 and you need to treat it as a six-digit identifier, convert it to formatted text first. With that value in A2, =MID(TEXT(A2,"000000"),2,4) returns 0123. The format pattern supplies two leading zeros before MID extracts the four characters. This is appropriate for identifiers, not for ordinary arithmetic.

Dates have a similar issue: Excel stores dates as serial numbers and displays them using a date format. MID may therefore not extract the month or day shown on screen. With an actual date of March 7, 2026, in A2, =MID(TEXT(A2,"mm/dd/yyyy"),1,2) returns 03. The TEXT format defines the displayed string that MID reads. Choose a format that matches the date convention you need, especially if the workbook is shared across regions where day and month order may differ.

If the extracted digits must be used in arithmetic, convert the text result to a number deliberately. With “ID-48” in A2, =VALUE(MID(A2,4,2)) returns the number 48. Conversion removes meaningful leading zeros, so keep an identifier as text if those zeros are part of its identity.

When a different function or method is better

MID is a good fit when the target has a known position or lies between delimiters. It is less suitable when the source format varies unpredictably. FIND locates a delimiter, as in the hyphen example above; SEARCH is another option when case-insensitive searching is needed. LEFT extracts from the beginning, while RIGHT extracts from the end, so either may be simpler when the target is at an edge.

For repeated cleanup, consider whether the data should be split into separate columns rather than extracted by a formula. In Excel for Microsoft 365 on Windows, Data > Text to Columns can split consistent delimited text, such as codes separated by hyphens. This is useful for a one-time or repeatable worksheet transformation, but it changes where the data is placed and should be previewed before completing the operation.

Newer Excel functions can also make text extraction more readable in some cases, but they are not necessary to fix MID. If a workbook must work in older Excel versions, MID, FIND, LEFT, and RIGHT are often safer choices than functions available only in newer releases.

A quick troubleshooting checklist

Work through these checks in order; they usually reveal whether the problem is the formula, the source value, or the worksheet setup:

Confirm the formula points to the intended source cell, especially after copying it down a column.
Count characters from 1, including spaces and punctuation, then verify both the start position and requested length.
Check for leading spaces, inconsistent delimiters, or invisible characters in the source.
Look at the actual cell value, not only its number or date formatting.
For #VALUE!, check that the position is at least 1 and the character count is zero or greater.
If the formula displays as text or does not recalculate, check the cell format, Show Formulas, and calculation mode.
Test the extraction on one known example before filling the formula down through the full dataset.

In formulas shown here, commas separate arguments. Depending on Windows regional settings, Excel may expect semicolons instead. If Excel rejects a formula even though the function and references are correct, use the argument separator configured for that installation.

Frequently Asked Questions

Why does MID return #VALUE!?
Usually the start position is below 1, the character count is negative, or an argument used for a position or count is invalid. Check those values and any cells they refer to.

Why does MID return blank when the source cell is not blank?
The start position may be beyond the text’s length, or the requested character count may be zero. A wrong cell reference can also point the formula to an empty cell.

Does MID count spaces?
Yes. Regular spaces count as characters, just like letters and punctuation. Hidden or nonbreaking spaces can also affect what you see, so inspect copied data if the positions seem off.

Why won’t MID extract the date or number I see on screen?
Excel may store a numeric value or date differently from its displayed format. Use TEXT to create the exact text representation first, then extract from that text. Keep in mind that MID returns text.

Can MID extract text that changes position?
Not reliably with a fixed start number. Find a stable delimiter with FIND or SEARCH and calculate the start position from it, or split the source data into columns if the structure is consistently delimited.

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.