Excel VBA MID Function
There are situations where we want to extract the first name, last name, or middle name. In those situations, the TEXT category formulas help fulfill our requirements. Therefore, the use of this function is the same as that of the worksheet reference. Therefore, the syntax is also the same.
You are free to use this image on you website, templates, etc., Please provide us with an attribution linkHow to Provide Attribution?Article Link to be HyperlinkedFor eg:Source: VBA MID Function (wallstreetmojo.com)
Syntax
Like our excel MID functionExcel MID FunctionThe mid function in Excel is a text function that finds strings and returns them from any mid-part of the spreadsheet. read more, in VBA too, it has a similar set of syntax values. Below is the syntax.
- String to Search: This is nothing but what is the sentence of the string, i.e., from which string or word you would like to extract the values.Starting Position: From which position of the sentence you want to extract. It should be a numeric value.Number of Characters to Extract: How many characters do you want to extract from the starting position? It also should be a numerical value.
How to use VBA MID Function?
Example #1
Assume you have the word “Hello Good Morning,” and you want to extract “Good” from this sentence. Follow the below steps to extract the value.
Step 1: Create a macro name first.
Code:
Sub MID_VBA_Example1()
End Sub
Step 2: Declare a variable as “STRING.”
Sub MID_VBA_Example1()
Dim MiddleValue As String
End Sub
Step 3: Now, assign a value to this variable through the MID function.
Sub MID_VBA_Example1()
Dim MiddleValue As String
MiddleValue = Mid(
End Sub
Step 4: The first argument is String, i.e., from which value we want to extract. So our value is “Hello Good Morning.”
Sub MID_VBA_Example1()
Dim MiddleValue As String
MiddleValue = Mid("Hello Good Morning",
End Sub
Step 5: Next up is what is the starting position of the character you want to extract. In this case, Good morning starts from a 7th character.
Note: Space is also a character.
Sub MID_VBA_Example1()
Dim MiddleValue As String
MiddleValue = Mid("Hello Good Morning", 7
End Sub
Step 6: Length is nothing but how many characters you want to extract. We need to extract 4 characters here because the length of the word “Good” is 4 characters.
Sub MID_VBA_Example1()
Dim MiddleValue As String
MiddleValue = Mid("Hello Good Morning", 7, 4)
End Sub
Step 7: We have completed the formula. Next, let us show the result of the variable in the message box.
Sub MID_VBA_Example1()
Dim MiddleValue As String
MiddleValue = Mid("Hello Good Morning", 7, 4)
MsgBox MiddleValue
End Sub
Step 8: Now run this code manually or press the F5 key. The message box should show the word “Good.”
Output:
Example #2
Assume you have a first and last name together, and the word is “Ramesh, Tendulkar.” Between the first name and last name, the separation character is a comma (,). So, now we need to extract the first name only.
Step 1: Create a macro and define a variable.
Sub MID_VBA_Example2()
Dim FirstName As String
End Sub
Step 2: Now, assign a value to this variable through the MID function.
Sub MID_VBA_Example2()
Dim FirstName As String
FirstName = Mid(
End Sub
Step 3: Our string is “Ramesh.Tendulkar,” so enter this word.
Sub MID_VBA_Example2()
Dim FirstName As String
FirstName = Mid("Ramesh,Tendulkar",
End Sub
Step 4: Since we are extracting the first name starting position is 1.
Sub MID_VBA_Example2()
Dim FirstName As String
FirstName = Mid("Ramesh,Tendulkar",1,
End Sub
Step 5: Length of the character you can directly enter as 6, but this is not the best way. To determine the length, let us apply one more formula called “Instr.”
Sub MID_VBA_Example2()
Dim FirstName As String
FirstName = Mid("Ramesh,Tendulkar",1,InStr(
End Sub
Step 6: For this starting position is 1.
Sub MID_VBA_Example2()
Dim FirstName As String
FirstName = Mid("Ramesh,Tendulkar",1,InStr(1,
End Sub
Step 7: String 1 is our name, i.e., “Ramesh, Tendulkar.”
Sub MID_VBA_Example2()
Dim FirstName As String
FirstName = Mid("Ramesh,Tendulkar",1,InStr(1,"Ramesh,Tendulkar",
End Sub
Step 8: String 2 is the separator of first and last names, i.e., comma (,).
Sub MID_VBA_Example2()
Dim FirstName As String
FirstName = Mid("Ramesh,Tendulkar",1,InStr(1,"Ramesh,Tendulkar",",")
End Sub
Note: The Instr function will return how many characters are in the word “Ramesh, Tendulkar” from the string 1 position to the string 2 positions, i.e., until comma (,). So Instr will return 7 as a result, including comma (,).
Step 9: Since the Instr function returns no. of characters including comma (,), we need to minus 1 character here. So, enter -1 after the close of the Instr function.
Sub MID_VBA_Example2()
Dim FirstName As String
FirstName = Mid("Ramesh,Tendulkar", 1, InStr(1, "Ramesh,Tendulkar", ",") - 1)
End Sub
Step 10: Now, show the variable’s value in the message box.
Sub MID_VBA_Example2()
Dim FirstName As String
FirstName = Mid("Ramesh,Tendulkar", 1, InStr(1, "Ramesh,Tendulkar", ",") - 1)
MsgBox FirstName
End Sub
Step 11: Run this code using the F5 key, or you can run this code manually. We would get the first name in the message box.
Example #3
Now, we will give you one assignment to solve. But, first, we have a list of first and last names.
From this list, we want you to extract the first name only. All the best!
If you have tried and cannot get the result, then the code below will help you.
Sub MID_VBA_Example3()
Dim i As Long For i = 2 To 15 Cells(i, 2).Value = Mid(Cells(i, 1).Value, 1, InStr(1, Cells(i, 1).Value, “,”) - 1) Next i
End Sub
Copy and paste the above code into your module. After copying the code, run this code using the F5 key, or you can run it manually.
It should give a result like the one below.
Things to Remember
- The length argument in the MID function is optional. If you ignore this, it will take 1 as the default value.To determine the length or starting position, use the Instr function and the MID function.
Recommended Articles
This article has been a guide to VBA MID Function. Here, we learned how to use the MID function to extract a word from a sentence, practical examples, and a downloadable Excel template. Below you can find some useful Excel VBA articles: –
- VBA Today FunctionVBA DIR in ExcelVBA VLookupFIND Function in VBAVBA Outlook
