Introduction | Example | Tutorial | Applications | Comments

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

Becoming a VBA expert isn't hard
Over 5000 members are improving their VBA skills for free with our email tutorials. Why don't you join them? Our experts share time-saving VBA tips and we'll give you access to our huge macro library - it's sure to speed up your macro development.

I want to get your free VBA material

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!


Comments

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 if you haven’t done so. You’ll love the great VBA content I send your way!