Excel Formula for MID: Extract Text Like a Pro

Formulas & functions · Updated November 22, 2026

The Excel formula for extracting text from the middle of a string is =MID(text, start_num, num_chars). For example, =MID("Excel is powerful", 7, 2) returns “is”. This function works in Excel for Microsoft 365, Excel 2021, 2019, 2016, and earlier versions, as well as Excel for the web and Mac.

What Does the MID Function Do?

MID is one of Excel’s text functions. It pulls out a specific number of characters from a text string, starting at a position you choose. Unlike LEFT (which grabs from the beginning) or RIGHT (which grabs from the end), MID lets you target any part in between. This is essential when you have structured data like product codes, IDs, or names where the useful part sits in the middle.

The function takes three arguments:

  • text – the original string (a cell reference or text in quotes).
  • start_num – the position of the first character to extract (1 is the first character).
  • num_chars – how many characters to extract.

If start_num is greater than the length of the text, MID returns an empty string (“”). If num_chars goes beyond the end, MID simply returns everything from start_num to the end.

How to Use MID: Step-by-Step with Examples

Suppose you have a list of employee codes in column A, formatted like “EMP-12345-DEPT”. You want to extract the numeric ID (characters 5 through 9).

  1. In cell B2, enter: =MID(A2, 5, 5)
  2. Press Enter. If A2 contains “EMP-12345-DEPT”, the result is “12345”.
  3. Drag the fill handle down to apply to other rows.

Another example: extracting the month from a date stored as text. If A1 has “2025-03-14”, =MID(A1, 6, 2) gives “03”.

For dynamic extraction, you can nest other functions. For instance, to extract everything between a hyphen and a space, you might use =MID(A1, FIND("-", A1)+1, FIND(" ", A1)-FIND("-", A1)-1). This finds the positions of the delimiters and calculates length accordingly.

Choosing Between MID, LEFT, and RIGHT

These three functions are siblings. Choose based on where the text you need sits:

  • LEFT – from the start: =LEFT(text, num_chars)
  • RIGHT – from the end: =RIGHT(text, num_chars)
  • MID – from any position: =MID(text, start_num, num_chars)

Use MID when the position is known but not at the edges. For example, extracting area codes from phone numbers (characters 1-3) is LEFT, but extracting the exchange (characters 4-6) is MID. If the length varies, combine with FIND or SEARCH to locate delimiters.

Common Errors and How to Fix Them

Several issues can trip you up:

  • #VALUE! error – occurs if start_num is less than 1 or num_chars is negative. Ensure both are positive numbers.
  • Spaces or invisible characters – MID extracts exactly what’s there, including spaces. Use =TRIM(MID(...)) to remove extra spaces.
  • Incorrect position due to leading zeros – If your text has leading zeros, MID will include them. That’s usually desired, but if not, use =VALUE(MID(...)) to convert to a number.
  • Using 0 as start_num – Excel treats 0 as invalid; always start at 1.

If you get unexpected results, double-check the actual characters in your string. Use =LEN(text) to see the length and =CODE(MID(text, position, 1)) to see the character code of a specific character—this helps spot non-printing characters.

Advanced MID: Nested Functions and Dynamic Extraction

MID becomes powerful when combined with other functions:

  • FIND or SEARCH – locate a delimiter: =MID(A1, FIND("-", A1)+1, 5)
  • LEN – extract to the end: =MID(A1, 4, LEN(A1)-3) (from character 4 to end)
  • IFERROR – handle missing delimiters: =IFERROR(MID(A1, FIND("-", A1)+1, 5), "Not found")

For a real-world case, imagine you have full names in column A (like “John Smith”). To get the first name, you could use =LEFT(A1, FIND(" ", A1)-1). To get the last name, use =MID(A1, FIND(" ", A1)+1, LEN(A1)) – but that’s simpler with RIGHT. A more complex example: extracting the domain from an email address. If A1 has “user@example.com”, =MID(A1, FIND("@", A1)+1, LEN(A1)-FIND("@", A1)) returns “example.com”.

Note that MID works with arrays too. In Excel 365, you can use =MID(A1:A5, 2, 3) to extract from a range, and it will spill results down.

MID in Google Sheets vs. Excel

The MID function works identically in Google Sheets and Excel. The syntax and behavior are the same. The only difference is in how you might use it with other functions that have slightly different names (e.g., SPLIT in Sheets vs. TEXTSPLIT in Excel). For example, in Sheets you could use =MID(A1, 2, 3) exactly as in Excel. If you’re migrating between the two, your MID formulas will carry over without changes.

Frequently Asked Questions

Q: Can MID extract numbers and then use them in calculations?
Yes, but the result is text. To use it in math, wrap it in =VALUE(MID(...)) or multiply by 1. For example, =VALUE(MID(A1, 5, 3)) converts the extracted digits to a number.

Q: What’s the difference between MID and MIDB?
MIDB is for languages that use double-byte character sets (like Japanese or Chinese). It counts each byte as a character. For most users, MID is sufficient. Use MIDB only if you’re working with DBCS languages and need byte-based extraction.

Q: How do I extract text between two specific characters?
Combine MID with FIND twice. Example: to get text between parentheses in A1, use =MID(A1, FIND("(", A1)+1, FIND(")", A1)-FIND("(", A1)-1). This finds the opening and closing positions and calculates the length.

Q: Can I use MID with wildcards?
No, MID doesn’t support wildcards. For pattern-based extraction, you’d need to use functions like FILTER or TEXTBEFORE/TEXTAFTER (in Excel 365) or write a VBA macro.

Q: Does MID work with numbers?
Yes, but Excel will treat the number as text. If you have a number in a cell, MID will convert it to text. For example, =MID(12345, 2, 2) returns “23”. To get a number back, wrap with VALUE.

Q: How do I extract the last word in a string?
Use a combination: =TRIM(RIGHT(SUBSTITUTE(A1, " ", REPT(" ", 100)), 100)). This replaces spaces with many spaces, then takes the rightmost part and trims it. It’s a common trick that works in both Excel and Sheets.

Practical Tips for Using MID Effectively

  • Always test with a small sample to verify positions.
  • Use LEN to avoid hardcoding lengths that might change.
  • Combine with TRIM to clean up extra spaces.
  • Remember that MID is case-sensitive? No, it’s not – it extracts exactly what’s there, preserving case.
  • For dynamic start positions, use FIND or SEARCH (SEARCH is case-insensitive).

MID is a fundamental tool for text manipulation. Once you master it, you’ll find dozens of uses, from cleaning data to parsing complex strings. Practice with your own data to get comfortable.

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.