Excel VBA SubString

Excel VBA SubString is a very useful type of function in VBA which is used to slice and dice a data in a form of string. But in worksheet functions, we have three substring functions which are Left-right and mid function while in VBA we have Left-right mid and split functions as substring functions. As the name suggests itself substring function in VBA divides a string into multiple SubStrings. Also as explained above in VBA there are multiple VBA Substring functions. In this article, we will learn how to use these substring functions separately with examples. Before moving on with the examples first let us learn the syntax of these functions and what argument these functions take as input.

Syntax of Excel VBA SubString

Following are the different syntax:

Syntax of Left SubString Function:

Text string is the string we provide as input while the length is the number of characters we want from the input string.

Example: If we have a string as ANAND and want AN as substring the code will be

Left (“ANAND”,2)

Syntax of Right SubString Function:

Text string is the string we provide as input while the length is the number of characters we want from the input string.

Example: If we have a string as ANAND and use the same code as above the result will be

Right (“ANAND”,2)

This gives ND as a result.

Syntax of Mid SubString Function in VBA:

Text string is the string we provide as input and Start position is the position where we want the character to start for extraction while the length is the number of characters we want from the input string.

Example: We have a string as COMPUTER and we want to PUT as the substring then the code will be as follows:

MID (“COMPUTER”,4,3)

Syntax of Split SubString Function:

Expression As String: This is a mandatory argument in the SPLIT function. It is basically the input string we provide.
Delimiter: This is an optional argument. It is the specific delimiter that divides the string but by default, space is considered as default delimiter.
Limit: This is also an optional argument. Limit means the maximum number of parts we want to do of a string. But again if we do not provide a limit to the function VBA treats it as default -1. This concludes that the string will be broken apart each time there is a delimiter in the string.
Compare: This final argument is also an optional argument. Compare is a compare method which is one of the two below:

Either it is 0 which means SPLIT will perform a binary comparison which means every character should match itself.
Or it can be 1 which means the SPLIT function will do a textual comparison.

Split Function is the trickiest and most useful among them all the substring functions above. All the other three substring functions use one string as input while Split function uses an array of string.

For example if I write Split(“I AM A GOOD BOY”) will divide the string as separately (each word as separate). Now let us use these substrings functions in examples.

Note: To use VBA we need to have the developer’s tab enabled from the file tab under the Options section.

How to Use SubString Functions in VBA?

We will learn how to use the SubString function in Excel by using the VBA Code.

You can download this VBA SubString Excel Template here – VBA SubString Excel Template

Example #1

Left Substring Function in VBA. Let us use the first substring function in VBA. For this, follow the below steps:

Step 1: Go to the developer’s Tab and click on Visual Basic to open VB Editor.

Step 2: Insert a new module inside Visual Basic Editor (VBE). Click on Insert tab > select Module.

Step 3: Declare a sub-function to start writing the code.

Code:

Sub Sample()

End Sub

Step 4: Declare two strings one to take input from the user and another to store the value of the result.

Code:

Sub Sample()

Dim A, B As String

End Sub

Step 5: Take the input from the user for the input string using the input box function.

Code:

Sub Sample()

Dim A, B As String
A = InputBox("Enter a String", "Single String")

End Sub

Step 6: In B variable store the value from the left function up to the third place.

Code:

Sub Sample()

Dim A, B As String
A = InputBox("Enter a String", "Single String")
B = Left(A, 3)

End Sub

Step 7: Use Msgbox function to display the final result.

Code:

Sub Sample()

Dim A, B As String
A = InputBox("Enter a String", "Single String")
B = Left(A, 3)
MsgBox B

End Sub

Step 8: Now run the above code by pressing the F5 key. and Write input String as ANAND.

Step 9: When we press OK we see the result of the left substring function.

ANA is the three characters from the left of the string.

Example #2

RIGHT Substring function in VBA. For this, follow the below steps:

Step 1: In the same module declare another sub-function to start writing the code for the right substring function.

Code:

Sub Rightsub()

End Sub

Step 2: Declare two variables A and B as string.

Code:

Sub Rightsub()

Dim A, B As String

End Sub

Step 3: Take the input from user and store the value in A variable.

Code:

Sub Rightsub()

Dim A, B As String
A = InputBox("Enter a String", "Single String")

End Sub

Step 4: Use the Right function on the string to the third place and store the value in B variable.

Code:

Sub Rightsub()

Dim A, B As String
A = InputBox("Enter a String", "Single String")
B = Right(A, 3)

End Sub

Step 5: Use Msgbox function to display the value of B.

Code:

Sub Rightsub()

Dim A, B As String
A = InputBox("Enter a String", "Single String")
B = Right(A, 3)
MsgBox B

End Sub

Step 6: Run the code and enter the input string as “MOTHER”.

Step 7: Press OK to see the result.

HER is the three characters from the right of the string.

Example #3

MID Substring Function in VBA. For this, follow the below steps:

Step 1: In the same module declare another sub-function to start writing the code for Mid function.

Code:

Sub MIDsub()

End Sub

Step 2: Declare two variables A and B as String.

Code:

Sub MIDsub()

Dim A, B As String

End Sub

Step 3: Take input from the user and store the value in Variable A.

Code:

Sub MIDsub()

Dim A, B As String
A = InputBox("Enter a String", "Single String")

End Sub

Step 4: Use Mid function with starting position as 4 and length as 3 stores the value in B and display it using Msgbox function.

Code:

Sub MIDsub()

Dim A, B As String
A = InputBox("Enter a String", "Single String")
B = Mid(A, 4, 3)
MsgBox B

End Sub

Step 5: Run the above code and give COMPUTER as input.

Step 6: Press OK to see the final result.

The substring PUT starts from 4^th position and we have successfully extracted three characters.

Example #4

VBA Split SubString Function. For this, follow the below steps:

Step 1: In the same module declare a sub-function to start writing the code for sub-function.

Code:

Sub SplitSub()

End Sub

Step 2: Declare two Variables A as string and B as String array and take input string from the user and store it in Variable A.

Code:

Sub SplitSub()

Dim A As String
Dim B() As String
A = InputBox("Enter a String", "Separate with Commas")

End Sub

Step 3: Use the Split SubString function and store its value in Variable B.

Code:

Sub SplitSub()

Dim A As String
Dim B() As String
A = InputBox("Enter a String", "Separate with Commas")
B = Split(A, ",")

End Sub

Step 4: Use For loop to display every SubString in a single line.

Code:

Sub SplitSub()

Dim A As String
Dim B() As String
A = InputBox("Enter a String", "Separate with Commas")
B = Split(A, ",")
For i = LBound(B) To UBound(B)
strg = strg & vbNewLine & "String Number " & i & " - " & B(i)
Next i
MsgBox strg

End Sub

Step 5 Run the above code and give I,AM,GOOD,BOY as input.

Step 6: Press OK to see the result.

We used “,” as a delimiter in the above example.

Conclusion

Like worksheet substring functions VBA also has substring functions. They are Left Right Mid and Split Functions. Basically Substring functions divide a string or an array of string into multiple substrings. If we want a substring from the left of the string we use Left function or right in the opposite case. If we want a middle character of any given string we use MID functions. Also if we have an array of strings we use split functions.

Things to Remember

There are few things which we need to remember about Substring functions in VBA:

It is similar to worksheet substring functions.
Substring functions divide a given string into substrings.
If we have an array of strings we use split functions.
Only the input string in split function is mandatory while the others are optional.

Quiz Result
Total Questions	Correct Answers	Wrong Answers	Percentage

Excel VBA SubString

Syntax of Excel VBA SubString

How to Use SubString Functions in VBA?

Example #1

Example #2

Example #3

Example #4

Conclusion

Things to Remember

Recommended Articles

About Us

Apps

Resources

Certification Courses