Excel MID Cell Reference: Extract Text from a Cell

To extract characters from a cell in Excel, use the MID function with that cell as its first argument: =MID(A2,5,4). This returns four characters from cell A2, starting at the fifth character; for example, if A2 contains INV-2048-CA, the result is 2048.

What the MID function does

MID returns a section of text from within a longer text value. You tell Excel where to begin and how many characters to return. Its syntax is MID(text, start_num, num_chars): text is the text or cell reference to read, start_num is the position of the first character to extract, and num_chars is the number of characters to return.

Character positions start at 1, not 0. In INV-2048-CA, I is position 1, N is 2, V is 3, the hyphen is 4, and the first digit is 5. MID counts spaces and punctuation as characters too. The function returns a text value, even if the extracted characters look like a number. If you need to calculate with the result, you may need to convert it to a number separately.

The first argument can be a direct cell reference such as A2, a range reference that resolves to one cell, or text supplied by another function. In ordinary worksheet use, a direct reference is the clearest way to extract part of a cell’s contents.

Use a cell reference in a basic MID formula

Suppose A2 contains the code INV-2048-CA and you want the four digits in its middle. With the code in A2, this formula returns 2048:

=MID(A2,5,4)

The first argument, A2, tells Excel which cell to read. The 5 says to begin at the fifth character, and the 4 says to return four characters. If the source code is in another cell, replace A2 with that cell address. For example, if the text is in D7, use D7 as the first argument and adjust the position and length to match the text there.

You can also put the starting position and character count in cells. With the code INV-2048-CA in A2, the start position 5 in B2, and the length 4 in C2, the formula below returns 2048:

=MID(A2,B2,C2)

This setup is useful when extraction rules vary by row or when a user should be able to change the position and length without editing the formula. For a table of records, enter the formula in the first result row and fill it down; references will adjust to the corresponding row unless you lock them.

Extract text between delimiters

A fixed character position works when every value follows the same format. If the part you need follows a delimiter such as a hyphen, you can use SEARCH to find its position. For example, with INV-2048-CA in A2 and two hyphens separating the three parts, this formula finds the text between the hyphens and returns 2048:

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

SEARCH locates the first hyphen, then locates the next one after it. MID starts one character after the first hyphen and returns the number of characters between those two positions. This is more adaptable than hard-coding the start and length if the middle part can vary in length, such as 7, 2048, or 31675.

The delimiter must actually appear in the cell in the pattern the formula expects. If a code lacks one of the hyphens, SEARCH cannot find it and returns an error. If your data has inconsistent separators, spaces, or extra punctuation, clean or validate the source values before relying on a delimiter-based formula. SEARCH is not case-sensitive; use FIND instead when the location depends on matching letter case.

References, copying, and changing the source cell

A reference such as A2 is relative. When you copy a formula down one row, Excel normally changes A2 to A3, then A4, and so on. That is usually what you want when each row contains a different code. You can fill a formula down with the fill handle or select the formula cell and press Ctrl+D in Excel for Windows. On Mac, the equivalent fill-down shortcut is Control+D.

Use dollar signs to make a reference absolute or partly absolute when copying. For example, $A$2 always points to A2; $A2 keeps column A fixed but lets the row change; and A$2 keeps row 2 fixed but lets the column change. In a MID formula, this can be useful when every row should read from one fixed source cell, or when all rows should use a start position stored in a fixed settings cell.

To toggle reference styles while editing a formula, select the cell reference in the formula bar or formula and press F4 in Windows. On some Mac keyboards, use Command+T; keyboard behavior can depend on the keyboard and Excel settings. You can also type the dollar signs yourself. Check the formula after copying it, especially if the source cell and the cells containing start and length values should not all shift in the same way.

Choose MID, LEFT, RIGHT, or another approach

MID is the right choice when the characters begin somewhere inside the text. LEFT extracts a specified number of characters from the beginning, while RIGHT extracts them from the end. For example, for a fixed-format code such as INV-2048-CA, MID can take the central section, LEFT can take the INV prefix, and RIGHT can take the CA suffix. If the portion’s position changes from one record to another, combine MID with a search function rather than assuming it always starts at the same place.

For more involved text-cleaning tasks, Excel for Microsoft 365 and Excel 2021 also offer functions such as TEXTBEFORE and TEXTAFTER. These can make delimiter-based extraction easier to read when you want everything before or after a delimiter. TEXTSPLIT is another option in Microsoft 365 and Excel 2024 when a value should be divided into multiple pieces. Those functions do not replace MID in every situation: MID remains useful when you need a precise character position and length, or when a workbook must work in older Excel versions.

Do not confuse MID with a reference to the middle cell of a selected range. MID is a text function; it does not identify the center cell in a group of cells. If you meant the center position in a range rather than extracting characters, that is a different task and requires a formula based on the range’s dimensions.

Numbers, dates, and displayed formatting

MID is designed to return text. If A2 contains the text value 00482 and you extract its last three characters, the result is the text 482; leading zeros can disappear from view in later operations that convert the result to a number. If those zeros are meaningful, keep the extracted value as text or apply an appropriate number format after a deliberate conversion.

Cells containing numbers or dates need extra care. Excel stores dates as serial numbers and displays them according to the cell’s number format. MID works with the underlying value converted to text; it does not simply take characters from the date as they appear on the screen. For a date, first create text in the format you need, then extract from that text, or use date functions such as MONTH and DAY if the goal is to obtain a date component rather than a string of characters.

Similarly, number formatting such as thousands separators, currency symbols, or leading zeros may affect what a person sees without changing the stored value in the same way. If the exact visible representation matters, build that representation explicitly as text before extracting from it. This distinction avoids surprises when a formula appears to return a serial number or omits a displayed symbol.

Common errors and ways to troubleshoot

If MID returns the wrong characters, count from the first character and include spaces, hyphens, and other punctuation in the count. A frequent mistake is treating the first position as zero, which shifts the extraction by one character. Another is using a fixed character count when some source values have different lengths.

#VALUE!: start_num is less than 1, num_chars is negative, or a nested function such as SEARCH cannot find its requested text.
Blank result: the requested starting position is beyond the end of the text, or the referenced cell is blank.
Unexpected characters: the source includes leading spaces, hidden characters, or a different delimiter than expected. Inspect the cell contents and, when appropriate, clean the text before extracting.
Numbers do not behave as numbers: MID returns text. Convert the result only if a later calculation requires a numeric value and the text contains a valid number.

Test a formula against a few representative examples before filling it through a large dataset. Include the shortest and longest values, missing delimiters, and any codes with leading zeros. If a formula uses SEARCH, check that every row has the delimiter pattern it requires. For formulas copied across or down, verify that relative and absolute references still point to the intended cells.

Frequently Asked Questions

Can MID use a cell reference?
Yes. Put the cell reference in the first argument, such as A2 in =MID(A2,5,4). Excel extracts characters from the contents of that referenced cell.

What does the 5 mean in MID(A2,5,4)?
It is the starting character position. Excel counts the first character as position 1, so the formula begins at character five and returns four characters.

Can MID extract text after a hyphen?
Yes. Use MID with SEARCH to locate the hyphen and start just after it. If you need the text between two hyphens, the formula must also find the second hyphen to calculate the section’s length.

Why does MID return text instead of a number?
MID extracts characters, so its result is text even when those characters are digits. Keep it as text when formatting matters, such as leading zeros; convert it only if you need to perform arithmetic.

What happens if the starting position is past the end of the cell contents?
MID returns an empty string when the starting position is beyond the available text. A starting position below 1 or a negative character count produces a #VALUE! error.

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.