VBA MID Function
There arise many situations in day-to-day Excel life where we want to fetch the first substring, last substring, or middle substring. We can rely upon the TEXT formulas to fulfill our requirements in such situations. Although these functions work in the same manner as Excel worksheet reference, the syntax is also the same. One such function is VBA MID. It is helpful to extract the middle string from the input string.
This tutorial will run you through the definition, syntax, parameters, return type, and different examples explaining how to write a macro code using the VBA MID function.
What is MID Function?
The VBA MID Function is commonly used to extract the values (usually a substring) from the middle from a full-text string. It is categorized under String type variable.
VBA Mid function enables the user to fetch the middle part of the string from a full-text string. The best part about VBA string functions is that they do not change the original string. Instead, they will return a new string after modifying the input string. The VBA Mid function returns a substring from a supplied text or a string.
In other words, the VBA MID function only fetches the middle part from the input string. For instance, For instance, if you have a String “Sukla Rani Patra,” here the middle value is “4” in this string starting position of the character to be extracted is 6, and we need 6 characters from the starting position.
This function can be used as a VBA function and a worksheet function in excel. It can be used as a part of the formula in the cell. The VBA MID function is listed under the text category of VBA functions. It just functions like the MID function in the Excel worksheet. VBA MID Function is categorized under String type variable.
Syntax
OR
OR
Parameter
- String or text_string or string_to_search (required): The String argument represents the length of the string we are trying to extract.
- Starting point or start_number (required): This parameter represents the character number from where to start extracting the sub-string.
- Length or number-of_characters (optional): This length argument represents but how many characters you want to fetch from the start position.
Return
The VBA MID Function is commonly used to extract the values (usually a substring) from the middle from a full-text string.
Points to Remember
- The VBA MID function returns a string-based output for a given string.
- IF the specified string argument is Null, this function will return Null as the output.
- In this function, if the parameter start_number is greater than the length of the text_string argument, the VBA MID function returns an empty string (zero-length) as an output.
Examples
# MID Function Example 1: Fetch the middle name from the string “Sukla Rani Panda”.
VBA MID is one of the inbuilt functions that many users use to fetch only a part of the String (usually the middle substring) or value given by the user.
Below given are the steps to write a VBA macro to fetch the middle name from the specified string using the VBA Left function:
Step 1: Open the VBA developer tab. Go to Excel worksheet, from the ribbon tab click on developer window -> visual basic editor or directly click on the shortcut keywords Alt +F11.
Step 2: The VB Editor window will be displayed. The next step is to create a module. From the ribbon tab click on Insert-> Click on Module.
Step 3: You will notice, VBA will insert a new module window. Start the program by introduce your macro name followed by the declaration of the variable.
Refer to the below given macro code:
Step 4: Next, we will incorporate the VBA MID function. In the arguments, we will pass the String, the start_character i.e., 6 and in the length argument, we will pass 5 since we want to extract the first 4 characters of our middle substring.
Store the fetch value in our variable and later return the variable using MsgBox.
Output
Run your macro either by clicking Run or by pressing the F5 key. As a result, you will notice VBA will throw a MsgBox displaying middle string.
The macro has extracted the middle 4 characters from the string “Sukla Rani Panda.” So the output will display middle name, i.e., “Rani”.
#MID Example 2: Fetch the middle name from the list of Excel cells using VBA MID Function.
The good part of VBA language is that it can be written more than one way. You can assign the VBA MID macro to your Active button, and it will perform the macro operation whenever the event is clicked. Using the event, you can extract the middle name for all of your data at once.
Following are the steps to write a VBA macro to eliminate the unwanted space characters from the given string using the VBA TRIM function:
Step 1: Open the Excel worksheet. From the ribbon tab click on developer window -> visual basic editor or directly click on the shortcut keywords Alt +F11.
Step 2: The VB Editor window will be displayed. The next step is to create a module. From the ribbon tab click on Insert-> Click on Module.
Step 3: The Moule window will be inserted. Start the program with the macro name followed by the declaration of the variable. Set the variable value with the Excel selection.
Refer to the following macro code:
Step 4: Since, we have to iterate the function to further cells, so will use the For loop. Inside that will trim the string and store the value in Excel cell. Once done, we will display a message note using VBA MsgBox.
Step 5: Once we are done with code writing, we will close the VBA editor window. Again, go to your Excel worksheet click on the Developer -> Insert menu.
As shown below, you will get a drop-down menu displaying Form Controls and ActiveX Controls. Choose the button option from the list.
Step 6: Using your mouse cursor, draw the button shape anywhere on your Excel worksheet. Assign a macro name, and you can also select from the list of created macros. Here we have selected MID_Function_Example2. Click on OK.
As a result, the button will be created at the selected position of your Excel worksheet. You can also change the name by double-clicking on the top of it. Here, we have mentioned the MID function.
Step 7: The last step is to assign some macro to this button. Right-click on the MID button. A window will appear displaying the available macros. Select the Marco with the name ‘MID_Function_Example2’ and click on OK. Once done, Excel will assign the macro to this button.
Output
We will insert some text strings in various cells to test the above macro.
For testing, select the cells with data and then click on the MID Button.
As a result, you will notice all the middle names has been extracted from the list of cells.
# MID Function Example 3: VBA MID program returns an empty string.
Sometimes, you receive an empty string even if the entire logic is correct. This kind of error mostly occurs if the parameter start_number is greater than the length of the text_string argument. Let’s see an example where the VBA MID program will return an empty string:
Step 1: Open the VBA developer tab. Go to Excel worksheet, from the ribbon tab click on developer window -> visual basic editor or directly click on the shortcut keywords Alt +F11.
Step 2: The VB Editor window will be displayed. The next step is to create a module. From the ribbon tab click on Insert-> Click on Module.
Step 3: You will notice, VBA will insert a new module window. Start the program by introduce your macro name followed by the declaration of the variable.
Refer to the below given macro code:
Step 4: Next, we will incorporate the VBA MID function. In the arguments, we will pass the String, the start_character i.e., 26 and in the length argument, we will pass 15. As you will notice, the start_character is nore than the actual string.
Store the fetch value in our variable and later return the variable using MsgBox.
Output
Run your macro either by clicking Run or by pressing the F5 key. As a result, you will notice VBA will throw a MsgBox displaying middle string.
The macro has thrown an empty string, as shown above; always make sure that the values of both the parameters are entered properly and according to the logic.
