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 11^th 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 5^th 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.