Introduction | Example | Tutorial | Applications
Introduction - VBA Substring
Extract a VBA substring from a string using the Mid function. VBA Mid accepts 3 arguments to define which substring you want to extract from your main string.
Example - VBA Substring
Sub MidFunctionDemo() str1 = "abc-123-yyy" str2 = Mid(str1, 5, 3) 'Extracts 123 End Sub
Make powerful spreadsheets with our free VBA Developer's Guide This is actually pretty neat. If you have trouble understanding or remembering it, our free VBA Developer Guide can help. It’s loaded with VBA shortcuts to help you make your own macros a whole lot faster - we’ll send a copy to your email address below.
This is actually pretty neat. If you have trouble understanding or remembering it, our free VBA Developer Guide can help. It’s loaded with VBA shortcuts to help you make your own macros a whole lot faster - we’ll send a copy to your email address below.
Tutorial - VBA Substring
The macro above illustrates how the VBA Mid function gets part of a string. You use Mid to get the substring by passing it 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 substring 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 Substring
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 free wellsrPRO 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 to extract substrings. The Excel Worksheet function, also called Mid, 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!
I want to thank all my readers who have already subscribed to my free wellsrPRO VBA Training Program and I encourage you to go ahead and subscribe using the form below if you haven’t done so. You’ll love the great VBA content I send your way!