VBA Left Function

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.

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:

• We can extract the LEFT function only from the left.
• VBA Instr function finds the position of the supplied character in the string.