Use Excel’s MID function to extract a specified number of characters from a text value, starting at a position you choose. Its basic pattern is =MID(text, start_num, num_chars); for example, with a code in A2, =MID(A2,5,4) returns four characters beginning at the fifth character.
What the MID function does
MID is a text function for taking a section from inside a longer string. It is useful when the part you need has a known position: a year in an order code, a region marker in an employee ID, or a fixed-width field in an imported file. Unlike a function that takes characters only from the beginning or end, MID lets you specify where the extraction starts.
The function returns text, even when the extracted characters look like a number. That distinction matters for values such as product codes or postal codes, where a leading zero may be meaningful. The returned text can be displayed, joined to other text, or converted to a number if that is genuinely what your calculation needs.
MID is available in Microsoft Excel for Microsoft 365 on Windows, and it is also supported in Excel for Mac and Excel for the web. The formula works the same way across these versions; you do not need a special ribbon command to use it. Enter it in a cell, or type it in the formula bar.
MID syntax and character positions
The syntax has three arguments: MID(text, start_num, num_chars). The text argument is the cell or text string to read. start_num is the position of the first character to return, and num_chars is how many characters to return. Excel counts from 1, not 0, so the first character is position 1.
For a concrete example, suppose A2 contains INV-2026-0048. Position 1 is I, position 2 is N, position 3 is V, and position 4 is the hyphen. Position 5 is the first digit of the year. With that value in A2, =MID(A2,5,4) returns 2026. The result is text, not a numeric value.
- If
num_charsis longer than the remaining text, MID returns the characters that remain. - If
start_numis greater than the text length, MID returns an empty string. - If
start_numis less than 1, ornum_charsis negative, Excel returns#VALUE!. - A
num_charsvalue of 0 returns an empty string.
Use ordinary positive whole numbers for the position and length. Excel can coerce some numeric-looking arguments, but formulas are easier to understand and maintain when the intended values are explicit.
Extract a fixed section from a string
MID is simplest when records follow a consistent pattern. Suppose A2 contains the order code ORD-58317, and you want the three digits immediately after the hyphen. With that value in A2, use =MID(A2,5,3). The result is 583, because the letters and hyphen occupy the first four character positions.
For a longer example, suppose A2 contains INV-2026-0048. With that value in A2, =MID(A2,10,4) returns 0048. This is often preferable to converting the extracted result to a number: converting it would remove the leading zeros and produce 48, which may no longer match the original identifier.
For fixed-width data, first identify the character positions in a representative value. You can count them manually, or use a worksheet cell with a ruler-like sequence of characters during setup. Check examples that have the shortest and longest expected values, and confirm that spaces or punctuation do not shift the positions. If the source format changes, a fixed-position formula may still return something, but it could silently extract the wrong characters.
Extract text between delimiters
When the length of the text you want varies, combine MID with a function that finds the boundaries. For example, the year in an invoice code may always sit between two hyphens, even if other parts of the code change. FIND returns the position of a character or string, so the first FIND can locate the opening hyphen and another FIND can locate the next one.
With INV-2026-0048 in A2, this formula returns the text between the first and second hyphens:
=MID(A2,FIND("-",A2)+1,FIND("-",A2,FIND("-",A2)+1)-FIND("-",A2)-1)
The result is 2026. The start position is one character after the first hyphen. The character count is the distance between the hyphens, minus the delimiter itself. This approach adapts if the year has a different number of characters, provided the two delimiters are present and in the expected order.
You can use the same idea with parentheses, slashes, or other markers. For instance, in a cell containing Item (blue) - large, locate the opening and closing parentheses and return the characters between them. If your Excel version includes the newer TEXTBEFORE and TEXTAFTER functions, those may make delimiter-based formulas shorter. They are available in Microsoft 365 and Excel 2024; MID remains useful when you need compatibility with older Excel versions or a specific character position.
Handle variable-length text and missing markers
If you know where the text starts but want everything from that point to the end, you do not have to calculate the exact remaining length. MID returns the remainder when the requested character count extends beyond the string. For example, if A2 contains CODE-AB123, the following formula returns AB123:
With CODE-AB123 in A2, use =MID(A2,6,LEN(A2)). The result is AB123. LEN counts the characters in the cell, and the generous character count means MID can return the remaining text. You can also calculate the precise remainder as =MID(A2,6,LEN(A2)-5), but that is usually less flexible if the starting position changes.
A delimiter-based formula can fail when the source text does not contain the delimiter. FIND then returns an error, and that error flows into MID. If a missing marker is an expected possibility, decide how the result should behave: show a message, return a blank, or flag the row for review. For example, with a value in A2 where the text to extract always begins after a hyphen, =IFERROR(MID(A2,FIND("-",A2)+1,4),"") returns a blank if no hyphen is found. A blank result is convenient for reports, but it can conceal bad source data, so a visible warning may be safer when records need checking.
Use MID with dates and numeric data
Excel stores dates as serial numbers and displays them according to cell formatting. If you pass a date cell straight to MID, the function works with the value’s text representation, which may be a serial number rather than the date format visible in the worksheet. To extract a month from a real date, convert it to the text layout you want first.
With an actual Excel date in A2, use =MID(TEXT(A2,"yyyy-mm-dd"),6,2). The formula converts the date to year-month-day text and returns the two characters in the month position. For a date in November 2026, the result is 11 as text. Choose the format string deliberately: the displayed date format in the cell does not automatically control the output of MID.
For numeric-looking identifiers, keep in mind that MID returns text. With INV-2026-0048 in A2, =VALUE(MID(A2,10,4)) returns the number 48, not the text 0048. Use VALUE only when you need numeric behavior for arithmetic or comparison and losing leading zeros is acceptable. If the extracted value is an ID, keep it as text.
Choose between MID, LEFT, RIGHT, and newer functions
Choose a function based on where the desired text sits and how stable the source pattern is. LEFT returns characters from the beginning, RIGHT returns characters from the end, and MID returns characters from a position inside the string. If you need the last four characters of a code, RIGHT is generally more direct; if you need characters 5 through 8, MID makes the intended positions clear.
Use FIND or SEARCH with MID when a marker determines the position rather than a fixed count. FIND is case-sensitive, while SEARCH is not; both can locate text for a formula. If the target always starts or ends around a delimiter, TEXTBEFORE and TEXTAFTER may be easier to read in supported versions. For tasks that split a string into several pieces, TEXTSPLIT can be an option in Microsoft 365 and Excel 2024. These newer functions are not available in every older Excel release, so use MID when workbook compatibility matters.
- Use LEFT for a known number of characters at the start.
- Use RIGHT for a known number at the end.
- Use MID for a known position inside the text.
- Use a delimiter-based formula when the position or length varies with the content.
Common MID errors and how to fix them
The most common problem is counting positions incorrectly. Remember that spaces and punctuation count as characters. In AB 123, the space takes a position just like a letter or digit. If a formula returns a neighboring character instead of the intended one, inspect the source string character by character and verify both the start position and the requested length.
#VALUE! usually points to an invalid position or length, such as a start position below 1 or a negative character count. It can also appear when a nested calculation produces an invalid argument. If the formula uses FIND, check that the searched-for marker exists in every relevant row. Use IFERROR only when you have chosen a sensible fallback; it should not be used to hide errors that need correction.
A result that looks right but will not behave like a number is often simply text. MID does not turn a numeric-looking substring into a numeric value. Convert deliberately with VALUE when appropriate, or keep the text if preserving leading zeros is important. Another common surprise is extracting from a date cell: format the date as text with TEXT before using MID, as described above.
Finally, formulas can be correct for one row and wrong for another if the source pattern is inconsistent. Test with varied real examples, including missing delimiters, extra spaces, short strings, and values with leading zeros. If your data has unwanted spaces at the edges, consider cleaning the source or using TRIM around the text input; do not assume every space is accidental, since spaces inside a name or description may be meaningful.
Frequently Asked Questions
Can MID extract text from the right side of a cell?
Yes, but MID counts from the left. To take a fixed number of characters from the end, RIGHT is simpler. If you need to locate a changing position from the end, combine LEN with other functions or use RIGHT when the required length is known.
Does MID return a number or text?
MID returns text. A result made of digits can look numeric but will not necessarily act as a number in calculations. Use VALUE to convert it when numeric treatment is needed, and avoid conversion when leading zeros are part of an identifier.
How do I extract text between two characters?
Find the opening and closing markers, then give MID the position after the first marker and the distance between the two markers. The hyphen example above shows this method. It works when both markers are present and appear in the expected order.
What if the start position is longer than the text?
MID returns an empty string if the start position is beyond the text’s length. If the start position is less than 1, Excel returns #VALUE!. If the requested character count simply extends past the end, MID returns the remaining characters.
Is MID case-sensitive?
MID selects characters by position; it does not search for text, so case sensitivity is not part of the extraction itself. If you use FIND to locate a marker first, that search is case-sensitive. Use SEARCH instead when the marker’s letter case should not matter.
Related
- Convert Excel to PowerPoint: Methods, Steps, and Tips
- Convert PowerPoint to Excel: Steps, Options, and Limits
- Access Database Import from Excel: Steps and Tips
Try it: the Formula Explainer: paste any formula and see each function and reference in plain English.
More in Formulas & functions
- How to Forecast Dates in Excel with Useful FormulasTo extend a regular date schedule, add the interval to the previous date—for example, with a date in…
- Excel SUBSTITUTE Multiple Values: Formulas and TipsTo substitute multiple values in Excel, nest the SUBSTITUTE function for each old-to-new text pair, or use a…
- COUNTIF in Excel: How It Works and ExamplesIn Excel, COUNTIF counts cells in a range that meet one condition. Its syntax is =COUNTIF(range, criteria); for…
- How to Add Double Quotes in an Excel Formula for TextTo show double quotation marks in text produced by an Excel formula, type each quote mark twice inside…
- Excel Absolute Value Formula: How ABS Works With ExamplesIn Excel, use the ABS function to return a number’s absolute value: it removes the number’s negative sign…
- Excel Consolidate Function: How to Combine DataTo use Excel’s Consolidate function, select the top-left cell for the results, then choose Data > Consolidate, select…
Sources: Function behaviour and menu paths: Microsoft Support documentation for Excel. Method & sources
Spotted an error in this guide? Let us know.