How to Use the Mid Function of VBA in Excel (3 Examples)

Get FREE Advanced Excel Exercises with Solutions!

One of the most important and widely used functions that we use while working with VBA in Excel is the Mid function of VBA. It takes a string as the input and returns a given number of characters from the middle of the string as output. Today in this article, I’ll show you how you can use the Mid function of VBA with proper examples and illustrations.

Excel VBA Mid Function

This will return 9 characters from the string “Angela Catherine Nevills”, starting from the 8th character. It’s “Catherine”.

Output of Using the Mid Function of VBA in Excel


Download Practice Workbook

Download this practice book to exercise the task while you are reading this article.


Introduction to the Mid Function of VBA in Excel

Syntax of the Mid Function of VBA in Excel

Function Objective:

The Mid function takes a value as the input and returns a given number of characters starting from a given position of the value as the output.

The value can be a string, number, or even a boolean.

For example, if you insert Mid(“Angela Catherine Nevills”,8,9), it’ll return “Catherine”.

If you insert Mid(12345,2,3), you will get 234.

And insert Mid(False,2,3), you’ll get als.

Syntax:

The Syntax of the Mid function of VBA is:

=Mid(String,Start as Long,[Length])

Arguments:

Argument Required / Optional Explanation
String Required The string from which a number of characters will be returned.
Start Required The starting character of the string that’ll be returned.
Length Optional The number of characters that will be returned. Default is 1.

Return Value:

Returns a given number of characters from the middle of a string, starting from a given position.

3 Examples of the Mid Function of VBA in Excel

This time, let’s explore the Mid function of VBA in detail with a few examples.


1. Separating a Given Number of Characters from the Middle of Some IDs Using the Mid Function of VBA in Excel

Here we’ve got a data set with the IDs and Names of some employees of a company called Saturn Group.

Here, the 4th to 7th characters of each ID denotes the joining year of the respective employee.

Now we’ll develop a User-Defined function using the Mid function of VBA that’ll extract the joining year of each employee from the Employee ID.

You can use the following VBA code:

VBA Code:

Function Joining_Year(ID)
Joining_Year = Mid(ID, 4, 4)
End Function

Note: This code creates a function called Joining_Year.

VBA Code to Use the Mid Function of VBA in Excel

Output:

Run this function in any cell of your worksheet with an ID as the argument.

Here, in cell D4, we’ve entered the formula:

=Joining_Year(B4)

It has returned the joining year of the first employee, 2021.

Now you can drag the Fill Handle to get the joining years of the rest of the employees.

Output of Using the Mid Function of VBA in Excel

Explanation of the Code:

  • First, we declare a function called Joining_Year that takes an ID as the input by the line Function Joining_Year(ID).
  • Then we extract 4 characters from the ID starting from position 4, by the line Joining_Year = Mid(ID, 4, 4).
  • The line End Function declares the end of the function.

2. Extracting the Extensions from Some Email Addresses Using the Mid Function of VBA in Excel

Now we’ve added a new column to the data set, that contains the Email addresses of the employees.

New Data Set to Use the Mid Function of VBA in Excel

This time we’ll extract out the extensions of the Email Addresses using the Mid function of VBA.

The VBA code will be:

VBA Code:

Function Extension(Email_Address)
For i = 1 To Len(Email_Address)
    If Mid(Email_Address, i, 1) = "@" Then
        Extension = Mid(Email_Address, i + 1, Len(Email_Address) - (i + 4))
    End If
Next i
End Function

Note: This code creates a function called Extension.

Output:

Run this function in any cell of your worksheet with an Email Address as the argument.

Here, in cell E4, we’ve entered the formula:

=Extension(D4)

It has returned the extension of the first email address.

Output of Using the Mid Function of VBA in Excel

Then you can drag the Fill Handle to extract out the extensions of all the email addresses.

Explanation of the Code

  • First, we declare a function called Extension that takes any name as the input by the line Function Extension(Email_Address).
  • Then we start an iteration with a for-loop that checks each character of the Email Address to see whether it’s @ or not by the line If Mid(Email_Address, i, 1) = “@” Then.
  • If it finds a @, then it extracts out the required extension from the Email Address by the line Extension = Mid(Email_Address, i + 1, Len(Email_Address) – (i + 4)).
  • Finally, we declare the end of the function.

3. Checking Some Texts to See whether They Contain a Specific Text or Not

One of the most important usages of the Mid function is to see whether a text contains a specific text or not.

Let’s develop a function to check whether the Email Addresses contain the term “gmail” or not.

You can use the following VBA code:

VBA Code:

Function Checking(Text1, Text2)
For i = 1 To Len(Text1)
    If Mid(Text1, i, Len(Text2)) = Text2 Then
        Checking = "Yes"
        Exit For
    Else
        Checking = "No"
    End If
Next i
End Function

Note: This code creates a function called Checking.

Output:

Run this function in any cell of your worksheet with two texts as the argument.

Here, in cell E4, we’ve entered the formula:

=Checking(D4,"gmail")

It has returned yes because the 1st email address is a Gmail address.

Output of Using the Mid Function of VBA in Excel

Then you can drag the Fill Handle to do the same for all the Email IDs.

Output of Using the Mid Function of VBA in Excel

Explanation of the Code:

  • First, we declare a function called Checking that takes two texts as the arguments by the line Function Checking(Text1,Text2).
  • Then we start a for loop that checks each segment of Text1 starting from position 1, to see whether it’s equal to Text2 or not, by the line  If Mid(Text1, i, Len(Text2)) = Text2 Then.
  • If it finds Text2, then it returns “Yes”, otherwise it returns “No”.
  • Finally, we conclude the function by the line End Function.

Things to Remember

  • The 1st argument of the Mid function needn’t be a string always. It can be a string, a number, or even a boolean value.
  • But the 2nd and 3rd arguments must be numbers. They can be integers or fractions but must be numbers. If they are fractions, then the Mid function will convert them to the nearest integers.

Conclusion

Using these methods, you can use the Mid function of VBA to extract a given number of characters from the middle of any string. Do you have any questions? Feel free to ask us.

What is ExcelDemy?

ExcelDemy - Learn Excel & Get Excel Solutions Center provides online Excel training , Excel consultancy services , free Excel tutorials, free support , and free Excel Templates for Excel professionals and businesses. Feel free to contact us with your Excel problems.
Rifat Hassan
Rifat Hassan

Rifat Hassan, BSc, Electrical and Electronic Engineering, Bangladesh University of Engineering and Technology, has worked with the ExcelDemy project for almost 2 years. Within these 2 years, he has written over 250 articles. He has also conducted a few Boot Camp sessions on effective coding, especially Visual Basic for Applications (VBA). Currently, he is working as a Software Developer to develop and deploy additional add-ins to enhance the customers with a more sophisticated experience with Microsoft Office Suits,... Read Full Bio

We will be happy to hear your thoughts

Leave a reply

Advanced Excel Exercises with Solutions PDF

 

 

ExcelDemy
Logo