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.
This will return 9 characters from the string “Angela Catherine Nevills”, starting from the 8th character. It’s “Catherine”.
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
⧭ 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.
⧭ 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.
⧭ 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.
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.
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.
Then you can drag the Fill Handle to do the same for all the Email IDs.
⧭ 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.