Introduction | Example | Tutorial | Applications | Comments
Introduction - VBA Mid
The VBA Mid function extracts a substring from a string. VBA Mid accepts 3 arguments to define which substring you want to extract from your main string.
Example - VBA Mid
Sub MidFunctionDemo() str1 = "abc-123-yyy" str2 = Mid(str1, 5, 3) 'Extracts 123 End Sub
Tutorial - VBA Mid
The macro above illustrates how the VBA Mid function behaves. It accepts 3 arguments, the third of which is optional:
Mid(String, Start As Long, [Length])
- String - The string you want to extract the substring from.
- Start As Long - The starting position where you want to begin extracting your string from. This is an integer (long) data type. Numbering starts at 1 with the first character of your string.
- Length - The number of characters you want to extract from the main string. This argument is optional. Omitting the Length argument will extract the remainder of the substring starting with the character defined in the 2nd argument.
In the example above, we passed the VBA
Mid function all 3 arguments. We told it to extract
Had we not provided the optional 3rd argument, like this:
Sub MidFunctionDemo2() str1 = "abc-123-yyy" str2 = Mid(str1, 5) End Sub
the value stored in str2 would be
Application Ideas - VBA Mid
In October, I introduced you to a user-defined function (UDF) I created to extract text between two strings with VBA. This UDF, which I call SuperMid, relies heavily on the principles I introduced in this tutorial. It searches for where a substring begins, then calculates lengths to extract the new string using the Mid function. This function is included in my wellsr.com Tools Excel Add-in.
The Mid function is one of the most, if not the most, important string manipulation function to understand when extracting data from fixed-width text files. You can march down, row by row, and manipulate the data effortlessly.
Fortunately, you don’t have to be a VBA expert to know how Mid works. Excel has its own native worksheet function called Mid that behaves almost the exact same as the VBA Mid function. The only difference is that the 3rd argument (Length) is required in the Excel Worksheet version of the function, whereas it’s optional in the VBA Mid function.
Check out my tutorials on other string manipulation functions to quench your thirst for more VBA knowledge!
Please, share this article on social media and follow me on Google+ and Twitter for even more great VBA content. Sharing on social media is how I’m able to reach and teach more people about the awesome power of VBA.
I want to thank all my readers who have already subscribed to my email list and I encourage you to go ahead and subscribe if you haven’t done so. You’ll love the great VBA content I send your way!
About Ryan Wells
Ryan Wells is a Nuclear Engineer and professional VBA Developer. He is the lead developer of several VBA applications, including PDF VBA - a leading Excel Add-in for exporting Excel Objects, like charts and tables, to PDFs. Discover more of his popular Excel Add-ins, including Mouse To Macro and CF Shapes, at his dedicated Excel Add-ins page.Follow