Excel MID Function: Extract Text from a Cell’s Middle

Excel MID Function: Extract Text from a Cell's Middle

To extract characters from the middle of a cell in Excel, use the MID function, which takes a text value, a starting position, and the number of characters to return. With the value INV-2026-0842 in A2, =MID(A2,5,4) returns 2026.

How the MID function works

MID is useful when the characters you need are not necessarily at the beginning or end of a text value. Its three arguments tell Excel what to read and how much of it to return: the text or cell reference, the position where extraction begins, and the character count. Excel numbers character positions from 1, not 0. In the example above, the first character, I, is position 1; the 2 in 2026 is position 5.

The start position and character count are both numbers. The count describes how many characters to return, not the final position. For example, starting at position 5 and taking 4 characters returns positions 5 through 8. The result is text, even if the characters look like a number. That distinction matters for codes, postal codes, and other values where leading zeroes should remain visible.

MID works in Microsoft Excel for Microsoft 365 on Windows, as well as in earlier desktop versions of Excel. You can enter it directly in a worksheet cell or use it as part of a longer formula. It does not alter the original cell: it creates a result in the formula cell, which updates if the source text changes.

Extract text from a fixed position

When each entry follows the same pattern and the target characters always occupy the same positions, a simple MID formula is usually the clearest choice. For instance, with product codes such as ABX739Q in A2 and the three-character number always at positions 4 through 6, enter =MID(A2,4,3) to return 739. This approach is easy to fill down a column when every row uses the same format.

For a longer example, with the text PO-5831-CA in A2, =MID(A2,4,4) returns 5831. The hyphens count as characters, so the first digit is at position 4. Counting carefully before writing a fixed-position formula helps prevent off-by-one errors.

To apply the same formula to more records, enter it beside the first record and fill it down. On Windows, select the formula cell and the cells below it, then press Ctrl+D. On Mac, the corresponding Fill Down shortcut is Command+D. You can also drag the fill handle at the lower-right corner of the selected cell. Excel adjusts a relative reference such as A2 to A3, A4, and so on as it fills the formula.

Extract text between delimiters

Fixed positions are fragile if the length of a prefix varies. If a code contains two hyphens and you need the text between them, you can use SEARCH to find the hyphen positions and calculate the length to extract. With INV-2026-0842 in A2, the following formula returns 2026:

With the full code in A2, where the desired text lies between its first and second hyphens:

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

SEARCH finds a character’s position. The first SEARCH locates the first hyphen, so adding 1 sets the starting position just after it. The second SEARCH starts looking after the first hyphen and locates the next one. Subtracting the first hyphen’s position and 1 gives the number of characters between them. This version handles a variable-length middle section, provided both delimiters are present and in the expected order.

If the desired value is everything after the first hyphen, the length can instead be calculated from the total text length. With INV-2026-0842 in A2, use this formula to return 2026-0842:

With the complete code in A2 and a hyphen separating the prefix from the remainder:

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

SEARCH is not case-sensitive, which is usually fine for punctuation delimiters. If the delimiter is a letter and uppercase and lowercase must be treated differently, FIND is the case-sensitive alternative. Both SEARCH and FIND return an error if the character is not present, so a formula built around either function may need an error-handling plan when the input is inconsistent.

Choose between MID and other text functions

The right function depends on where the target text sits and how consistently the data is structured:

MID: Use it for a section in the middle, especially when its start and length are known or can be calculated.
LEFT: Use it when you need a set number of characters from the beginning.
RIGHT: Use it when you need a set number of characters from the end.
TEXTBEFORE and TEXTAFTER: Consider these when the task is defined by a delimiter rather than a position. They are available in Microsoft 365 and Excel 2024, but not in every older Excel release.

For example, with INV-2026-0842 in A2, this formula combines TEXTAFTER and TEXTBEFORE to return 2026, the text between the first and second hyphens:

With the complete code in A2 and hyphens surrounding the target section:

=TEXTBEFORE(TEXTAFTER(A2,"-"),"-")

That formula is shorter than the equivalent MID-and-SEARCH version, but it requires a newer Excel version. MID remains valuable when the source data has a fixed layout, when you need a specific character count, or when a workbook must also work in older versions. If the delimiter can appear several times or is missing from some records, decide which occurrence you mean and how missing delimiters should be handled before choosing a formula.

Common problems and how to fix them

A formula that returns an unexpected result is often using the wrong start position or count. Remember that spaces and punctuation occupy positions just like letters and digits. If the result is shifted by one character, write out the sample text and number each character before changing the formula.

#VALUE!: This can occur if the starting position is less than 1 or the character count is negative. Check calculations used to build either argument. A zero character count returns an empty result.
Blank result: If the starting position is beyond the end of the text, MID returns an empty string. Check whether a delimiter search or calculated position points too far into the cell.
Shorter result than expected: If the requested count runs past the end of the text, MID returns only the characters that remain. Check the source value and the starting position.
Missing leading zeroes: MID returns text, but it cannot restore zeroes that were already discarded when Excel stored an entry as a number. Store identifiers as text or import them with the correct data type if zeroes are significant.

Dates need special attention. Excel stores dates as serial numbers and displays them using a date format. Extracting characters from a date value may therefore work on the underlying serial number rather than the displayed date. If the goal is to extract part of a date, use a date-specific function such as YEAR, MONTH, or DAY, or convert the date to the intended text format first. Similarly, a number’s displayed formatting—such as commas or leading zeroes—does not necessarily match the underlying value that a text formula reads.

Practical tips for reliable formulas

Before filling a formula down a large list, try it on a few representative records: a typical value, the shortest value, and any known exception. Check that the source cells use the same pattern. A formula that assumes the middle section is always four characters long will not correctly handle a three-character section just because it worked on the first row.

For extraction based on delimiters, consider what should happen when the delimiter is absent, appears only once, or appears more than twice. SEARCH-based formulas generally return an error when they cannot find a required delimiter. You can either correct inconsistent source data, filter those rows for review, or add error handling after deciding what a missing delimiter should mean. Avoid suppressing every error without checking it: an error can reveal that a record does not match the format the formula expects.

Keep the source value intact and put the formula in a separate column if you may need to audit or revise the extraction. If you later need static results rather than formulas, copy the results and use Paste Special > Values. That replaces the formulas with their current outputs, so retain a copy of the original data if you need to recalculate them later.

Frequently Asked Questions

How do I get the middle characters from an Excel cell?
Use MID with the cell reference, starting character position, and number of characters. For example, with text in A2, =MID(A2,3,5) returns five characters beginning at position 3.

Does MID count spaces and punctuation?
Yes. Spaces, hyphens, commas, and other characters in the text count toward the starting position and character count. Include them when counting positions manually.

Can MID extract text between two symbols?
Yes. Combine MID with SEARCH or FIND to locate the symbols and calculate the number of characters between them. Use SEARCH for a case-insensitive search and FIND when letter case matters.

Why does MID return a number as text?
MID is a text-extraction function, so its result is text even when it contains only digits. That is often useful for identifiers. If you need to calculate with the result, convert it to a number deliberately, while considering whether leading zeroes should be preserved.

What is the difference between MID and TEXTAFTER?
MID extracts a specified number of characters from a position. TEXTAFTER returns text after a specified delimiter and is available in Microsoft 365 and Excel 2024. For a value between two delimiters, TEXTBEFORE can be combined with TEXTAFTER; MID is a useful option for fixed positions and broader version compatibility.

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.