EDUCBA

Home VBA VBA Resources VBA String and Text Functions VBA SubString
 

VBA SubString

Madhuri Thakur
Article byMadhuri Thakur

VBA SubString

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:

Watch our Demo Courses and Videos

Valuation, Hadoop, Excel, Mobile Apps, Web Development & many more.

Syntax of Left SubString Function:

Syntax of Left

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:

Syntax of Right

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:

Syntax of Mid

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:

Syntax of Split

  • 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:
  1. Either it is 0 which means SPLIT will perform a binary comparison which means every character should match itself.
  2. 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.

Developer Tab

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

Insert Module

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

Code:

Sub Sample()

End Sub

Excel VBA SubString Example1-1

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

Excel VBA SubString Example1-2

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

Input Box Example1-3

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

VBA SubString Example1-4

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

VBA SubString Example1-5

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

Excel VBA SubString Example1-6

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

VBA SubString Example1-7

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

VBA SubString Example1-8

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

Code:

Sub Rightsub()

Dim A, B As String

End Sub

VBA SubString Example1-9

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

VBA SubString Example1-10

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

Right function Example1-1

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

VBA SubString Example1-12

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

VBA SubString Example1-13

Step 7: Press OK to see the result.

VBA SubString Example1-14

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

Mid Function Example3-1

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

Code:

Sub MIDsub()

Dim A, B As String

End Sub

VBA SubString Example3-2

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

VBA SubString Example3-3

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

MsgBox Example3-4

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

VBA SubString Example3-5

Step 6: Press OK to see the final result.

VBA SubString Example1-14

The substring PUT starts from 4th 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

VBA SubString Example4-1

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

VBA SubString Example4-2

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

Split Example4-3

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

For Loop Example4-4

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

VBA SubString Example4-5

Step 6: Press OK to see the result.

VBA SubString Example4-6

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.

Recommended Articles

This is a guide to the VBA SubString. Here we discuss how to use the SubString function in Excel VBA along with practical examples and downloadable excel template. You can also go through our other suggested articles –

  1. VBA SendKeys
  2. VBA On Error Goto
  3. VBA Input
  4. VBA LBound