Excel VBA SubString
A string is nothing but a series of characters. The characters could be alphabets, numbers, special characters, and combinations.
Often in Excel, when we work with data, which is the string, we need to get only the portion of the string to facilitate our purpose. We may not need the full string to use, but we need only the part of the string for our use. For example, if you have the name “Sachin Tendulkar,” you may need only the first part of the name, i.e., only “Sachin.” It is known as the SubString of the string in Excel VBA. We have built-in functions under the TEXT function in the excelTEXT Function In ExcelTEXT function in excel is a string function used to change a given input to the text provided in a specified number format. It is used when we large data sets from multiple users and the formats are different.read more category to deal with these strings.
This article will discuss getting the substring from the full string in VBAString In VBAString functions in VBA do not replace the string; instead, this function creates a new string. There are numerous string functions in VBA, all of which are classified as string or text functions.read more.
You are free to use this image on you website, templates, etc., Please provide us with an attribution linkHow to Provide Attribution?Article Link to be HyperlinkedFor eg:Source: VBA SubString (wallstreetmojo.com)
How to Use SubString Functions in VBA?
To extract the substring from the string, we have some of the built-in text functions, and some of the important functions are LEFT, RIGHT, INSTR and MID in excelMID In ExcelThe mid function in Excel is a text function that finds strings and returns them from any mid-part of the spreadsheet. read more. The function Instr will serve as the supporting function for the other three functions.
We will see how to use these functions to extract the substrings practically. Please read the below examples to understand them.
Example #1 – Using Left Function
If you have the full name as “Sachin Tendulkar” and need only the first name extracted as the substring, use the following code to get the same.
Step 1: Create a macro name and define two variables as a string.
Code:
Sub SubString_Example1()
Dim FullName As String Dim FirstName As String
End Sub
Step 2: Now, assign the name “Sachin Tendulkar” to the variable FullName.
Sub SubString_Example1()
Dim FullName As String Dim FirstName As String
FullName = “Sachin Tendulkar”
End Sub
Step 3: The variable FullName holds the value of “Sachin Tendulkar.” We need to extract the Excel VBA substring of the first name from the full name. So, assign the value for the variable FirstName through the LEFT function.
Sub SubString_Example1()
Dim FullName As String Dim FirstName As String
FullName = “Sachin Tendulkar” FirstName = Left(
End Sub
Step 4: The VBA VBA Excel VBA Left is an inbuilt worksheet text function that facilitates the user to extract a certain number of characters (denoted as N) from the left side of a string. It helps in pulling the leftmost substring from a particular string in the data set.read more LEFT function’s first argument is String. That is what is the full value or full string. In this example, our full value or string is “Sachin Tendulkar,” assigned to the variable FullName.
So supply variable FullName as the argument.
Sub SubString_Example1()
Dim FullName As String Dim FirstName As String
FullName = “Sachin Tendulkar” FirstName = Left
End Sub
Step 5: The next argument is how many characters we need from the supplied string. So, in this case, we need the first name “Sachin,” so totally, we need 6 characters from the left side.
Sub SubString_Example1()
Dim FullName As String Dim FirstName As String
FullName = “Sachin Tendulkar” FirstName = Left(FullName, 6)
End Sub
Step 6: Now, show the result in a message box in VBAMessage Box In VBAVBA MsgBox function is an output function which displays the generalized message provided by the developer. This statement has no arguments and the personalized messages in this function are written under the double quotes while for the values the variable reference is provided.read more.
Sub SubString_Example1()
Dim FullName As String Dim FirstName As String
FullName = “Sachin Tendulkar” FirstName = Left(FullName, 6)
MsgBox FirstName
End Sub
Step 7: Run the macro and see the first name as a substring in the message box.
Example #2 – Get SubString from the Right
Like how we have extracted the substring from the left similarly, we can also extract it from the right. Again, take the same name as an example.
Step 1: Define two variables as String.
Sub SubString_Example2()
Dim FullName As String Dim LastName As String
End Sub
Step 2: As usual, assign the value to the variable FullName as “Sachin Tendulkar.”
Sub SubString_Example2()
Dim FullName As String Dim LastName As String
FullName = “Sachin Tendulkar”
End Sub
Step 3: Now, for the variable LastName, assign the value through the RIGHT excelRIGHT ExcelRight function is a text function which gives the number of characters from the end from the string which is from right to left. For example, if we use this function as =RIGHT ( “ANAND”,2) this will give us ND as the result.read more function.
Sub SubString_Example2()
Dim FullName As String Dim LastName As String
FullName = “Sachin Tendulkar” LastName = Right(
End Sub
Step 4: String is our FullName, so supply the variable.
Sub SubString_Example2()
Dim FullName As String Dim LastName As String
FullName = “Sachin Tendulkar” LastName = Right(FullName,
End Sub
Step 5: Length is how many characters we need from the right side. We need 9 characters on the right side.
Sub SubString_Example2()
Dim FullName As String Dim LastName As String
FullName = “Sachin Tendulkar” LastName = Right(FullName, 9)
End Sub
Step 6: Show this value in the message box.
Sub SubString_Example2()
Dim FullName As String Dim LastName As String
FullName = “Sachin Tendulkar” LastName = Right(FullName, 9)
MsgBox LastName
End Sub
Step 7: Run the macro. We will see the last name in the message box.
Example #3 – Using Instr Function
In the above examples, we had only one name. So, we have directly supplied how many characters we need from the left and right. But in the case of many names, first name and name characters are not the same. It will differ from name to name. So, in those cases, we cannot supply the number of characters directly so that we can use the function Instr.
The Instr function will return the supplied character position in the string. For example, look at the below code.
Sub SubString_Example3()
Dim Position As String Position = InStr(1, “Sachin”, “a”)
MsgBox Position
End Sub
InStr(1, “Sachin,” “a”), this will identify the position of the letter “a” as the first appearance in the string “Sachin.” In this case, the letter “a” is in the second position. So, we will get 2 as a result in the message box.
Like this, we can use the Instr function to find the space character between the first name and last name.
For example, look at the name below in the Excel sheet.
We can extract the substrings using the LEFT, RIGHT, and Instr functions. For example, below is the code to extract the first name.
Sub FirstName() Dim K As Long Dim LR As Long LR = Cells(Rows.Count, 1).End(xIUp).Row
For K = 2 To LR Cells(K, 2).Value = Left(Cells(K, 1).Value, InStr(1, Cells(K, 1).Value, “”) - 1) Next K
End Sub
Run the macro and see the first name as a substring in the message box.
Use the below code to extract the last name as a substring.
Sub LastName() Dim K As Long Dim LR As Long LR = Cells(Rows.Count, 1).End(xIUp).Row
For K = 2 To LR Cells(K, 3).Value = Right(Cells(K, 1).Value, Len(Cells(K, 1)) - InStr(1, Cells(K, 1).Value, “”)) Next K
End Sub
Run the macro. We will see the last name in the message box.
Recommended Articles
This article has been a guide to VBA Substring. Here, we learn how to extract substring using the Left, Right, and Instr VBA function, examples, and a downloadable Excel template. Below you can find some useful Excel VBA articles: –
- Excel VBA New LineVBA Count FunctionSubstring Function in ExcelExcel VBA Replace String
