MID Function in Excel and Google Sheets
Purpose
Extract a specified number of characters from a text string starting at a given position
Return value
A text string containing num_chars characters from text beginning at start_num
MID is the text extraction function that handles everything LEFT and RIGHT cannot reach -- characters in the middle of a string at any position you specify. Extract the year from a date string embedded in a filename. Pull the account number from a structured reference code where the account starts at position 5. Get the middle segment of a hyphen-delimited identifier. Any time the meaningful part of a text value starts somewhere other than the very beginning or very end, MID is the function for the job. With a fixed start position it is a simple three-argument formula. Combined with FIND to locate a delimiter dynamically, it becomes a pattern that adapts to every row regardless of varying lengths before the target segment. MID is the third member of the LEFT, RIGHT, MID trio that together cover every fixed-position text extraction scenario, and understanding how all three interact gives you a complete text-parsing toolkit.
Syntax
✓ Excel 2003+
✓ Google Sheets
= Same syntax
=MID(text, start_num, num_chars)
Arguments
| Argument | Required | Description |
|---|---|---|
| text | Required | The text string to extract from. Can be a cell reference, literal text in quotes, or a formula result. |
| start_num | Required | The position of the first character to extract. Position 1 is the leftmost character. Must be at least 1 -- a value of 0 or negative causes a #VALUE! error. |
| num_chars | Required | The number of characters to return. Must be zero or positive. If larger than the remaining characters from start_num to the end, MID returns everything from start_num to the end without error. |
How it works
MID counts character positions from the left of the string starting at 1. The character at start_num is the first included in the result, and num_chars determines how many characters are returned from that position rightward. Spaces, hyphens, and special characters all count as one character. The result is always a text string -- wrap in VALUE if the extracted characters need to be numeric for calculations. When start_num varies by row because a delimiter shifts, use FIND to locate the delimiter and calculate the start dynamically: FIND returns the position of a character, and adding 1 moves past the delimiter to the first character you want to extract. When both the start and end of the target segment are marked by delimiters, use FIND twice -- once for each delimiter -- to calculate both start_num and num_chars. The LEFT and RIGHT functions handle fixed-position extracts from either end of the string and are simpler when the target segment is at the start or end.
Examples
1
Extract the 4-digit year from a structured product code where the year always starts at position 5.
| A | B | |
|---|---|---|
| 1 | Product Code | Year |
| 2 | PRD-2024-001 | =MID(A2,5,4) |
| 3 | PRD-2023-145 | 2023 |
| 4 | CAT-2024-012 | 2024 |
| 5 | REG-2022-088 | 2022 |
| 6 | PRD-2025-003 | 2025 |
Row 2: 2024
— PRD-2024-001 has the year starting at position 5 (P=1, R=2, D=3, -=4, 2=5). MID(A2,5,4) extracts 4 characters from position 5: 2024.
The result is the text string 2024, not the number 2024. Wrap in VALUE if you need to compare it to a numeric year: =VALUE(MID(A2,5,4)).
2
Extract the middle segment of a hyphen-delimited code dynamically, where the segment sits between the first and second hyphen.
| A | B | |
|---|---|---|
| 1 | Code | Middle Segment |
| 2 | PRD-NORTH-001 | =MID(A2,FIND("-",A2)+1,FIND("-",A2,FIND("-",A2)+1)-FIND("-",A2)-1) |
| 3 | CAT-SOUTH-012 | SOUTH |
| 4 | REG-EAST-088 | EAST |
| 5 | PRD-WEST-003 | WEST |
| 6 | CAT-CENTRAL-099 | CENTRAL |
Row 2: NORTH
— The first hyphen in PRD-NORTH-001 is at position 4. FIND+1 gives start position 5. The second hyphen is at position 10. The length is 10-4-1 = 5. MID(A2,5,5) returns NORTH.
The nested FIND(delimiter,start) syntax finds the second occurrence of a character by starting the search after the first match. This dynamic calculation works for any middle segment length -- NORTH (5) and CENTRAL (7) both extract correctly.
3
Extract the text inside square brackets from a comment field using two FIND calls to locate the bracket positions.
| A | B | |
|---|---|---|
| 1 | Comment | Tag |
| 2 | Approved [Budget] review complete | =MID(A2,FIND("[",A2)+1,FIND("]",A2)-FIND("[",A2)-1) |
| 3 | Pending [Legal] sign-off | Legal |
| 4 | Complete [Finance] verified | Finance |
| 5 | On hold [IT] dependency | IT |
| 6 | Escalated [Management] attention | Management |
Row 2: Budget
— FIND locates [ at position 10. FIND+1 gives start position 11 (B in Budget). FIND locates ] at position 16. Length = 16-10-1 = 5. MID returns Budget.
This between-delimiters pattern using two FIND calls is one of the most reusable MID patterns. Any structured comment or label field with bracket-enclosed tags, parenthetical codes, or quoted segments can be parsed with this same formula structure.
Common use cases
1. Extract a year, month, or day segment from a date stored as text in a structured format
=MID(A2,5,4)
2. Pull the middle segment of a hyphen-delimited or slash-delimited identifier code
=MID(A2,FIND("-",A2)+1,4)
3. Extract text between two delimiter characters such as brackets, parentheses, or quotes
=MID(A2,FIND("[",A2)+1,FIND("]",A2)-FIND("[",A2)-1)
4. Get a fixed-length account number that always starts at a known position in a reference string
=MID(A2,8,6)
5. Extract the domain from a URL after the protocol by finding the double slash and offsetting
=MID(A2,FIND("//",A2)+2,FIND("/",A2,FIND("//",A2)+2)-FIND("//",A2)-2)
Common errors
#VALUE! error
start_num is less than 1, or FIND inside the formula cannot locate the delimiter and returns an error that propagates through MID.
Fix: Ensure start_num is at least 1. Wrap FIND in IFERROR if the delimiter may not always be present: =IFERROR(MID(A2,FIND("-",A2)+1,4),A2).
Result is an empty string when you expected content
start_num is beyond the end of the string, or num_chars is 0. MID returns an empty string rather than an error in these cases.
Fix: Check the string length with LEN(A2) to confirm start_num is within the string. Verify num_chars is a positive integer.
Wrong characters extracted
The string has leading or trailing spaces shifting all character positions. Or the start position was calculated from a FIND result that located the wrong occurrence of the delimiter.
Fix: Use TRIM on the text argument to remove extra spaces first. Use FIND with a start position argument to skip to the correct delimiter occurrence.
Tips and variations
Use LEN as the num_chars argument to capture everything from start_num to the end
=MID(A2,FIND("-",A2)+1,LEN(A2)) extracts everything after the first hyphen regardless of how many characters remain. LEN is always an overcount for the remaining characters, so MID safely returns exactly what is left.
=MID(A2,FIND("-",A2)+1,LEN(A2))
Wrap in VALUE when the extracted segment needs to be numeric
MID always returns text. If the extracted characters are digits that need to participate in arithmetic or date functions, wrap in VALUE: =VALUE(MID(A2,5,4)) converts the text year to a number.
=VALUE(MID(A2,5,4))
Combine with FIND to make both start_num and num_chars dynamic
When both the start and end of the target segment are marked by delimiters, compute both positions with FIND and derive num_chars from their difference. This two-FIND pattern scales to any structured string and is the most general form of MID for parsing real-world data.
=MID(A2,FIND("[",A2)+1,FIND("]",A2)-FIND("[",A2)-1)
Excel vs Google Sheets
Excel vs Google Sheets
MID works identically in Excel 2003 and newer and every version of Google Sheets. Character counting from position 1, the text-only return value, and the safe overcount behaviour when num_chars exceeds remaining length are the same in both applications. Formulas copy between them without changes.
Frequently asked questions
MID extracts a specified number of characters from the middle of a text string, starting at a position you define. =MID("Hello World",7,5) returns World -- it starts at position 7 and takes 5 characters. MID is the most flexible of the three text-extraction functions (LEFT, MID, RIGHT) because you control both the start position and the length.
Use FIND or SEARCH to locate a delimiter and calculate the start position dynamically. =MID(A2,FIND("-",A2)+1,4) starts one character after the hyphen and takes 4 characters. The +1 skips the delimiter itself. This pattern adapts to any row where the delimiter position varies.
LEFT extracts characters from the beginning of a string. RIGHT extracts from the end. MID extracts from anywhere in the middle -- you specify the exact start position and how many characters to take. Use LEFT for prefixes, RIGHT for suffixes, and MID for anything that starts at a known or calculable position inside the string.
MID returns all characters from the start position to the end of the string without error. =MID("Hello",3,100) returns llo -- the three remaining characters from position 3 -- because MID stops at the end of the string. This makes it safe to use a large num_chars as an overcount when you want everything from a given position to the end.
Yes. Use FIND twice -- once to locate the start delimiter and once to locate the end delimiter -- then calculate the length from the difference: =MID(A2,FIND("[",A2)+1,FIND("]",A2)-FIND("[",A2)-1) extracts the text between square brackets. The first FIND+1 gives the start position inside the brackets, and the difference of the two FIND results minus 1 gives the length.