How MID works in Excel? Best MID examples

How MID works in Excel? Best MID examples

If you’ve ever worked with text in Excel, chances are you’ve needed to extract a specific portion of a string. That’s where the MID function comes in. It’s a simple yet powerful function that helps retrieve substrings from text while giving you control over which characters to extract. In this guide, I’ll break down how MID works in Excel and provide the best examples to demonstrate its practical applications.

What is the MID Function in Excel?

The MID function in Excel is used to return a specific number of characters from a text string, starting at a defined position. It’s particularly useful when you need to extract parts of a text, such as pulling product codes, separating names, or processing structured data.

The syntax for the MID function is:

=MID(text, start_num, num_chars)
  • text – The original text string from which you want to extract characters.
  • start_num – The position in the text where extraction should begin.
  • num_chars – The number of characters to extract from the text.

How MID Works in Excel? Best MID Examples

To understand MID better, let’s go through a few useful examples.

Example 1: Extracting a Substring from a Sentence

Let’s say you have the following text in cell A1:

Excel is amazing!

If I want to extract “is” from the sentence, I would use:

=MID(A1, 7, 2)

This formula:

  1. Starts at position 7 in “Excel is amazing!” (where “is” begins).
  2. Extracts 2 characters.

Result: “is”

Example 2: Extracting a Product Code from a Mixed String

Let’s say I have a list of product entries in column A, formatted like this:

A (Product Data)
P123-Apple
P456-Banana
P789-Grapes

If I want to extract just the numeric product codes, I can use the following formula:

=MID(A2, 2, 3)

Explanation:

  • Starts at position 2 (after “P”).
  • Extracts 3 characters (the numbers).

For “P123-Apple”, this will return “123”.

Example 3: Using MID with LEN to Extract the Last Part of a String

Sometimes, I don’t know the exact position of certain parts of a string but I know I want the last segment. In such cases, I combine MID with LEN and FIND.

Example dataset:

A (File Names)
Report_2024_Q1.pdf
Report_2024_Q2.pdf
Report_2024_Q3.pdf

If I want to extract just the quarter (e.g., “Q1”, “Q2”, “Q3”), I can use:

=MID(A2, FIND("_Q", A2) + 1, 2)

How it works:

  • FIND("_Q", A2) finds the position of “_Q” in the string.
  • We add 1 to start extracting right from “Q”.
  • We extract 2 characters (the quarter, e.g., “Q1”).

Common Errors When Using MID

When working with the MID function, you may encounter a few common errors:

  • #VALUE! – This occurs when start_num or num_chars is negative or non-numeric.
  • #VALUE! – If start_num is greater than the length of the text, an empty string (“”) is returned.

Conclusion

The MID function in Excel is incredibly useful for extracting text from strings when you know the start position. By combining it with other functions like FIND and LEN, you can extract dynamic portions of text efficiently.

Whether you’re handling product codes, extracting file names, or processing structured data, mastering MID ensures greater flexibility in text manipulation within Excel.

 

Other interesting article:

How RIGHT works in Excel? Best RIGHT examples