Ravindra BagaleCourses & study guides Track your progress

Excel MID Function

By · Last updated:

Chala mitrano — mID is the middle scissors. Tell Excel where to start and how many letters to keep.

Fictional sample data for learning. Invoice numbers with city codes in the middle (Pune / Austin). Mixed ₹ and $.

Purpose

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

Return value

The middle slice as text.

Syntax

MID(text, start_num, num_chars)

Arguments

ArgumentRequired?Description
textRequiredThe full text.
start_numRequired1-based start position (1 = first character).
num_charsRequiredHow many characters to take from that start.

Version

All Excel versions. LibreOffice Calc compatible.

How to use — Why → What → How

Why: Invoice INV-PUNE-7781 hides a city code in the middle. Ops in Austin use INV-AUS-2201. You need that city token without rebuilding the ID by hand. MID rewards careful layout audits. Print ten sample IDs, mark start positions with a pen, then lock start_num. When layouts drift by region, FIND must drive the start — never a lucky hard-coded 5.

What: MID starts at start_num and returns num_chars characters. Positions count from 1 at the left. Read every argument in the table once before you type; each one changes the answer on purpose.

How — numbered steps:

  1. Type =MID(
  2. Select the text cell.
  3. Comma, start position (example 5).
  4. Comma, length (example 4).
  5. Enter and fill down.
  6. Verify with LEN that every ID shares the same layout before you trust a fixed start.
  7. When the start moves, nest FIND to compute start_num.
  8. Keep results as text unless VALUE is required.

Ravindra Bagale's Tip

Friends, start_num is 1-based: the first character is 1, not 0. Programmers often slip here in interviews.

Ravindra Bagale's Tip

Friends, sleep on MID and rebuild example 1 tomorrow without looking. That five-minute test beats rereading the whole page.

Ravindra Bagale's Tip

Friends, after you finish MID, open yesterday’s real workbook and find one place this function would have saved ten minutes. That habit turns lessons into salary skills.

Download practice file: practice-mid.xlsx (includes sheet About this data, practice sheets, and Answers).

Worked examples

1) City code inside invoice

Pune billing.

InvoiceAmount (₹)
INV-PUNE-778124500
INV-NASH-778218200
INV-PUNE-900131000
Formula: =MID(A2, 5, 4)

Result: PUNE

What it means: Characters 5–8 are PUNE.

2) Digits in the middle

Austin SKU block.

SKU
ABCD1234XYZ
ABCD9999XYZ
Formula: =MID(A2, 5, 4)

Result: 1234

What it means: Middle four digits as text.

3) Single character

Pick one letter.

Code
QX9-HELLO
Formula: =MID(A2, 3, 1)

Result: 9

What it means: Third character only.

4) Start past end

Start too far.

Code
ABC
Formula: =MID(A2, 10, 2)

Result:

What it means: Empty text — not an error — when start is beyond the length.

5) With FIND for flexible start

After the first dash.

Invoice
INV-PUNE-7781
INV-NASH-12
Formula: =MID(A2, FIND("-",A2)+1, 4)

Result: PUNE

What it means: FIND locates the dash; MID takes the next 4.

6) Name middle initial zone

Compact codes.

Token
R-K-BAGALE
A-S-SHAH
Formula: =MID(A2, 3, 1)

Result: K

What it means: Middle initial K.

About this data. Small demo rows for learning. Real sheets are longer; the same formula pattern scales when columns are clean.

Common errors and fixes

#VALUE! — start_num < 1 or num_chars < 0. Fix: Use start_num ≥ 1 and num_chars ≥ 0.
Wrong city — IDs are not aligned. Fix: Do not hard-code start until every ID shares the pattern — use FIND.

Notes and tips

  • MID returns text.
  • Empty string when start is past LEN(text).
  • Pair with FIND when the start moves.
  • Fixed-layout IDs are common in ERP exports from Indian manufacturers and US 3PLs. MID bridges LEFT/RIGHT and FIND-based parsing. Always sample ten IDs before locking start_num in a shared workbook.
  • MID vs TEXTBEFORE/TEXTAFTER: delimiter functions need Microsoft 365/2021 and read clearer when a dash marks the cut. MID works on every Excel version.
  • Fictional sample data only — practice amounts and names are made for learning.
  • After the 10 tasks, re-read the errors table once.
  • Say the business question in plain words before you type equals.
  • Keep a scrap sheet for experiments so you do not overwrite class files.
  • Fill formulas down only after the first row looks correct.
  • When two answers look possible, write both on paper then test in Excel.
  • MID rewards careful layout audits. Print ten sample IDs, mark start positions with a pen, then lock start_num. When layouts drift by region, FIND must drive the start — never a lucky hard-coded 5.
  • Revisit the MID errors table after the quiz — most classroom mistakes are listed there.
  • Build one brand-new example on a blank sheet from memory before you close the laptop.
  • If a result looks wrong, check spaces with LEN and case with exact equals before changing the formula.
  • Share one tip from this page with a classmate in Marathi or Hindi — teaching locks the idea.
  • Keep currency symbols consistent inside a single report column even when practice mixes ₹ $ £ € across examples.
  • When Excel versions differ at home and in class, write the fallback formula under the modern one in a comment cell.
  • Print the revision card for MID and stick it near your monitor for one week of real worksheets.
  • When helping a junior, ask them to predict the result before pressing Enter — prediction builds judgement.
  • Keep a personal notebook of the one mistake you make most often with this function; review it monthly.

In real work

Fixed-layout IDs are common in ERP exports from Indian manufacturers and US 3PLs. MID bridges LEFT/RIGHT and FIND-based parsing. Always sample ten IDs before locking start_num in a shared workbook. MID rewards careful layout audits. Print ten sample IDs, mark start positions with a pen, then lock start_num. When layouts drift by region, FIND must drive the start — never a lucky hard-coded 5. Classroom drill: write three brand-new MID formulas on a blank sheet using names from your own city — if you can invent them without the page open, you own the skill. Then break one formula on purpose (wrong case, wrong start, extra space) and watch the error; recovering from that break is half of interview readiness.

How it compares

MID vs TEXTBEFORE/TEXTAFTER: delimiter functions need Microsoft 365/2021 and read clearer when a dash marks the cut. MID works on every Excel version.

Interview corner

Is start_num 0-based or 1-based?
1-based.
What if start is beyond the text?
Empty string.
How do you take text between two dashes?
Combine FIND (or TEXTBEFORE/TEXTAFTER on 365).

LEFT, RIGHT, FIND, LEN

Practice tasks (10)

  1. Task 1. Return PUNE from INV-PUNE-7781 with MID.
    Hint: start at position 5, take 4.
  2. Task 2. Fill city codes for three invoices.
    Hint: same start if layout matches.
  3. Task 3. Pull 1234 from ABCD1234XYZ.
    Hint: start 5, length 4.
  4. Task 4. Third character of QX9-HELLO.
    Hint: length 1.
  5. Task 5. What does MID return when start is 10 on ABC?
    Hint: try it — notice there is no red error.
  6. Task 6. After-dash city using FIND+MID.
    Hint: FIND gives the dash position.
  7. Task 7. Middle initial from R-K-BAGALE.
    Hint: position 3.
  8. Task 8. Note in a cell: is start_num 0-based?
    Hint: test MID(A2,1,1).
  9. Task 9. Compare fixed MID with FIND-based MID on two layouts.
    Hint: change one invoice format.
  10. Task 10. Explain when TEXTBEFORE would replace your MID.
    Hint: delimiter clarity.

Quick quiz

Q1. MID("INV-PUNE-7781",5,4) returns:
Q2. start_num counting starts at:
Q3. Start past the end returns:
Q4. Flexible start after a dash uses:
Q5. MID result type is usually:

Answers: Q1=PUNE, Q2=1, Q3=Empty text, Q4=FIND inside MID, Q5=Text (check after you try).

Revision card

Standalone card (no tip or photo strip). QR opens this lesson. Download the PNG and revise offline.

Revision card: MID
MID revision card