What does the MID function do in Excel?
MID extracts a text fragment from the middle of a text string.
The function needs to know at which position the fragment to extract begins and how many characters to select.
If the number of characters to select exceeds what's available, only the available characters will be selected.
MID syntax
=MID(text, start_num, num_chars)| A | B | C | D | |
|---|---|---|---|---|
| 1 | Text | start_num | num_chars | MID |
| 2 | SKU-2026-RED | 5 | 4 | 2026=MID(A2,B2,C2) |
| 3 | SKU-2026-RED | 10 | 3 | RED=MID(A3,B3,C3) |
| 4 | SKU-2026-RED | 10 | 50 | RED=MID(A4,B4,C4) |
| 5 | SKU-2026-RED | 20 | 3 | =MID(A5,B5,C5) |
If num_chars runs past the end, MID returns what’s left. If start_num is past the end, the result is an empty string, not an error.
MID: result and input format
MID usage examples
Example 1
This example shows how to extract a variable-length element that is located inside text.
In this case, it's the middle name within a full name.
The extracted name text is always located between the first and second space.
| A | B | |
|---|---|---|
| 1 | Full name | Middle name |
| 2 | John Fitzgerald Kennedy | Fitzgerald=MID(A2,FIND(" ",A2)+1,FIND(" ",A2,FIND(" ",A2)+1)-FIND(" ",A2)-1) |
| 3 | Mary Ann Smith | Ann=MID(A3,FIND(" ",A3)+1,FIND(" ",A3,FIND(" ",A3)+1)-FIND(" ",A3)-1) |
| 4 | Anna Louise Maria Berg | Louise=MID(A4,FIND(" ",A4)+1,FIND(" ",A4,FIND(" ",A4)+1)-FIND(" ",A4)-1) |
FIND(" ",A2)+1 is where the middle name starts; the second FIND looks for the next space after it. Their difference minus 1 is the length. In Excel 365 the same is =TEXTBEFORE(TEXTAFTER(A2," ")," ").
Example 2
The MID function is used to extract from a string the first word containing a specific fragment.
The SUBSTITUTE function in combination with REPT creates a large number of spaces between words to guarantee extracting only one word with extra spaces on the right and left.
The TRIM function then removes them.
| A | B | |
|---|---|---|
| 1 | Text | ups |
| 2 | order 1452 shipped via ups-ground today | ups-ground=TRIM(MID(SUBSTITUTE(A2," ",REPT(" ",100)),MAX(1,SEARCH($B$1,SUBSTITUTE(A2," ",REPT(" ",100)))-50),100)) |
| 3 | ups-express, 2 boxes | ups-express,=TRIM(MID(SUBSTITUTE(A3," ",REPT(" ",100)),MAX(1,SEARCH($B$1,SUBSTITUTE(A3," ",REPT(" ",100)))-50),100)) |
| 4 | delivered by dhl | #VALUE!=TRIM(MID(SUBSTITUTE(A4," ",REPT(" ",100)),MAX(1,SEARCH($B$1,SUBSTITUTE(A4," ",REPT(" ",100)))-50),100)) |
Every space becomes 100 spaces, so any 100-character window around the fragment holds only one word; TRIM removes the padding. MAX(1, …) keeps the start from going below 1 when the word is at the very beginning. Commas stay attached to the word (“ups-express,”). No match → #VALUE!; wrap in IFERROR if needed.
See also:
Like the article? Help its author! Buy !SEMTools, it has lots of useful instruments to process text data.