VBA Mid Function
The Excel VBA Mid function is used to extract a substring of characters from the middle of an expression. Expression may be any valid VBA data type, except array or object. A user-specified starting position is required. Optionally, the number of characters to return is also specified.
Syntax:
Mid(Expression, Pos, N)
The VBA Mid function accepts three arguments, as described below.
Arguments:
| Expression | - the expression from which a substring in the middle is extracted |
| Pos | - beginning position of substring to return |
| N | - length of substring to return |
Notes about usage:
- Expression may be any valid VBA data type, except array or object.
- Mid will accept an element of an array for the expression.
- If Expression is a zero-length string (""), then a zero-length string ("") is returned for all valid values of Pos and N.
- If N is a fractional number, then it is rounded to the nearest Long integer.
- If the rounded value of N is a negative number, then an error message is generated and the program execution ends.
- If the rounded value of N is 0, then a zero-length string ("") is returned.
- If Pos is a fractional number, then it is rounded to the nearest Long integer.
- The rounded value of Pos must be a positive number or an error message is generated and the program execution ends.
- If Pos is greater than the length of the Expression, then a zero-length string ("") is returned.
- If N is greater than or equal to the number of characters in Expression from Pos to the end, then the entire substring from Pos to the end is returned.
- If Pos is a positive integer that is less than or equal to the length of the Expression, and N is unspecified, then the entire substring from Pos to the end is returned.
How to use the Excel VBA Mid function to extract a substring from a string
Dim str1, str2 As String
Dim Pos, N As Integer
str1 = "123 Street Dr, City, State Zip"
Pos = 16
N = 4
str2 = Mid(str1, Pos, N)
MsgBox str2
' Returns City
Position is set to sixteen (16), which is at the "C" in City. Here we are asking for four (4) characters to be returned starting with and including "C." VBA Mid function returns "City."
Using VBA Mid function with a fractional starting position
Dim str1 As String
Dim Pos As Double
Dim N As Integer
str1 = "123 Street Dr, City, State Zip"
Pos = 15.7
N = 11
MsgBox Mid(str1, Pos, N)
' Returns City
Excel VBA Mid will accept a fractional number for Pos, but converts it to a Long integer. Here, Pos is converted to sixteen (16). Asking for eleven (11) characters beginning with position sixteen (16) results in a return value of "City, State."
Using VBA Mid function with a fractional starting position and no length
Dim str1 As String
Dim Pos As Double
str1 = "123 Street Dr, City, State Zip"
Pos = 15.7
MsgBox Mid(str1, Pos)
' Returns City, State Zip
Excel VBA Mid does not require the final argument, N. When omitted, the entire remaining string from the starting position to the right is returned. Here, "City, State Zip" is returned.
Using VBA Mid function with no start position and no length
str1 = "123 Street Dr, City, State Zip"
MsgBox Mid(str1)
If both Pos and N are unspecified, then VBA Mid returns an error message and the program execution ends.
