VBA Left Function

Last Updated :

21 Aug, 2024

Blog Author :

N/A

Edited by :

Aaron Crowe

Reviewed by :

Dheeraj Vaidya

Table Of Contents

arrow

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.

VBA Left Function

The syntax of the LEFT function is the same as in the worksheet function.

VBA Left

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.

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
VBA Left Function Example 1

Step 2: Now, assign a value to this variable.

Code:

Sub Left_Example1()

    Dim MyValue As String

    MyValue =

End Sub
VBA Left Function Example 1-1

Step 3: Open the LEFT function.

Code:

Sub Left_Example1()

    Dim MyValue As String

    MyValue = Left(

End Sub
VBA Left Function Example 1-2

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
VBA Left Function Example 1-3

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
VBA Left Function Example 1-4

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
VBA Left Function Example 1-5

Step 7: Run the macro using the F5 key or manually through a run option to get the result in a message box.

VBA Left Function Example 1-6

Output:

VBA Left Function Example 1-7

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
VBA Left Function Example 1-8

We will get the value in cell A1 if you run this code.

VBA Left Function Example 1-9

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.

VBA Left Function Example 2

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
VBA Left Function Example 2-1

Step 2: Assign the value to the variable through the LEFT function.

Code:

Sub Left_Example2()

    Dim FirstName As String

    FirstName = Left(

End Sub
VBA Left Function Example 2-2

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
Visual Basic Application Example 2-3

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
Visual Basic Application Example 2-4

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
Visual Basic Application Example 2-5

Step 6: Run this code manually, or through F5, we will get the first name.

Visual Basic Application Example 2-6

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
Visual Basic Application Example 2-7
Note: The Instr function also returns space character, so we need to minus 1 from the Instr result.

If you run this code, we will get the first name values.

Visual Basic Application Example 2-8

Output:

Example 2-9

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: -