How to Use the MID Function in Excel

Description

The Excel MID function returns a certain number of characters from the middle of the specified text string.

As a quick example, =MID(“worksheets”,5,6) returns “sheets.”

In plain English, you tell Excel to pick a text string (the first argument; “worksheets”) and extract a certain number of characters from it by specifying the location of the first character from where to start the extraction (the second argument; “5” or the first letter “s” in the word “worksheets”) and the number of characters to want to extract (the third argument; “6” or “sheets”).

Purpose

The function helps extract the characters from a string which would be problematic to pull using the LEFT or RIGHT functions. 

When combined with other functions like SEARCH and LEN, you can slice and dice any data with ease, be it names, dates, or CSV files.

Syntax

=MID(text,start_num,num_chars)

Arguments

  • text – the location of the value you want to extract from.
  • start_num – the position where the selection starts.
  • num_chars –  the number of characters that you want to extract from the string.

Return Value

The MID function returns the extracted characters as string/text values.

Usage Notes

1. Applying the MID function to numeric values converts them into text/string values.

Using the MID function to convert numbers to text

2. If the num_chars argument is greater than the overall number of characters in the text string, Excel returns all of the remaining characters in the string starting from the location specified by the start_num argument.

Using num_chars

3. If start_num is less than “1,” the #VALUE! error is triggered.

When num_chars is less than 1

4. When the start_num argument is greater than the overall number of characters in your text string, the MID function returns an empty string.

When start_num is greater than the overall number of characters

5. If num_chars equals zero(“0”), the MID function returns an empty string.

When num_chars equals zero

6. Each space is counted as one character.

The Excel MID function and whitespaces

Examples

For you to better understand how the formula works, let’s pick one phrase “James Bond 007” and run with it to show you different ways to use the Excel MID function.

This MID function returns 4 characters starting at the 7th character:

=MID("James Bond 007",7,4) // Output: Bond

This MID function returns 3 characters starting at the 12th character:

=MID("James Bond 007",12,4) // Output: 007

This function extracts everything starting at the 7th character since the num_charts argument is greater than the remaining number of characters in our string.

=MID("James Bond 007",7,100) // Output: Bond 007

How to Use the MID Function in Excel VBA

Thought we were done? Well, not so fast. 

The Excel MID function can also be used in VBA, providing you with more flexibility with your data. Here’s a quick example showing you how to use the MID function in Excel VBA.

Sub VBA_MID_Function()
MidValue = Mid(Range("A2"), 7, 4)
Range("B2") = MidValue
End Sub

If you’re unfamiliar with VBA, here’s how you can adjust.

First, leave the first and last lines of code unchanged. These are merely containers holding the VBA code together. 

The second line of the VBA code declares a new variable (MidValue) storing the extracted characters using the MID function. 

This VBA MID function selects cell A2 and extracts 4 characters starting at the 7th character, returning the word “Bond.”The third line is responsible for moving those extracted characters from the VBA editor into cell B2.

Using the Excel MID function in VBA