Text

MID Function in Excel

Returns a specific number of characters from a text string, starting at a specified position.

Syntax

  • =MID(text, start_num, num_chars)

Arguments

  • text (required): The text string
  • start_num (required): Position of first character to extract
  • num_chars (required): Number of characters to extract

Examples

  • =MID("Hello World", 7, 5) - Extract 'World' from middle - Result: World
  • =MID(A1, 5, 3) - Extract 3 characters starting at position 5 - Result: 3 characters

Excel MID function documentation

  • MID returns a substring starting at start_num for num_chars characters from any position inside text.
  • Syntax: =MID(text, start_num, num_chars). Positions are 1-based (first character is 1).
  • Essential when the segment is neither at the left nor right edge — invoice segments, embedded codes.
  • Related: [LEFT](/functions/left/), [RIGHT](/functions/right/), [REPLACE](/functions/replace/).

MID syntax and arguments

  • start_num: position of first character to extract — 1 is the first character.
  • num_chars: how many characters to return from start_num.
  • If start_num exceeds text length, MID returns empty text "".
  • If num_chars exceeds remaining length, MID returns all characters from start_num to end.

Step-by-step: parse fixed-width export

  • Step 1 — Legacy file column A has 20-char codes: positions 1–5 region, 6–10 product.
  • Step 2 — Region: =MID(A2, 1, 5).
  • Step 3 — Product: =MID(A2, 6, 5).
  • Step 4 — Validate with LEN(A2)=20 helper column before trusting splits.
  • Step 5 — For delimiter files prefer [TEXTSPLIT](/functions/textsplit/) in M365 over fixed MID.

MID vs LEFT/RIGHT vs REPLACE

  • MID — extract read-only segment from middle.
  • REPLACE — overwrite characters at a position with new text.
  • LEFT/RIGHT — special cases when segment touches an edge.
  • Compare: [LEFT vs MID vs RIGHT](/compare/left-vs-mid-vs-right/).

Worked examples to copy

  • Characters 3–7: =MID(A2, 3, 5).
  • After first space: =MID(A2, FIND(" ", A2)+1, 20) for remainder of string.
  • ISO date month: =MID(A2, 6, 2) from "2024-06-15" text.
  • Single char at position 10: =MID(A2, 10, 1).

People also ask

  • MID start_num 1-based? — Yes. First character is position 1, not 0.
  • MID vs SUBSTRING? — Excel uses MID; some languages call it SUBSTRING.
  • MID returns #VALUE!? — start_num or num_chars invalid (negative or zero length in some cases).
  • MID with FIND? — Common pattern: =MID(A2, FIND("-",A2)+1, 10).

Common errors

  • #VALUE! if start_num < 1
  • Returns empty if start_num > text length

Use cases

  • Parsing structured text
  • Extracting codes
  • Data cleaning

Frequently asked questions

  • How does MID positioning work? MID uses 1-based positioning. =MID("ABCDE", 2, 3) starts at position 2 (B) and extracts 3 characters: "BCD". Position 1 is the first character.
  • How do I extract text between two characters? Use FIND to locate positions: =MID(A1, FIND("(",A1)+1, FIND(")",A1)-FIND("(",A1)-1) extracts text between parentheses.
  • Can MID extract from the end of text? MID counts from the start. To extract from end, calculate start position: =MID(A1, LEN(A1)-4, 3) gets 3 characters starting 4 from end.
  • What if num_chars exceeds remaining text? MID returns whatever characters remain. =MID("ABC", 2, 10) returns "BC" (only 2 characters available from position 2).
  • How do I extract the Nth word from text? Complex but possible: use TRIM, SUBSTITUTE, and MID. Or use TEXTSPLIT in Excel 365: =INDEX(TEXTSPLIT(A1," "), N) for the Nth word.

Editorial review

  • Reviewed by Excel.Directory Editorial Team. Updated May 2026.

When to use MID

  • Parsing structured text — common Text scenario for MID.
  • Extracting codes — common Text scenario for MID.
  • Data cleaning — common Text scenario for MID.

MID in the Text category

  • Browse all Text functions at /categories/text/ for related formulas.
  • MID syntax: =MID(text, start_num, num_chars)
  • text (required): The text string
  • start_num (required): Position of first character to extract
  • num_chars (required): Number of characters to extract
  • Confirm MID arguments match the syntax shown above before filling down.
  • Lock table and range references with $ when copying formulas across rows or sheets.

Formula checklist before you copy down

  • Confirm MID arguments match the syntax shown above before filling down.
  • Lock table and range references with $ when copying formulas across rows or sheets.
  • If results look wrong, check for text stored as numbers and invisible spaces with TRIM.
  • Spot-check three known input rows manually against expected output.

Related Excel functions

  • LEFT (/functions/left/): Returns the specified number of characters from the start of a text string.
  • RIGHT (/functions/right/): Returns the specified number of characters from the end of a text string.
  • FIND (/functions/find/): Finds one text string within another and returns the starting position (case-sensitive).
  • LEN (/functions/len/): Returns the number of characters in a text string.
  • SUBSTITUTE (/functions/substitute/): Replaces occurrences of old text with new text in a string.

Comparisons involving this function

  • LEFT vs MID vs RIGHT (/compare/left-vs-mid-vs-right/): Use LEFT, MID, and RIGHT for fixed-position extraction. Use TEXTBEFORE, TEXTAFTER, and TEXTSPLIT in modern Excel when values are separated by a delimiter.

Errors to watch for

  • #VALUE! if start_num < 1 — review causes on linked error pages in the directory.
  • Returns empty if start_num > text length — review causes on linked error pages in the directory.

Copy-paste audit workflow

  • Enter MID on three test rows with known expected output documented on a QA tab.
  • Fill down only after absolute references are locked on lookup tables and rate tables.
  • Compare against manual calculation or a calculator for financial and statistical functions.
  • Search this directory for comparison guides when choosing between similar functions in the same category.

MID worked examples to copy

  • =MID("Hello World", 7, 5) — Extract 'World' from middle. Expected result: World.
  • =MID(A1, 5, 3) — Extract 3 characters starting at position 5. Expected result: 3 characters.

MID reference summary for crawlers and offline review

  • MID belongs to the Text category in Excel. Returns a specific number of characters from a text string, starting at a specified position.
  • Full syntax: =MID(text, start_num, num_chars). Open /functions/mid/ for parameters, FAQs, and related pages.
  • Common mistakes: #VALUE! if start_num < 1; Returns empty if start_num > text length
  • Pair this function with comparison guides when another Excel formula might fit the same task better.
  • Review fix-excel-formula-errors when unexpected errors appear after upgrading Excel or sharing across locales.