Excel MID Function: Extract Text From a Cell

If the text you want to extract is in A2, use =MID(A2,5,5) to return five characters starting at the fifth character. For example, if A2 contains INV-48271-CA, the result is 48271; MID extracts characters from a chosen position in a cell’s text.

What the MID function does

MID is Excel’s function for returning a section of text from the middle of a text string. You choose the source text, the position where extraction begins, and how many characters to return. The starting character is counted as position 1, not position 0. Spaces, hyphens, and other punctuation count as characters too.

This makes MID useful when a value follows a predictable structure. A product code might contain a category, a numeric identifier, and a region; a text string might contain a date or reference number in a known position. MID returns the selected characters, but it does not check whether they make sense as a code or convert them into a number automatically.

In the opening example, the characters in INV-48271-CA are I at position 1, N at 2, V at 3, the first hyphen at 4, and 4 at 5. Returning five characters from position 5 therefore gives 48271. If you begin at position 4 instead, the result includes the hyphen.

How to write a MID formula

A MID formula takes three arguments in this order: the text to read, the position of the first character to return, and the number of characters to return. In Excel for Microsoft 365 on Windows, enter the formula in the result cell and press Enter. Excel uses commas between arguments in the examples here; some regional settings use semicolons instead.

With a product code in A2, where A2 contains AB-739-X, enter =MID(A2,4,3) to return 739. The hyphen is position 3, so the first digit is position 4; the three-character count returns 7, 3, and 9.

For a one-character result, with a word in A2 and the desired position in B2, enter =MID(A2,B2,1). If A2 contains ORCHARD and B2 contains 4, the result is H. Using a cell reference for the start position is handy when you want to change the position without editing the formula.

After entering a formula, you can copy it down a column using the fill handle, the small square at the lower-right corner of the selected cell. References such as A2 normally adjust to A3, A4, and so on as you fill down. If every row has the same layout, this is a quick way to extract the same part from many values.

Extracting text when positions vary

MID is simplest when the section always starts at the same position and has the same length. If a code has a fixed three-character prefix, for example, the remaining characters start at position 4. With the full code in A2, enter =MID(A2,4,LEN(A2)-3). If A2 contains CAT8472, the formula returns 8472: LEN counts seven characters, and the requested length is seven minus three, or four.

This approach adapts to different total lengths, but it depends on the prefix always having three characters. If a value is shorter than the assumed prefix, the calculated character count can become negative and MID returns an error. Check that the input follows the expected format before applying a formula across a large list.

When the text is separated by delimiters rather than fixed positions, FIND can locate those delimiters. With a two-hyphen code in A2, such as SKU-48271-CA, enter =MID(A2,FIND("-",A2)+1,FIND("-",A2,FIND("-",A2)+1)-FIND("-",A2)-1) to return 48271. FIND locates the first hyphen at position 4 and the next at position 10; MID starts at position 5 and returns five characters, stopping before the second hyphen.

This delimiter-based formula assumes that A2 contains at least two hyphens in the expected order. If a delimiter is absent, FIND returns an error, which then flows through the formula. For cleaner data, you can validate the input separately or use a formula designed to handle missing separators. In Microsoft 365 and Excel 2024, TEXTBEFORE and TEXTAFTER can be convenient alternatives for splitting text around delimiters; they are not available in every older Excel version.

Common errors and edge cases

One frequent mistake is counting from zero or forgetting that punctuation occupies a position. Excel starts at 1. To debug an unexpected result, count every character from the left, including spaces and separators, then confirm both the start position and requested length.

If the starting position is greater than the text’s length, MID returns an empty string.
If the requested character count is zero, the result is also an empty string.
A starting position below 1 or a negative character count produces a #VALUE! error.
Decimal values supplied for the position or character count are truncated to integers.
Leading and trailing spaces are part of the text and can shift the characters you expect to extract.

A result that looks blank may therefore be an empty string rather than a truly empty cell. This distinction can matter in later formulas, filters, or checks for blank cells. If inconsistent spacing is the cause, clean the source data or account for spaces before relying on fixed character positions.

Text results, numbers, and leading zeros

MID returns text, even when the extracted characters look like a number. That is often desirable for IDs, postal codes, and account references because their character sequence matters more than arithmetic. If you need to calculate with the result, convert it deliberately with a suitable number-conversion method and consider whether the value can contain leading zeros.

For example, if A2 contains the text code AB0079 and you extract positions 3 through 6, the result is the text 0079. Converting that result to a number would produce 79 and discard the leading zeros. Keep the result as text when those zeros are part of the identifier.

There is a related source-data issue: a cell that displays leading zeros because of number formatting does not necessarily store those zeros as text. MID works on the underlying value, not on every visual formatting choice. If the exact characters matter, store or import the identifier as text rather than depending on a display format to preserve its appearance.

MID compared with LEFT, RIGHT, and MEDIAN

Use MID when extraction begins at a position inside the text. LEFT returns characters from the beginning, while RIGHT returns characters from the end. If a fixed-length code always ends with a two-letter region, RIGHT may be simpler for that part; if the region is between other components, MID or a delimiter-based method is more appropriate.

MID is also easy to confuse with MEDIAN because both words suggest something in the middle. They do different jobs. MID extracts characters from text; MEDIAN calculates the statistical middle of a set of numbers. If you mean the middle value in a list of numbers, use MEDIAN rather than MID.

For example, with the numbers 3, 8, and 12 in A1:A3, enter =MEDIAN(A1:A3) in another cell to return 8. With an even number of numeric values, MEDIAN returns the average of the two central values after ordering them. It does not extract the central characters from a cell’s text.

Version and platform notes

MID is a long-established Excel function and is available in current desktop Excel, including Microsoft 365 for Windows and Mac, as well as Excel for the web. The basic formula and its character positions work the same way across these platforms. The examples use commas as argument separators; Excel may display or require semicolons depending on regional settings.

Excel for Mac has the same MID function. You can enter the formula in a cell just as you would on Windows; the exact keyboard shortcut for editing a cell differs by keyboard and Excel setup, so using the formula bar or double-clicking the cell is a practical alternative. Excel for the web also supports MID, though its interface and available editing shortcuts differ from the desktop app.

For ordinary English letters, digits, spaces, and punctuation, counting characters is straightforward. Some scripts contain characters represented internally by more than one code unit, and Excel has compatibility behavior for certain Unicode characters. If a formula handles emoji or uncommon symbols unexpectedly, test the specific text and Excel version rather than assuming every visible symbol occupies one internal unit.

Frequently Asked Questions

What does MID mean in Excel?
MID is a text function that returns a specified number of characters from a chosen position in a text string. It does not calculate a numeric midpoint.

How do I extract the middle part of a cell?
Identify where the part begins and how many characters it contains, then use MID with those values. For a code in A2 where the desired text starts at character 4 and is 3 characters long, use =MID(A2,4,3).

Why does my MID formula return a blank?
The starting position may be beyond the end of the text, or the requested character count may be zero. Check the cell’s actual contents, including spaces, and recount positions from 1.

Can MID extract text between two symbols?
Yes. A formula can use FIND to locate the symbols and calculate the characters between them, provided the expected delimiters exist. For more readable splitting around delimiters, TEXTBEFORE and TEXTAFTER are options in Microsoft 365 and Excel 2024.

Does MID return a number or text?
MID returns text. A numeric-looking result can be converted for arithmetic, but conversion may remove leading zeros, so retain it as text when it is an identifier.

Is MID the same as MEDIAN?
No. MID extracts characters from text. MEDIAN returns the middle statistical value of a numeric set, or the average of the two central values when the set has an even number of values.

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.