Spreadsheet Practice

MID function

Quick answer

MID returns characters from the middle of a text string, starting at a position you choose.

Syntax
=MID(text, start_num, num_chars)
Most common use
=MID(A2,5,4) returns 2026 (The year: four characters starting at position 5)

Use it for fixed-format codes where the piece you want sits in the same place every time, like the year in INV-2026-0042.

Available in
Every current version
Category
Text functions
Returns
Text. If start_num is past the end of the text, an empty string.
Google Sheets
Works the same in Google Sheets.

Syntax

=MID(text, start_num, num_chars)

ArgumentRequired?What it is
textRequiredThe text or cell to read from.
start_numRequiredPosition of the first character to return. The first character in the text is 1.
num_charsRequiredHow many characters to return.

Examples

Each result below is calculated from the sample sheet by our formula engine, the same one that grades the practice.

CellFormulaResultWhat it shows
D2=MID(A2,5,4)2026The year: four characters starting at position 5
D3=MID(B3,3,6)888BB2Characters 3 through 8 of a tracking number
D4=MID(C4,FIND("-",C4)+1,3)GRNThe color code, starting just after the first dash

Try it live

This is the sample sheet. Change any number or text, or edit a formula, and the results update.

Show the sample data as a table
InvoiceTracking numberProduct codeResult
INV-2026-00421Z999AA10123456784WDG-BLU-L2026
INV-2025-11871Z888BB20234567891GDT-RED-M888BB2
INV-2026-03101Z777CC30345678912GZM-GRN-SGRN

Practice MID

From lesson 18, LEFT, RIGHT, MID & LEN. Graded instantly, then re-checked on changed data.

Year from the middle

In D2, extract the four-digit year from the middle of the code. Fill down to D6.

Common mistakes

What you seeWhy, and how to fix it
Off by onestart_num counts from 1, and the delimiter takes a position too: in NY-2026 the year starts at 4, not 3. Use FIND("-",A2)+1 to start right after a dash.
The result won't add upMID returns text, so MID(A2,5,4) gives "2026", which SUM skips and lookups treat as text. Add 0 or wrap it in VALUE.
Pieces vary in lengthA fixed num_chars cuts variable-length parts short. Locate both delimiters with FIND, or use TEXTBEFORE and TEXTAFTER.

Tips

  • Ask for more characters than you need and MID stops at the end of the text: =MID(A2,5,99) returns everything from position 5 on.

Questions

How do I extract text between two characters in Excel?

In Excel 2024 or Microsoft 365, nest the split functions: =TEXTBEFORE(TEXTAFTER(A2,"-"),"-") returns the text between the first two dashes. In older versions, use MID with FIND to calculate the start and length.

Does Excel have a SUBSTRING function?

No. MID is the equivalent: give it the text, a start position and a length.

Lessons that use MID

Answers that use MID

In other languages

MID is EXTRAE in Spanish Excel and TEIL in German Excel. Each page has the syntax with semicolons, the examples and the exercise in that language. For other functions and languages, use the Function Translator.

Updated Checked by running: every formula on this page was run by our formula engine on .