VBA MID Function in Excel

VBA Mid function in Excel is categorized as a Text/String function in VBA. It is a built-in function in MS Office Excel. It returns a specified number of characters from a specified/given string. It has two mandatory parameters and one optional parameter. If input string contains Null, then the function returns Null.

We use this function as a VBA function and a Excel Worksheet function. The Mid function can use in either procedure or function in a VBA editor window in Excel. We can use this VBA Mid function any number of times in any number of procedures or functions. In the following section we learn what is the syntax and parameters of the Mid function, where we can use this Mid function and real-time examples in VBA.

Table of Contents:

Overview
Syntax of VBA Mid Function
Parameters or Arguments
Where we can apply or use the VBA Mid Function?
Example 1: Extract a sub-string from the specified string
Example 2: Get First_Name from Full_Name
Example 3: Extract Email Id from Email
Example 4: Extract File Extension from the File Name
Instructions to Run VBA Macro Code
Other Useful Resources

Syntax of VBA Mid Function

The syntax of the VBA Mid function is

Mid(String, Start, [Length])

Note: This Mid function returns a specified number of characters from a supplied string.

Parameters or Arguments

This function has two mandatory parameters and one optional argument for the Mid Function.
Where
String:The string is a mandatory argument. It represents a string which we want to extract a sub-string from
Start:The start is a mandatory argument. It represents a starting position of substring.If start value is greater than the number of characters in string, the Mid function returns an empty string.
Length:The length is an optional parameter. It represents a number of characters to be extracted from a string.

Where we can apply or use the VBA Mid Function?

We can use this VBA Mid function in MS Office 365, MS Excel 2016, MS Excel 2013, 2011, Excel 2010, Excel 2007, Excel 2003, Excel 2016 for Mac, Excel 2011 for Mac, Excel Online, Excel for iPhone, Excel for iPad, Excel for Android tablets and Excel for Android Mobiles.

Example 1: Extract a substring from the specified string

Here is a simple example of the VBA Mid function. This below example macro returns a substring. The output of the below macro is ‘Welcome’.

'Extract a substring from the specified string
Sub VBA_Mid_Function_Ex1()
    
    'Variable declaration
    Dim sInput As String
    Dim sOutput As String
    
    sInput = "Welcome to VBAF1"
    
    sOutput = Mid(sInput, 12, 5)
       
    MsgBox " The substring is : " & sOutput, vbInformation, "VBA Mid Function"
    
End Sub

Output: Here is the screen shot of the first example output.

Example 2: Get First_Name from Full_Name

Here is a simple example of the VBA Mid function. This below example macro returns a substring. The output of the below macro is ‘Adam’.

'Get First_Name from Full_Name
Sub VBA_Mid_Function_Ex2()

    'Variable declaration
    Dim sInput As String
    Dim sTemp() As String
    Dim sOutput As String
    
    sInput = "Adam Christ John"
    
    sTemp = Split(sInput, " ")
    
    sOutput = Mid(sInput, 1, Len(sTemp(0)))
       
    MsgBox " The First Name is : " & sOutput, vbInformation, "VBA Mid Function"

End Sub

Output: Here is the screen shot of the second example output.

Example 3: Extract Email Id from Email

Here is a simple example of the VBA Mid function. This below example macro returns a substring. The output of the below macro is ‘vbahelp.contact’.

'Extract Email Id from Email
Sub VBA_Mid_Function_Ex3()

    'Variable declaration
    Dim sInput As String
    Dim sTemp() As String
    Dim sOutput As String
    
    sInput = "vbahelp.contact@gmail.com"
    
    sTemp = Split(sInput, "@")
    
    sOutput = Mid(sInput, 1, Len(sTemp(0)))
       
    MsgBox " The Email Id is : " & sOutput, vbInformation, "VBA Mid Function"
    
End Sub

Output: Here is the screen shot of the third example output.

Example 4: Extract File Extension from the File Name

Here is a simple example of the VBA Mid function. This below example macro returns a substring. The output of the below macro is ‘xls’.

'Extract File Extension from the File Name
Sub VBA_Mid_Function_Ex4()

   'Variable declaration
    Dim sInput As String
    Dim sTemp() As String
    Dim sOutput As String
    
    sInput = "C:/Test/File_Name.xls"
    
    sTemp = Split(sInput, ".")
    
    sOutput = Mid(sInput, Len(sTemp(0)) + 2, Len(sTemp(1)))
       
    MsgBox " The File extension is : " & sOutput, vbInformation, "VBA Mid Function"
    
End Sub

Output: Here is the screen shot of the fourth example output.