How Exactly Do Substrings in VBA Work?
Contents
What is a String?
A collection/series of characters that includes numbers and punctuation is called a string. A single character or a single space is also considered a string.
Examples:
Three
Free
Testme123
$%#^%$
4
56,
9*
What is a Substring?
A substring is any part of a string that can be from the left, right, or middle. This also means that it can be a character in a string.
In programming, there may be a need to validate substrings/main strings based on some criteria which will further decide the flow of the program. VBA supports this using three different functions. These functions extract substrings from the main strings.
They work more or less like Excel formulas.
Left Function
It can return/extract a specific number of characters from the left side of the string.
Syntax:
Left (< string >, < length >)
Example 1
Here is a program that can extract 4 characters from the left side of the main string and print it. The main string is “Hello World.” 4 characters from the left of this string is “Hell.” So that is the output.
Sub substring_demo() ' declare variables Dim mainstring, substring ' assigning value to mainstring mainstring = "Hello World" ' extracting the substring substring = Left(mainstring, 4) ' print the extracted value Debug.Print substring End Sub
Example 2
The below program snips 7 characters from the left side of the main string and stores it in the variable named “substring.” Then it is printed.
Sub substring_demo() ' declare variables Dim mainstring, substring ' assigning value to mainstring mainstring = "It is a beautiful cake" ' extracting the substring substring = Left(mainstring, 7) ' print the extracted value Debug.Print substring End Sub
Example 3
In this program, the length provided is greater than the length of the string.
The length of the main string is 41, but I’m trying to extract 50 characters from the right side of the string. So, the whole string is returned to the substring variable.
Sub substring_demo() ' declare variables Dim mainstring, substring ' assigning value to mainstring mainstring = "He won the first prize in the competition" ' extracting the substring from the right side substring = Left(mainstring, 50) ' print the extracted value Debug.Print substring End Sub
Right Function
It can return/extract a specific number of characters from the right side of the string.
Syntax:
Right (< string >, < length >)
Where <string> is the main string from which substring has to be extracted and <length> is the number of characters to be extracted (It includes any spaces in between). If the length is greater than the length of the string, the entire string value is returned as a substring.
Example 1
Here is a program that can extract 5 characters from the right side of the main string and print it. The main string is “God is great.” 5 characters from the left of this string are “great.” So that is the output:
Sub substring_demo() ' declare variables Dim mainstring, substring ' assigning value to mainstring mainstring = "God is great" ' extracting the substring substring = Right(mainstring, 5) ' print the extracted value Debug.Print substring End Sub
Example 2
In this program, the length provided is greater than the length of the string.
The length of the mainstring is 41, but I’m trying to extract 50 characters from the right side of the string. So, the whole string is returned to the substring variable.
Sub substring_demo() ' declare variables Dim mainstring, substring ' assigning value to mainstring mainstring = "Did you attend the party yesterday" ' extracting the substring from the right side substring = Right(mainstring, 35) ' print the extracted value Debug.Print substring End Sub
Example 3
The program below snips 20 characters from the right side of the main string and stores it in the variable named “substring.” Then it is printed.
Sub substring_demo() ' declare variables Dim mainstring, substring ' assigning value to mainstring mainstring = "Insurance is not applicable to damages caused by natural calamities. So, please do not apply." ' extracting the substring from the right side substring = Right(mainstring, 20) ' print the extracted value Debug.Print substring End Sub
Mid Function
It can return/extract specific no of characters from anywhere in the middle of the string.
Syntax:
Mid (< string >, < start position >, [ < length > ])
Where <string> is the main string from which substring has to be extracted, <start position> is the position from where our substring starts, and <length> is the number of characters (it includes any spaces in between) to be extracted from the specified starting position. The length parameter is optional here. If it is not provided, or a bigger number than the length of the string is provided, then the substring is extracted from the starting position until the end of the string.
Example 1
Here is a program to snip 20 characters from the mainstring starting from its 11th position.
Sub substring_demo() ' declare variables Dim mainstring, substring ' assigning value to mainstring mainstring = "Insurance is not applicable to damages caused by natural calamities. So, please do not apply." ' extracting the substring from the right side substring = Mid(mainstring, 11, 20) ' print the extracted value Debug.Print substring End Sub
The output is:
Example 2
Now, in the same example above (Example 1), when I change the length value from 20 to 200, the output is as below:
Example 3
This time, let us try the program without the length option and see it is still working the same way as in example 2:
Sub substring_demo() ' declare variables Dim mainstring, substring ' assigning value to mainstring mainstring = "Did you attend the party yesterday?" ' extracting the substring from the right side substring = Mid(mainstring, 5) ' print the extracted value Debug.Print substring End Sub
The entire text/string starting from the 5th character of the mainstring has been printed as the output.
Example 4
What will happen if we provide a starting position greater than the length of the string?
In this example, the length of the mainstring is 35 including the question mark. We will try to fix the starting position as 37 in the mid function.
This time we will print the substring in a message box instead of printing on the immediate window as we are expecting the output to be visibly blank.
Sub substring_demo() ' declare variables Dim mainstring, substring ' assigning value to mainstring mainstring = "Did you attend the party yesterday?" ' extracting the substring from the right side substring = Mid(mainstring, 37) ' print the extracted value MsgBox substring End Sub
The output is a blank value as expected.
Conclusion
The three categories of substring functions discussed in this article are of much use in the programming language to decide or control the flow of the program. In real-time, it can validate input strings like email id, phone number, etc. In email addresses, the domain (yahoo.com, @gmail.com) can be checked using the right function, and in phone numbers, the left function can be used to check the area/country code.
One drawback which I find here is that we can check the existence of a substring within a main string only if we know the position at which it resides. If not, we will have to create a loop to check the existence of the substring at every next position of the main string. There is no straightforward method to achieve this.