VBA MID Function

Excel VBA MID Function

VBA MID function extracts the values from the middle of the supplied sentence or word. The MID function is categorized under the String and Text function and it is a worksheet function which means to use this function in VBA we need to use the application.worksheet method.

There are situations where we want to extract the first name, last name, or middle name. In those situations, TEXT category formulas are helpful to fulfill our requirements.  The use of this function is the same as that of the worksheet reference, and the syntax is also the same.

VBA MID Function

Syntax

Like our excel MID function, in VBA too, it has a similar set of syntax values. Below is the syntax.

MID Syntax
  • String to Search: This is nothing but what is the sentence of 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. This should be a numeric value.
  • Number of Characters to Extract: From the starting position, how many characters you want to extract? This also should be a numerical value.

How to use VBA MID Function?

You can download this VBA MID Function Template here – VBA MID Function Template

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
VBA MID Example 1

Step 2: Declare a variable as “STRING.”

Code:

Sub MID_VBA_Example1()

   Dim MiddleValue As String

End Sub
VBA MID Example 1-1

Step 3: Now assign a value to this variable through the MID function.

Code:

Sub MID_VBA_Example1()

    Dim MiddleValue As String

    MiddleValue = Mid(

End Sub
VBA MID Example 1-2

Step 4: The first argument is String, i.e., from which value we want to extract. So our value is “Hello Good Morning.”

Code:

Sub MID_VBA_Example1()

    Dim MiddleValue As String

    MiddleValue = Mid("Hello Good Morning",

End Sub
VBA MID Example 1-3

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.

Code:

Sub MID_VBA_Example1()

    Dim MiddleValue As String

    MiddleValue = Mid("Hello Good Morning", 7

End Sub
VBA MID Example 1-4

Step 6: Length is nothing but how many characters you want to extract. We need to extract 4 characters here because of the length of the word “Good” is 4 characters.

Code:

Sub MID_VBA_Example1()

    Dim MiddleValue As String

    MiddleValue = Mid("Hello Good Morning", 7, 4)

End Sub
VBA MID Example 1-5

Step 7: We have completed the formula. Let’s show the result of the variable in the message box.

Code:

Sub MID_VBA_Example1()

    Dim MiddleValue As String

    MiddleValue = Mid("Hello Good Morning", 7, 4)

    MsgBox MiddleValue

End Sub
VBA MID Example 1-6

Step 8: Now run this code manually or press the F5 key, the message box should show the word “Good.”

VBA MID Example 1-6

Output:

VBA MID Example 1-7

Example #2

Assume you have a first name and last name together, and the word is “Ramesh, Tendulkar.” Between First Name & Last Name, separation character is a comma (,). Now we need to extract the first name only.

Step 1: Create a macro and define a variable.

Code:

Sub MID_VBA_Example2()

    Dim FirstName As String

End Sub
VBA MID Example 2

Step 2: Now assign a value to this variable through the MID function.

Code:

Sub MID_VBA_Example2()

    Dim FirstName As String

    FirstName = Mid(

End Sub
VBA MID Example 2-1

Step 3: Our string is “Ramesh.Tendulkar,” so enter this word.

Code:

Sub MID_VBA_Example2()

    Dim FirstName As String

    FirstName = Mid("Ramesh,Tendulkar",

End Sub
VBA MID Example 2-2

Step 4: Since we are extracting the first name starting position is 1.

Code:

Sub MID_VBA_Example2()

    Dim FirstName As String

    FirstName = Mid("Ramesh,Tendulkar",1,


End Sub
VBA MID Example 2-3

Step 5: Length of the character you can directly enter as 6, but this is not the best way. In order to determine the length, let’s apply one more formula called Instr.

Code:

Sub MID_VBA_Example2()

    Dim FirstName As String

    FirstName = Mid("Ramesh,Tendulkar",1,InStr(

End Sub
VBA MID Example 2-4

Step 6: For this starting position is 1.

Code:

Sub MID_VBA_Example2()

    Dim FirstName As String

    FirstName = Mid("Ramesh,Tendulkar",1,InStr(1,


End Sub
VBA MID Example 2-5

Step 7: String 1 is our name, i.e., “Ramesh, Tendulkar.”

Code:

Sub MID_VBA_Example2()

    Dim FirstName As String

    FirstName = Mid("Ramesh,Tendulkar",1,InStr(1,"Ramesh,Tendulkar",


End Sub
VBA MID Example 2-6

Step 8: String 2 what is the separator of first name & last name, i.e., comma (,).

Code:

Sub MID_VBA_Example2()

    Dim FirstName As String

    FirstName = Mid("Ramesh,Tendulkar",1,InStr(1,"Ramesh,Tendulkar",",")

End Sub
VBA MID Example 2-7

Note: Instr function will return how many characters are there 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 Instr function returns no., of characters including comma (,) we need to minus 1 character here. So enter -1 after the close of Instr function.

Code:

Sub MID_VBA_Example2()

    Dim FirstName As String

    FirstName = Mid("Ramesh,Tendulkar", 1, InStr(1, "Ramesh,Tendulkar", ",") - 1)

End Sub
VBA MID Example 2-8

Step 10: Now show the value of the variable in the message box.

Code:

Sub MID_VBA_Example2()

    Dim FirstName As String

    FirstName = Mid("Ramesh,Tendulkar", 1, InStr(1, "Ramesh,Tendulkar", ",") - 1)

    MsgBox FirstName

End Sub
Example 2-9

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 2-10

Output:

Example 2-11

Example #3

Now I will give you one assignment to solve. I have a list of First Name & Last Name.

MID Example 3

From this list, I want you to extract the first name only. All the best!!!!.

Ok, If you have tried and not able to get the result, then the below code would help you in this.

Code:

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 & Paste the above code in your module. After copying the code, run this code using the F5 key, or you can run manually.

visual basic application mid MID Example 3-1

It should give a result like the below.

visual basic application mid Example 3-2

Things to Remember

  • The length argument in the MID function is optional. If you ignore this, it will take 1 as the default value.
  • In order to determine the length or starting position use the Instr function along with the MID function.

This has been a guide to VBA MID Function. Here we learned how to use the mid function to extract a word from a sentence along with practical examples and a downloadable excel template. Below you can find some useful excel VBA articles –

  • 3 Courses
  • 12 Hands-on Projects
  • 43+ Hours
  • Full Lifetime Access
  • Certificate of Completion
LEARN MORE >>