MID Function in Excel VBA
MID Function is commonly used to extract a substring from a full-text string. It is categorized under String type variable. VBA Mid function allows you to extract the middle part of the string from a full-text string. VBA string functions do not change the original string. Usually, they will return a new string based on the function you input in the code. VBA Mid function returns a substring from within a supplied text or a string.
In VB macro window, when you click or enter CTRL + SPACE, VBA Intellisense drop-down menu appears, which helps you out in writing the code & you can autocomplete words in the VB editor. After typing MID and click on spacebar below-mentioned syntax appears.
Syntax of VBA MID in Excel
The syntax for VBA Mid function in excel is as follows:
MID(string variable, starting point, [length])
or
MID(text_string, start_number, char_numbers)
or
Mid(string_to_search, starting position, number_of_characters)
String Variable or Text string or string_to_search: It is a text string from where you want to start extracting the specified number of characters.
Start_number or Starting point: Character position or number from where to start extracting the sub-string.
char_numbers or Length (Optional): Number of characters to extract or to return from the start position.
How to Use Excel VBA MID?
Below are the different examples to use Mid in Excel using VBA code.
Excel VBA MID – Example #1
Follow the below steps to use excel VBA MID.
Step 1: Select or click on Visual Basic in the Code group in the Developer tab or you can directly click on Alt + F11 shortcut key.
Step 2: To create a blank module, under the Microsoft excel objects, right-click on sheet 1(VB_MID) Insert and under the menu section select Module so that a new blank module gets created.
Step 3: Under the Microsoft Excel objects, right-click on sheet 1 (VB_MID) Insert and under the menu section select Module so that a new blank module gets created.
Excel VBA MID – Example #2
Now the blank module is created, it is also called as a code window, where you can start writing VBA MID function statement codes.
Suppose, I have the word “RAMNATHR@OUTLOOK.COM” and you want to extract email domain i.e. “OUTLOOK” from this sentence with the help of VB MID function macro code.
Step 1: In the VBA editor, I have a given a name as VBA_MID_1() after typing Sub.
Code:
Sub VBA_MID_1() End Sub
Step 2: As the MID function is categorized under string type variables, DIM (Dimension) is used in a VBA code to declare a variable name, it’s type.
Code:
Sub VBA_MID_1() Dim MiddleValue As String End Sub
Step 3: After declaring a variable, assign a value to this variable with the help of MID function.
Code:
Sub VBA_MID_1() Dim MiddleValue As String MiddleValue = Mid End Sub
In VB macro window, when you click or enter CTRL + SPACE, VBA Intellisense drop-down menu appears, which helps you out in writing the code & you can autocomplete words in VB editor. After typing MID press the spacebar key and the above-shown syntax appears.
Step 4: First parameter or argument is String i.e. It is a text String from where you want to start extracting the specified number of characters. Here it is “RAMNATHR@OUTLOOK.COM” when using strings argument, you have to surround the text in quotation mark.
Start As Long: It is the starting position of the character from where you want to extract. Here, in this case, it is “10”
Length: It is a number of characters to extract or to return from the start position, Here, in this case, it is “7”
Code:
Sub VBA_MID_1() Dim MiddleValue As String MiddleValue = Mid("RAMNATHR@OUTLOOK.COM", 10, 7) MsgBox (MiddleValue) End Sub
We have completed the MID function arguments here. Now, I want to display this result of the variable in the message box. Let’s press Ctrl + Space, type Msg, in the bracket you can mention a variable name.
Step 5: Now, the code is ready, I can run it by clicking on the F5 key. Once I do that, pop up message appears, with the result in it.
Excel VBA MID – Example #3
Instead of output appearing in the message box, I want the result or output data to appear in the worksheet, i.e. in cell “D5”. In the worksheet, I have a data, i.e. Email id of the employee in cell “C5”, now I want to extract email domain form this, I can use the MID function with a slight modification of code.
Let’s check out, how it can be done
Step 1: After declaring a variable, I need to input the variable name again and cell address of the full-text string with the help of Range or cell function.
Code:
Sub VBA_MID_2() Dim MiddleValue As String MiddleValue = Range("C5") End Sub
Step 2: Now, again I need to enter the variable name, and apply mid function & input its arguments.
Code:
Sub VBA_MID_2() Dim MiddleValue As String MiddleValue = Range("C5") MiddleValue = Mid("RAMNATHR@OUTLOOK.COM", 10, 7) End Sub
In the previous example, we entered a msg box for the result to be displayed, now, I want the result to appear in a specific cell in a worksheet. For this, I need to enter the range or cell function for the result to be displayed.
Step 3: Let’s apply range function, initially we need to input the range or cell function and Later enter the variable name, so that in that specific cell (“D5”), the result appears.
Code:
Sub VBA_MID_2() Dim MiddleValue As String MiddleValue = Range("C5") MiddleValue = Mid("RAMNATHR@OUTLOOK.COM", 10, 7) Range("D5") = MiddleValue End Sub
Step 4: Now, the code is ready, I can run it by click on the F5 key. once I do that, you can observe an output value of MID function appearing in cell “D5”
Things to Remember
- In Excel VBA MID function, Length argument is an optional parameter. If you fail to enter any argument or ignore this, VBA Mid function returns all characters from the supplied start position to the end of the string.
- In Excel VBA MID function, if the start number argument is greater than the length of the text string, then the MID function returns an empty string (zero-length).
- The mid function is very significant and useful along with loops function, as it helps you to examine one character at a time from a string of text.
Recommended Articles
This is a guide to VBA MID. Here we discuss how to use MID in Excel using VBA code along with few practical examples and downloadable excel template. You can also go through our other suggested articles –
