MID - Read characters from a known position
Extract an embedded product family or serial segment while counting positions from one rather than zero.
By the end, you can
- Choose a 1-based start position.
- Separate the starting position from the extraction length.
- Recognize the limitations of fixed-format parsing.
Before you start: Know LEFT/RIGHT and how separators occupy character positions.
In this lesson
A field inside an identifier
MID extracts text beginning at a specified character position. A product identifier may place a family code after a two-letter warehouse prefix and a hyphen. Unlike LEFT or RIGHT, you choose both where the extract begins and how long it is. This works when the source format is consistent.
Syntax and arguments
=MID(text, start_num, num_chars)All three arguments are required. Positions start at 1; num_chars is a length, not an ending position.
| Argument | Purpose | Example |
|---|---|---|
textRequired | The source text or cell. | A2 |
start_numRequired | The 1-based position of the first character to extract. | 4 |
num_charsRequired | How many characters to extract from that start. | 3 |
Extract an embedded product family
PEN| A | B | |
|---|---|---|
| 1 | Product ID | Family |
| 2 | DL-PEN-001 | PEN |
| 3 | MU-PAD-017 | PAD |
| 4 | BL-INK-204 | INK |
=MID(A2,4,3)Start at P in position 4 of DL-PEN-001 and take P, E, N.
- Count the prefix letters and the separator: two plus one characters.
- Start at position 4, the first family letter.
- Take three characters, which covers positions 4, 5, and 6.
Extract another segment or the remaining text
=MID(A2,8,3)Position 8 starts the serial 001 after the second hyphen. The result is text, preserving zeroes.
=MID(A2,4,99)An overlong requested length returns only the available remainder.
=MID(A3,4,3)MU-PAD-017 has the same layout and returns PAD.
Boundaries and compatibility
- A start beyond the text length returns empty text. A request extending past the end returns the available characters.
- start_num below 1 or num_chars below zero causes #VALUE!. A zero length returns empty text.
- Spaces, hyphens, and other separators all occupy positions. MID does not locate a delimiter automatically.
- MID works in older and current Excel; Unicode surrogate-pair positions can vary with workbook Compatibility Version. These examples use ASCII codes.
Common mistakes and Practice scope
The linked task uses fixed-width product families with a different warehouse prefix. It exercises the supported ASCII MID behavior, not every desktop Unicode case.
Check your understanding
Choose an answer for instant feedback. You can try again; answers stay only on this page.