In Excel, use the MID function to extract a specified number of characters from the middle of a text string: give it the text, the starting character position, and how many characters to return. For example, if A2 contains INV-2026-047, =MID(A2,5,4) returns 2026.
What MID does in Excel
MID is a text function for extracting part of a value based on character positions. Unlike LEFT and RIGHT, which take characters from the beginning or end, MID lets you choose where the extraction starts. It is useful when a code, date-like label, phone number, or other string follows a consistent layout.
For instance, a product code might place a region code in characters 4 through 5, or an identifier might put a four-digit year in a fixed position. MID returns the selected characters as text. It does not remove them from the original cell or change the source value.
Character positions start at 1, not 0. The first character in a cell is position 1, the second is position 2, and so on. Letters, digits, spaces, punctuation marks, and hyphens all count as characters. This detail matters when a string contains separators: the hyphen in a code takes up a position too.
MID syntax and its three arguments
The function takes three arguments in this order: the text to read, the starting position, and the number of characters to return. The text can be typed directly into a formula, but a cell reference is usually easier to maintain. The starting position and character count can be fixed numbers or calculated from other values.
- Text: The string or cell containing the characters. Excel can convert a number to text for this purpose, though leading zeros already lost from a numeric value cannot be restored by MID.
- Start position: The position of the first character to return. The first character is position 1.
- Number of characters: How many characters to return. A value of zero returns an empty result; a negative value causes an error.
Arguments can be combined with other functions. For example, a start position can be found with SEARCH, and the requested length can be calculated with LEN. This makes MID useful for both fixed-width data and strings whose separators move from row to row.
Basic examples with fixed character positions
Suppose A2 contains INV-2026-047. With that value in A2, the formula =MID(A2,5,4) returns 2026. The first four characters are INV-, so character 5 is the first digit of the year; the next four characters make up the year.
For a phone number, imagine A2 contains 555-014-8821. The hyphen after the area code is character 4, so the middle group begins at character 5. With that layout, =MID(A2,5,3) returns 014. The result stays text, which is helpful when a group begins with zero.
To return everything from a known starting position through the end of the text, calculate the remaining length with LEN. If A2 contains INV-2026-047, then =MID(A2,5,LEN(A2)-4) returns 2026-047. The string has 12 characters, and subtracting the four characters before the start leaves eight characters to return.
These examples work best when the source values have a predictable structure. If different rows contain different prefixes or optional separators, a fixed start position may extract the wrong characters. In that case, locate the separator first or use a function designed to split text.
Find the text between separators
A common question is how to use MID when the desired section is between two hyphens, parentheses, or other markers. The key is to calculate both the starting position and the length. SEARCH finds the positions of the markers; the difference between those positions gives the number of characters between them.
Assume A2 contains ORD-CA-731 and you want the text between the first and second hyphens. With that layout, =MID(A2,SEARCH("-",A2)+1,SEARCH("-",A2,SEARCH("-",A2)+1)-SEARCH("-",A2)-1) returns CA. The first hyphen is at position 4, so the extraction starts at 5. The second hyphen is at position 7, leaving two characters between the markers.
For a simpler pair of markers, suppose A2 contains (West) and the goal is the word inside the parentheses. With that layout, =MID(A2,FIND("(",A2)+1,FIND(")",A2)-FIND("(",A2)-1) returns West. FIND locates the opening and closing parentheses; the formula starts one character after the opening mark and returns only the characters before the closing mark.
SEARCH is not case-sensitive, while FIND is case-sensitive. That distinction usually does not matter for punctuation, but it does matter if the marker is a letter. Both functions return an error if the marker is missing, so formulas built this way may need an error-handling step when input data is inconsistent.
Use MID with other text functions
MID is especially useful as one part of a larger formula. LEN counts the total number of characters, while SEARCH or FIND locates a character or text fragment. Together, these functions can extract a middle section even when its exact position varies. The formula between hyphens above is one example: its start and length are calculated rather than typed as constants.
In Microsoft 365 and Excel 2024, TEXTBEFORE and TEXTAFTER can make delimiter-based extraction easier to read. For example, if A2 contains ORD-CA-731, then =TEXTBEFORE(TEXTAFTER(A2,"-"),"-") returns CA. TEXTAFTER first removes the content through the first hyphen; TEXTBEFORE then returns the content before the next hyphen. These functions are not available in every Excel release, so MID with SEARCH is a more compatible option for older versions.
For data that contains many repeated separators or needs to be split into several columns, a dedicated text-splitting feature may be more convenient than writing several MID formulas. The best approach depends on whether the layout is fixed, whether the separator is consistent, and which Excel version recipients of the workbook use.
Common MID errors and data issues
#VALUE!: This commonly appears when the starting position is less than 1 or the requested character count is negative. Check the values supplied to both arguments.- Blank result: If the starting position is beyond the end of the string, there are no characters to return. A character count of zero also gives an empty result.
- Unexpected characters: Spaces and punctuation count. Check for a leading space or an extra separator before assuming the function is wrong.
- Leading zeros disappear elsewhere: MID returns text, so it can preserve a zero in the extracted portion. But if Excel previously stored the source as a number, any leading zeros may already be gone. Store codes as text when those zeros are significant.
- Wrong part after filling down: A formula that uses fixed positions assumes every row has the same format. Compare several source rows and use calculated positions if the layout changes.
For reliable results, inspect the original value and count positions carefully, including invisible-looking spaces. If a cell may contain extra spaces at the beginning or end, cleaning the source data can help; do not remove spaces automatically if they are meaningful in the text.
MID compared with LEFT, RIGHT, and splitting
Choose the function that matches where the text sits. LEFT returns characters starting at the first character; RIGHT returns characters from the end; MID begins at a position you specify. All three are useful for fixed layouts, but none automatically understands the meaning of a code or searches for a delimiter unless combined with another function.
For example, if the first three characters are always a department code, LEFT is simpler. If the final four characters are always a year, RIGHT may be clearer. If the desired section is between two markers, MID combined with SEARCH or FIND can do the job. In newer Excel versions, TEXTBEFORE and TEXTAFTER often communicate delimiter-based intent more directly.
Do not confuse MID with a function that searches for text. MID extracts by position; it will return whatever characters occupy those positions, whether or not they form the intended word. Use SEARCH or FIND to locate a marker, then use those positions to guide extraction when the location is not fixed.
Compatibility and handling extracted values
MID is a long-standing Excel function and is available in Microsoft 365 for Windows, as well as in many earlier Excel versions. Its basic syntax is the same in Excel for Mac and Excel for the web. The newer TEXTBEFORE and TEXTAFTER alternatives require Microsoft 365 or Excel 2024, so check compatibility before sharing a workbook with people using older releases.
MID returns text, even if the extracted characters look like a number. This is often exactly what you want for identifiers, postal codes, or number groups with leading zeros. If the result must be used as a numeric value for arithmetic, convert it deliberately and confirm that the text contains only appropriate numeric characters. Avoid converting identifiers simply because they contain digits: sorting or calculations can then behave differently from text-based codes.
For ordinary letters, digits, and punctuation, position counting is straightforward. Some Unicode characters can have more complex representations, so unusual symbols or emoji may not behave like a single visible glyph in every context. If a workbook processes multilingual or symbol-heavy text, verify the extracted result with representative examples rather than assuming each displayed symbol occupies one simple position.
Frequently Asked Questions
How do I extract the middle characters in Excel?
Use MID with the source text, the position where the extraction begins, and the number of characters to return. If A2 contains INV-2026-047, =MID(A2,5,4) returns 2026.
Does MID start counting at zero or one?
It starts at 1. The first character in the text is position 1, including when the text begins with a space or punctuation mark.
How can I extract text between two characters?
Find the positions of the two markers with SEARCH or FIND, then use those positions as MID’s starting position and character count. Check what the formula should do if one of the markers is missing.
Why does MID return a blank cell?
A blank result can mean the starting position is beyond the end of the text or the requested character count is zero. Check the source value and the two numeric arguments.
Does MID return a number or text?
MID returns text. A result made only of digits may look numeric, but Excel treats it as text unless you convert it. Keeping it as text is often preferable for codes and values with leading zeros.
Related
- Convert PowerPoint to Excel: Steps, Options, and Limits
- Access Database Import from Excel: Steps and Tips
- Excel Attendance Format: A Clear Monthly Attendance Tracker
Try it: the Formula Explainer: paste any formula and see each function and reference in plain English.
More in Formulas & functions
- Trim Excel Column Values and Remove Extra SpacesTo trim values in an Excel column, enter =TRIM(A2) in a blank column beside the first value, fill…
- How to Copy Formulas in Excel to Other Rows: Step by StepTo copy a formula into rows below it in Excel, select the cell that contains the formula and…
- How to Find Repeated Names in Excel with COUNTIF FormulaTo find repeated names in Excel, put names in A2:A100, then enter =COUNTIF($A$2:$A$100,A2) in B2 and fill it…
- VLOOKUP in Excel: Return a Value from Another CellTo make VLOOKUP return a value from another cell, look up a key in the leftmost column of…
- Round Excel to 2 Decimal Places: Formula or FormatTo round a number to two decimal places in Excel, use the ROUND function: with the value to…
- Excel Text Plus Cell Value: Formulas, Formatting, and FixesTo combine text with a cell value in Excel, join them with the ampersand operator (&). For example,…
Sources: Function behaviour and menu paths: Microsoft Support documentation for Excel. Method & sources
Spotted an error in this guide? Let us know.