Learning about VBA MID function

In this Article we will learn about following poing

1. Introduction & syntax of VBA MID function
2. VBA Code for Extracting a substring
3. VBA Code for Extract to the End of the String
4. VBA Code for Dynamic Length Based on String

The VBA Mid function is used in Microsoft Excel VBA (Visual Basic for Applications) to extract a substring from a string. It is useful for manipulating text and extracting portions of a string based on specific positions.

Syntax

Mid(string, start, [length])

Parameters:

1. String

The string from which you want to extract a substring.

(Type: String, Required)

2. Start:

The position in the string where the extraction begins (1-based index).

(Type: Integer, Required)

3. Length (optional):

The number of characters to extract. If omitted, Mid will return the substring from the starting position to the end of the string.

(Type: Integer, Optional)

Return:

A String containing the specified substring

Examples:

1. Extracting a substring in VBA MID Function 

VBA Code:

Sub example()

Dim mystring As String

mystring = "Hello, Mr. Vijay"

MsgBox Mid(mystring, 8, 9)

End Sub

Explaination: Extracts 9 characters starting from position 8 in "Hello, Mr.Vijay”

2. Extract to the End of the String in VBA MID Function

VBA Code:

Sub example2()

Dim mystring As String

mystring = "Excel VBA Programing"

MsgBox Mid(mystring, 7)

End Sub

Explanation: Extracts from position 7 to the end of the string.

3. Dynamic Length Based on String in VBA MID Function

VBA Code:

Sub exmaple3()

Dim mystring As String

mystring = "data Analysis"

Dim lengthofsubstring As Integer

lengthofsubstring = Len(mystring) - 5

MsgBox Mid(mystring, 6, lengthofsubstring)

End Sub

Explanation: Dynamically calculates the length of the substring to extract from position 6.

Notes:

• The start position is 1-based (not 0-based like some other programming languages).
• If the start is greater than the length of the string, Mid returns an empty string.
• If the length exceeds the number of characters remaining in the string, Mid returns the characters from the starting position to the end of the string.

VBA Code Excel File for Download 

More reated topic to Read

1.  VBA Instr Function :The InStr function in VBA is a search tool that helps find the location of a specific text (substring) within a larger text (main string).
2. VBA Split Function : The Split function in VBA is used to break a string into an array of smaller strings, based on a specified delimiter (e.g., a comma, space, or any other character).
3. VBA LEFT Function : The Left function in VBA is used to extract a specified number of characters from the start (left side) of a string.
4. VBA String Functions : Excel VBA provides a variety of string functions that help manipulate and analyze text. Here are the commonly used VBA string functions with examples: