VBA MID Function

Publication Date :

Blog Author :

Download FREE VBA MID Function Excel Template and Follow Along!
VBA MID Function Excel Template.xlsx

Table Of Contents

arrow

Excel VBA MID Function

VBA MID function extracts the values from the middle of the supplied sentence or word and categorizes them under the String and Text function. It is a worksheet function that means to use 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, 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.

Excel 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 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
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 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. Next, let us 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 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.

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. To determine the length, let us 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 is the separator of first and last names, 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: 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.

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 variable's value 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, we will give you one assignment to solve. But, first, we have a list of first and last names.

MID Example 3

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.

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 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.

visual basic application mid MID Example 3-1

It should give a result like the one 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.
  • To determine the length or starting position, use the Instr function and the MID function.