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).
- In cell B2, enter:
=MID(A2, 5, 5) - Press Enter. If A2 contains “EMP-12345-DEPT”, the result is “12345”.
- 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_numis less than 1 ornum_charsis 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
LENto avoid hardcoding lengths that might change. - Combine with
TRIMto 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
FINDorSEARCH(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
- Excel Business Template for Profit and Loss
- Excel Business Template for Cash Flow: How to Build One
- Excel Bullet Journal Template: Setup & Free Options
Try it: the Formula Explainer: paste any formula and see each function and reference in plain English.
More in Formulas & functions
- Excel Conditional Formatting IF False: Formula & FixesThe direct answer: Excel’s conditional formatting does not have a built-in “IF false” rule, but you create one…
- Excel SUBTOTAL Formula With Filters: How It WorksTo total only the rows currently visible after filtering, use Excel’s SUBTOTAL function: if your amounts are in…
- Excel Formula for Mileage ReimbursementThe simplest Excel formula for mileage reimbursement is =B2*C2 when column B holds miles driven and column C…
- Upper Quartile Formula in Excel: How to Find Q3 CorrectlyIn Excel, use =QUARTILE.INC(A2:A20,3) to find the upper quartile, or third quartile (Q3), of the numbers in A2:A20.…
- Tableau IF Function: Syntax and Practical ExamplesIn Tableau, IF is a conditional expression that returns a value when a condition is true, with optional…
- Sigma Code in Excel Solver: Build the Formula and SolveIf by “sigma code” you mean the Greek capital sigma, Σ, used to show a sum, Excel Solver…
Sources: Function behaviour and menu paths: Microsoft Support documentation for Excel. Method & sources
Spotted an error in this guide? Let us know.