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.

Like our excel MID function, 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.

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

Code:

Sub MID_VBA_Example1()

   Dim MiddleValue As String

End Sub

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

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

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 7^th character.

Note: Space is also a character.

Code:

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.

Code:

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.

Code:

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.

Code:

Sub MID_VBA_Example2()

    Dim FirstName As String

End Sub

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

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

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

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

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

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

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

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

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

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.

Output:

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.

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.

It should give a result like the one below.

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