MID Function in Excel Tutorial

Excel Tutorial

Introduction

The MID Function in Excel extracts a specific number of characters from the middle of a text string. You choose the starting position and the number of characters to return.

It is useful for extracting department codes, years, product identifiers, and employee numbers from structured text. This tutorial focuses entirely on the MID function, with practical examples and exercises to help you understand how it works.

MID Function Syntax

=MID(text, start_num, num_chars)
  • text: The text or cell containing the data.
  • start_num: The position of the first character to extract.
  • num_chars: The number of characters to extract.

Important: Excel starts counting character positions from 1. Spaces, hyphens, and other symbols also count as characters.

Question 1: Extract a Department Code from Employee IDs

Problem Statement

An HR department stores department codes inside employee IDs. Extract the two-character department code from each ID.

Employee IDDepartment Code
EMP-HR-1045HR
EMP-IT-2086IT
EMP-FN-3172FN
EMP-MK-4291MK

Assume employee IDs are stored in cells A2.

Excel Formula

=MID(A2,5,2)

Expected Result: HR

Student Task: Add EMP-AC-5834 in cell A6. Extract the two-character department code.

New Expected Result: AC

Question 2: Extract a Year from an Invoice Reference

Problem Statement

An accounts department stores the year in the middle of each invoice reference. Extract the four-digit year.

Invoice ReferenceYear
INV-2023-5012023
INV-2024-6022024
INV-2025-7032025
INV-2026-8042026

Assume invoice references are stored in cells A2.

Excel Formula

=MID(A2,5,4)

Expected Result: 2023

Student Task: Add INV-2027-905 in cell A6. Extract the year.

New Expected Result: 2027

Question 3: Extract a Product Code from a Reference

Problem Statement

A company uses product references containing a three-character product code in the middle. Extract the product code.

Product ReferenceProduct Code
ABC123XYZ123
DEF456WEB456
GHI789APP789
JKL234PRO234

Assume product references are stored in cells A2.

Excel Formula

=MID(A2,4,3)

Expected Result: 123

Student Task: Add MNO567NET in cell A6. Extract the three characters starting at position 4.

New Expected Result: 567

Question 4: Extract an Employee Number from a Complete Employee ID

Problem Statement

A company uses employee IDs in which the employee number always occupies the final four positions. Extract the employee number using MID.

Employee IDEmployee Number
EMP-HR-10451045
EMP-IT-20862086
EMP-FN-31723172
EMP-MK-42914291

Assume employee IDs are stored in cells A2.

Excel Formula

=MID(A2,8,4)

Expected Result: 1045

Student Task: Add EMP-AC-5834 in cell A6. Extract the four-digit employee number.

New Expected Result: 5834

Question 5: Understand Character Positions and Spaces

Problem Statement

A student database contains course codes with spaces. Extract the three characters starting at position 5.

Course CodeExtracted Text
WEB DESIGNDES
PYTHON BASICSON
DATA ANALYSISA A

Assume the course codes are stored in cells A2.

Excel Formula

=MID(A2,5,3)

Expected Result for WEB DESIGN: DES

Explanation: In WEB DESIGN, the characters are counted as follows:

CharacterWEBSpaceDESIGN
Position12345678910

The fifth character is D, so extracting three characters returns DES.

Student Task: Enter EXCEL TRAINING in cell A5. Extract three characters starting at position 7.

New Expected Result: TRA

Question 6: Extract Characters When the Requested Length Exceeds the Remaining Text

Problem Statement

A product list contains codes of different lengths. Extract five characters starting at position 3.

Product CodeExtracted Text
ABCDCD
LAPTOPPTOP
KEYBOARDYBOAR
XYEmpty text

Assume product codes are stored in cells A2.

Excel Formula

=MID(A2,3,5)

Expected Result for ABCD: CD

Explanation: MID returns the available characters when fewer characters remain than requested. If the starting position is beyond the end of the text, it returns an empty text string.

Student Task: Enter MON in cell A6. Extract five characters starting at position 2.

New Expected Result: ON

Student Tasks: MID Function in Excel

Complete these exercises independently using only the MID function.

Task 1: Extract Department Codes

Employee IDExpected Result
EMP-HR-101HR
EMP-IT-202IT
EMP-FN-303FN
EMP-MK-404MK

Task: Extract two characters starting at position 5.

Task 2: Extract the Year

Invoice ReferenceExpected Result
INV-2022-5012022
INV-2023-6022023
INV-2024-7032024
INV-2025-8042025

Task: Extract four characters starting at position 5.

Task 3: Extract Product Codes

Product ReferenceExpected Result
ABC456XYZ456
DEF789WEB789
GHI123APP123
JKL654PRO654

Task: Extract three characters starting at position 4.

Final Student Challenge: Build an Employee ID Summary

Problem Statement

You work in the HR department of a company. The employee database stores the department code and employee number inside a single employee ID. Extract both values into separate columns using only the MID function.

Employee IDDepartment CodeEmployee Number
EMP-HR-1045HR1045
EMP-IT-2086IT2086
EMP-FN-3172FN3172
EMP-MK-4291MK4291
EMP-AC-5834AC5834

Assume employee IDs are stored in column A, starting from cell A2.

Your Tasks

  1. Extract the two-character department code into column B.
  2. Extract the four-digit employee number into column C.
  3. Use cell references in your formulas.
  4. Apply both formulas to all employee records using the fill handle.
  5. Add EMP-IT-6728 and extract its department code and employee number.

Formula for Department Code

=MID(A2,5,2)

Formula for Employee Number

=MID(A2,8,4)

Expected Result for the New Employee

Employee IDDepartment CodeEmployee Number
EMP-IT-6728IT6728

Bonus Challenge: If cell A2 contains EXCEL2026TUTORIAL, write a MID formula to extract 2026.

Final Goal: Build an employee summary that extracts the required information from structured employee IDs using only the MID function.

Key Takeaways

  • MID extracts characters from a specified position in text.
  • The starting position begins at 1.
  • The number of characters determines how much text is returned.
  • Spaces and symbols count as characters.
  • MID returns the available characters if fewer remain than requested.
  • MID returns an empty text string if the starting position is beyond the end of the text.
  • MID returns text, even when the extracted characters contain only digits.

Frequently Asked Questions (FAQs)

1. What is the MID function in Excel?

MID extracts a specified number of characters from a text string, starting at a position you choose.

2. What is the syntax of the MID function?

The syntax is =MID(text, start_num, num_chars).

3. Does MID count spaces?

Yes. Spaces, hyphens, and punctuation marks count as characters.

4. Can MID extract numbers from an employee ID?

Yes. MID can extract digits from employee IDs, invoice references, and other text strings.

5. What happens if I request more characters than remain?

MID returns the characters available from the starting position to the end of the text.

6. What happens if the starting position exceeds the text length?

MID returns an empty text string.

7. What is the difference between LEFT, RIGHT, and MID?

LEFT extracts characters from the beginning, RIGHT extracts characters from the end, and MID extracts characters from a specified position.

8. Does MID return a number or text?

MID returns text, even if the extracted characters contain only digits.