Using MID in Excel: Extract Text by Position and Length

Formulas & functions · Updated November 20, 2026

Use Excel’s MID function to return a chosen number of characters from a text value, starting at a position you specify. If A2 contains INV-2026-00481, the formula =MID(A2,5,4) returns 2026: it starts at character 5 and extracts four characters.

What MID Does in Excel

MID is a text function for extracting characters from the middle of a value. It is useful when a code, label, or other text has a part you need to isolate by its position—for example, a year embedded in an inventory number or a department code in a fixed-width record. The result is text, even when the extracted characters look like a number.

The function uses three arguments: the text to read, the position of the first character to return, and the number of characters to return. Positions start at 1, not 0. For a value such as ABC123, A is position 1, B is position 2, and so on. MID does not change the source cell; it creates a result in the cell containing the formula.

Unlike a function that extracts from the beginning or end, MID lets you specify both the starting point and the length. That makes it suited to records with predictable layouts. If the location varies, you can combine it with a search function, as shown below.

Extract a Fixed Part of a Value

With inventory codes in A2:A100, and the year occupying four characters starting at position 5, enter this formula in B2:

=MID(A2,5,4)

For the sample code INV-2026-00481 in A2, the result is 2026. Copy the formula down column B to extract the corresponding year from each row. Excel adjusts the relative cell reference from A2 to A3, A4, and so on when you fill down.

This approach works well only if the data follows the same layout. If some codes have an extra prefix or a missing character, position 5 may no longer be the start of the year. Before applying a fixed-position formula to a large list, check several source values, including the shortest and longest entries and any unusual cases.

For another example, with product codes in C2:C50 and a two-character category always beginning at character 4, place this formula in D2:

=MID(C2,4,2)

If C2 contains ABXQ917, the result is Q9. The extracted text keeps any leading zeroes: if the selected characters are 07, MID returns the two-character text 07 rather than converting it to the number 7.

Find the Starting Position from a Delimiter

When a piece of text follows a known delimiter but can move to different positions, combine MID with FIND. FIND returns the position of a specified character or string. With invoice codes in A2:A100 where a hyphen comes immediately before a four-character year, use:

=MID(A2,FIND("-",A2)+1,4)

With INV-2026-00481 in A2, FIND locates the first hyphen at position 4. Adding 1 starts MID at position 5, and the formula returns 2026. This is more flexible than hard-coding 5 when a prefix before that hyphen can have different lengths.

FIND is case-sensitive, which matters when the delimiter is a letter. SEARCH is its case-insensitive counterpart. For punctuation such as a hyphen, the distinction usually makes no difference. If a delimiter appears more than once, this formula uses the first occurrence. To extract text after a later delimiter, the formula needs to locate that later occurrence rather than simply using the first match.

MID still needs a character count. In the example above, four is appropriate because the year always has four characters. If the part after the delimiter has a variable length, consider a delimiter-focused approach such as TEXTAFTER in Microsoft 365 or Excel 2024, rather than making MID’s length argument guess at the intended result.

Use MID with Other Text Functions

MID is often one part of a larger formula. LEN counts the characters in a text value, while FIND or SEARCH can locate a marker. For example, with fixed-length IDs in A2:A100 where the final four characters are the segment you need, enter this in B2:

=MID(A2,LEN(A2)-3,4)

If A2 contains INV-2026-00481, LEN returns 14. The starting position is 14 minus 3, or 11, so the formula returns 0481. This formula extracts the last four characters even if the text before them varies in length. If the required segment is not always four characters, this particular formula is not suitable.

LEFT and RIGHT are simpler when you always need characters from the beginning or end. Use LEFT for a prefix and RIGHT for a suffix; use MID when the section begins at a position within the text or when another function calculates that position. Excel’s TEXTBEFORE and TEXTAFTER functions can be clearer when the goal is to split text around a delimiter. Those functions require Microsoft 365 or Excel 2024, whereas MID is available in older Excel versions as well.

For a large, repeatable cleanup task, Power Query can also split a column by a delimiter through the Data tab’s data-import and transformation tools. That may be easier to maintain than a long formula when you regularly receive files with the same structure. MID remains convenient for a quick worksheet calculation or a formula-based result that updates as its source cell changes.

Understand Character Positions and Results

MID counts characters from left to right, including spaces and punctuation. In the value A-B 123, the hyphen, the space, and each digit occupy positions just like letters. A misplaced space in the source can therefore shift every later position. When a formula appears to return the wrong portion, count the characters in the actual cell rather than relying on how the value looks at a glance.

Excel’s text functions return text. If MID extracts 2026 from a code, the result may look numeric but is text for many spreadsheet operations. If you need to calculate with the extracted value, convert it deliberately—for example, with VALUE in a separate formula—and first confirm that every extracted result is a valid number. For identifiers, postal codes, and account codes, retaining text is usually preferable because leading zeroes can be meaningful.

When the source cell contains a number or date rather than text, the value may not be represented as it appears with cell formatting. A date, for instance, is stored as a serial number, while its display may show a calendar date. MID works on the value as text, not on the visual date format. If you need to extract from the displayed date, first produce text in the required format with TEXT, then extract from that text.

Modern Excel counts many Unicode characters according to its text-function character rules, but visually combined characters can still consist of more than one underlying character. For ordinary English letters, digits, and punctuation this is rarely an issue. If a dataset contains unusual symbols or characters from multiple writing systems, test the formula on representative values before relying on fixed positions.

Handle Errors and Short Inputs

MID returns an error when the starting position is less than 1 or when the requested character count is negative. A count of zero returns an empty result, and a starting position beyond the end of the text also produces an empty result. If the source is shorter than expected, the function can return the characters that are available rather than necessarily signaling that the whole requested segment was not present.

That last behavior can conceal incomplete data. With IDs in A2:A100 where the four-character section is supposed to occupy positions 5 through 8, use a length check if short entries should produce a blank instead of a partial segment. Enter this in B2:

=IF(LEN(A2)>=8,MID(A2,5,4),"")

If A2 contains INV-2026-00481, the result is 2026. If it contains only ABC12, the result is blank because the text has fewer than eight characters. The formula checks that the input reaches the end of the expected section before extracting it. It does not validate that the characters are the right kind of data, so a separate check may be needed if the year must contain digits.

Wrapping a formula in IFERROR can hide errors, but it can also hide a broken reference or an unexpected source-data problem. Use it when a blank or substitute result is genuinely appropriate, not as a substitute for understanding why a formula failed. For delimiter-based formulas, check that the delimiter is present in every relevant row; FIND returns an error if it cannot find the requested text.

Common Misconceptions and Practical Checks

  • MID is not a word extractor. It counts characters, not words. A space counts as a character, and the function does not recognize a word boundary unless you build one into the formula.
  • Positions begin at 1. A starting position of 0 is invalid; the first character is position 1.
  • The result is text. Digits returned from an ID do not automatically become a numeric value, and preserving that text can protect leading zeroes.
  • MID does not use the cell’s visual width or formatting. It reads the underlying value as text, which can differ from a date or number’s displayed appearance.
  • Hard-coded positions assume consistent data. If prefixes vary, use a function such as FIND to locate a stable marker, or use a delimiter-based method.

If results look shifted, inspect source cells for leading spaces, inconsistent separators, or characters copied from another system. You can use LEN to compare the character count of otherwise similar entries. If the data contains a nonbreaking space or another invisible character, ordinary spaces may not match it, so the issue may require cleaning the source before extracting.

Frequently Asked Questions

What is the MID formula in Excel? MID returns a specified number of characters from a text value, starting at a position you provide. Its three arguments are the source text, the starting character number, and the number of characters to return. For example, with INV-2026-00481 in A2, =MID(A2,5,4) returns 2026.

How do I use MID to extract text between two characters? MID needs a starting position and a length. You can use FIND to locate a delimiter and calculate the start, but you also need to calculate how many characters lie between the delimiters. If your Excel version supports TEXTBEFORE and TEXTAFTER, those functions can be more readable for delimiter-based extraction.

Why does MID return numbers as text? MID is a text function, so even digit characters come back as text. This helps preserve identifiers such as 007. If you need a numeric result for arithmetic, convert it with VALUE after confirming that the extracted text contains a valid number.

Can MID extract characters from the right side of a cell? Yes. Calculate the starting position from the text length, as in =MID(A2,LEN(A2)-3,4), which returns the final four characters. For a straightforward suffix, RIGHT is usually shorter and easier to read.

What is the difference between MID and FIND? MID returns characters from a text value. FIND returns the position where specified text occurs; it does not extract that text by itself. Combining FIND with MID lets a formula start at a position determined by a marker rather than a fixed character number.

Related

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.