People often use the MID Function to get a substring out of a full-text string. It’s considered a variable of the String type. The VBA Mid function lets you get the middle part of a full-text string. String functions in VBA do not modify the supplied String. In most cases, the function you specify in the code will return a new string. Excel’s Mid function returns a specified number of characters from the text or String.
When you press CTRL + SPACE in the Visual Basic for Applications (VBA) macro window, a drop-down menu called VBA Intellisense displays. When you press the spacebar after typing MID, the syntax will appear. The function will return a Null value if the string argument provided is Null. If the start number parameter’s value is greater than the text string argument’s length, the VBA MID function will return a null string.
Syntax
Mid(string_to_search, starting position, number_of_characters)
Parameter
1. String or text_string or string_to_search (required): The length of the String we want to extract is shown by the String argument.
2. Starting point or start_number (required): The number of characters from the sub-string should be taken.
3. Length or number_of_characters (optional): This length argument tells you the number of characters you want to get from the start position.
Example
Find the middle name from the string “Robin Mark Simon”.
One of VBA’s built-in functions, the MID function, is used by many users to get only a part of the String or value given by the user. Here are the steps for writing a VBA macro to use the VBA Left function to get the middle name from the given String:
Step 1: Open the tab for the VBA developer. Go to the Excel worksheet, click on the ribbon tab, and then click on the developer window. Tap on the visual basic editor or press Alt+F11.
Step 2: You will see the VB Editor window. Next, you need to do a module. Click on Insert on the ribbon, then click on Module.
Step 3: The VBA adds a new window for the Module. Start the programme by implementing the name of your macro and declaring the variables.
Sub VBA_MID_Function() Dim str_MiddleName As String End Sub
Step 4: Next, add the VBA MID function. In the arguments, we will give the String, the start character, which is 6, and the length, which is 5, as we want to get the first four characters of our middle substring.
Sub VBA_MID_Function()
Dim str_MiddleName As String
str_MiddleName = Mid("Robin Mark Simon", 6, 5)
MsgBox str_MiddleName
End Sub
Output
Either click Run or press the F5 key to run your macro. So, you will see that VBA will throw a MsgBox with the middle String in it.
