VBA Left Function

Excel VBA Left

VBA Left is also one of the worksheet functions categorized under the text functions which is 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 functionsThe Excel FunctionsExcel functions help the users to save time and maintain extensive worksheets. There are 100+ excel functions categorized as financial, logical, text, date and time, Lookup & Reference, Math, Statistical and Information functions.read more 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 excel LEFT functionExcel LEFT FunctionThe left function returns the number of characters from the start of the string. For example, if we use this function as =LEFT ( "ANAND",2), the result will be AN.read more, then VBA LEFT function is exactly the same. It can extract the characters from the left side of the string or value given by the user.

VBA Left Function

You are free to use this image on your website, templates etc, Please provide us with an attribution linkHow to Provide Attribution?Article Link to be Hyperlinked
For eg:
Source: VBA Left Function (wallstreetmojo.com)

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

VBA Left

It has two arguments.

How to use Excel VBA Left Function?

You can download this VBA Left Function Template here – VBA Left Function Template

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 left using the LEFT function in VBA.

Step 1: Create a macro name and Define variable as 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 MsgBoxVBA MsgBoxVBA 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.

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. We just 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

Now, if you run this code, we will get the value in cell A1.

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. Assume below is the list of values you have in your excel sheet.

VBA Left Function Example 2

In each case, first name characters are different from one to another; we cannot directly specify the number of characters we needed from each name.

This is where the beauty of other functions will come into the picture. In order to supply the number of characters dynamically, we can use the “VBA Instr” function.

In the above set of names, we need all the characters from the left until we reach space character. So Instr function can return those many characters.

Step 1: Similarly, 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 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, don’t enter the length of the characters manually. 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 the one name, but we have several other names as well. We cannot write 100 lines of code to extract, then how do we extract?

This is where the beauty of loops comes into the picture. Below is the loop code, which can eliminate all the unwanted steps and can 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: Instr function returns space character as well, 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

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

Recommended Articles

This has been a guide to VBA Left Function. Here we learn how to use Left Function to extract the characters from the left side of the string along with some examples and a downloadable excel templateBelow are some useful excel articles related to VBA –

  • 3 Courses
  • 12 Hands-on Projects
  • 43+ Hours
  • Full Lifetime Access
  • Certificate of Completion
LEARN MORE >>