VBA MID Function

VBA MID Function

Overview of VBA Mid Function:

VBA Mid function 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. It could be used as a VBA function and a Excel Worksheet function. The Mid function can be used 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:

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 substring 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.
VBA MID Function

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.
VBA MID Function

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.
VBA MID Function

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.
VBA MID Function

Instructions to use Macro Codes

Here are the step by step inMiductions to use the VBA MID function in Excel VBA.

  • Open an Excel workbook.
  • Press Alt+F11 to open VBA Editor window.
  • Go to Insert menu and click on module from the available options.
  • Copy above specified macros to the VBA editor.
  • Select any procedure from above codes and click on ‘Run’ command or use Keyboard shortcut ‘F5’.

Complete List of VBA Functions:

Please click on below link to go back to see all List of VBA Functions.
List of VBA Functions

Leave a Comment

Your email address will not be published. Required fields are marked *