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 macros with our free VBA Developer Kit

This is actually pretty neat. If you have trouble understanding or remembering it, our free VBA Developer Kit can help. It’s loaded with VBA shortcuts to help you make your own macros like this one - we’ll send a copy, along with our Big Book of Excel VBA Macros, to your email address below.

I'll take a free VBA Developer Kit

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])
  1. String - The string you want to extract the substring from.
  2. 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.
  3. 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 3 characters, starting at the 5th character in the string str1. The result is that str2 now contains the substring 123.

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 123-yyy. In other words, the entire string after the 5th character would be saved to the variable str2.

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!

Please, share this article on Twitter and Facebook. 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 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!