Quick Jump
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

Get wellsrPRO for Free

Get these macros delivered right to your spreadsheet for free

Access tutorials and import my macros without ever leaving Excel

Show me - It's free

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])
  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 string 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 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!


Comments

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!


Get wellsrPRO for Free

Get these macros delivered right to your spreadsheet for free

Access tutorials and import my macros without ever leaving Excel

Show me - It's free

About Ryan Wells


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.