VBA Left Function
Last Updated :
21 Aug, 2024
Blog Author :
N/A
Edited by :
Aaron Crowe
Reviewed by :
Dheeraj Vaidya
Table Of Contents
Excel VBA Left
VBA Left is also one of the worksheet functions categorized under the text functions used in VBA with the application.worksheet method. It returns the specified number of characters from the left part of the string.
Some of the excel functions are integrated with VBA as well. Of all the many text functions, VBA LEFT is one of those functions we use quite often than other formulas.
If you know the excel LEFT function, then the VBA LEFT function is the same. It can extract the characters from the left side of the string or the value given by the user.
The syntax of the LEFT function is the same as in the worksheet function.
It has two arguments.
- String: This is nothing but the value or cell reference. From this value, we are looking to chop the characters.
- Length: How many characters do you want to extract from the supplied string? It should be a numerical value.
Table of contents
How to use Excel VBA Left Function?
Example #1
Assume you have the word “Sachin Tendulkar,” and you want only the first 6 characters from this word. We will show how to extract from the left using the LEFT function in VBA.
Step 1: Create a macro name and define the variable as a string.
Code:
Sub Left_Example1() Dim MyValue As String End Sub
Step 2: Now, assign a value to this variable.
Code:
Sub Left_Example1() Dim MyValue As String MyValue = End Sub
Step 3: Open the LEFT function.
Code:
Sub Left_Example1() Dim MyValue As String MyValue = Left( End Sub
Step 4: The first argument is to tell what is the string or value. Our value here is “Sachin Tendulkar.”
Code:
Sub Left_Example1() Dim MyValue As String MyValue = Left("Sachin Tendulkar", End Sub
Step 5: Length is nothing but how many characters we need from the left. We need 6 characters.
Code:
Sub Left_Example1() Dim MyValue As String MyValue = Left("Sachin Tendulkar", 6) End Sub
Step 6: Show the value in the VBA MsgBox.
Code:
Sub Left_Example1() Dim MyValue As String MyValue = Left("Sachin Tendulkar", 6) MsgBox MyValue End Sub
Step 7: Run the macro using the F5 key or manually through a run option to get the result in a message box.
Output:
Instead of showing the result in a message box, we can store this result in one of the cells in our Excel worksheet. Then, we need to add the cell reference and variable value.
Code:
Sub Left_Example1() Dim MyValue As String MyValue = Left("Sachin Tendulkar", 6) Range("A1").Value = MyValue End Sub
We will get the value in cell A1 if you run this code.
Example #2 - LEFT with Other Functions
In the above case, we have directly supplied the length of the characters we need from the left direction, but this is more suitable for one or two values. For example, assume the list of values you have in your Excel sheet below.
In each case, first-name characters are different. Therefore, we cannot directly specify the number of characters we need from each name.
That is where the beauty of other functions will come into the picture. For example, we can use the “VBA Instr” function to dynamically supply the number of characters.
In the above names, we need all the characters from the left until we reach the space character. So, the Instr function can return those many characters.
Step 1: Start an excel macro name and define a variable as a string.
Code:
Sub Left_Example2() Dim FirstName As String End Sub
Step 2: Assign the value to the variable through the LEFT function.
Code:
Sub Left_Example2() Dim FirstName As String FirstName = Left( End Sub
Step 3: Here, we need to refer to the cell to get the value from the cells. So write the code as CELLE (2,1).Value.
Code:
Sub Left_Example2() Dim FirstName As String FirstName = Left(Cells(2,1).Value, End Sub
Step 4: The next thing is how many characters we need. After applying the LEFT function, do not manually enter the characters' length. Apply the Instr function.
Code:
Sub Left_Example2() Dim FirstName As String FirstName = Left(Cells(2, 1).Value, InStr(1, Cells(2, 1).Value, " ")) End Sub
Step 5: Store this result in the B2 cell. So the code is CELLS (2,2).Value = FirstName.
Code:
Sub Left_Example2() Dim FirstName As String FirstName = Left(Cells(2, 1).Value, InStr(1, Cells(2, 1).Value, " ")) Cells(2, 2).Value = FirstName End Sub
Step 6: Run this code manually, or through F5, we will get the first name.
We got the first name for one name, but we also have several others. So, if we cannot write 100 lines of code to extract, then how do we extract?
It is where the beauty of loops comes into the picture. Below is the loop code, which can eliminate all the unwanted steps and do the job in 3 lines.
Code:
Sub Left_Example2() Dim FirstName As String Dim i As Integer For i = 2 To 9 FirstName = Left(Cells(i, 1).Value, InStr(1, Cells(i, 1).Value, " ") - 1) Cells(i, 2).Value = FirstName Next i End Sub
If you run this code, we will get the first name values.
Output:
Things to Remember
- We can extract the LEFT function only from the left.
- VBA Instr function finds the position of the supplied character in the string.
Recommended Articles
This article has been a guide to VBA Left Function. Here, we learn how to use the Left function to extract the characters from the left side of the string, along with some examples and a downloadable Excel template. Below are some useful Excel articles related to VBA: -