How to Use the Left Function in VBA in Excel (2 Examples)

One of the most important and widely used functions that we use while working with VBA in Excel is the Left function of VBA. It takes a value as the input and returns a given number of characters from the left of the value as output. Today in this article, I’ll show you how you can use the Left function of VBA with proper examples and illustrations.

It returns the first 5 characters from the name “Frank Lampard”.

Download Practice Workbook

Download this practice book to exercise the task while you are reading this article.

Introduction to the Left Function of VBA in Excel

⧭ Function Objective:

The Left function takes a value as the input and returns a given number of characters from the left of the value as the output. The value can be a string, a number, or even a boolean.

For example, if you insert: Left(“Frank Lampard”,5), you will get Frank (String).

If you insert: Left(12345,2), you will get 12 (Number).

Also if you insert: Left(True,2), you will get Tr (Boolean).

⧭ Syntax:

The Syntax of the Left function of VBA is:

⧭ Arguments:

⧭ Return Value:

Returns a given number of characters from the left of a given value.

2 Examples of the Left Function of VBA in Excel

Let’s explore the Left function of VBA in detail with a few examples.

1. Separating the First Name from a Name Using the Left Function of VBA in Excel

Here we’ve got a data set with the IDs and Names of some employees of a company called Saturn Group.

Now we’ll develop a User-Defined Function using the Left function of VBA that’ll return the first names of the employees.

The VBA code will be:

⧭ VBA Code:

Function First_Name(Name)

Number_of_Characters = InStr(Name, " ") - 1

First_Name = Left(Name, Number_of_Characters)

End Function

Note: This code creates a function called First_Name.

⧭ Output:

Run this function in any cell of your worksheet with a name as the argument.

Here, in cell D4, we’ve entered the formula:

It has returned the first name of the name in cell C4, Mark.

Then you can drag the Fill Handle to extract out the first names of all the employees.

⧭ Explanation of the Code:

• First, we declare a function called First_Name that takes any name as the input by the line Function First_Name(Name).
• Then we find out the number of characters to be extracted from the left to get the first name using the InStr function of VBA by the line Number_of_Characters = InStr(Name, ” “) – 1. (We have to extract the number of characters before the space. For Mark Orwell, it’s 4.).
• Then the most important line. We extract the number of characters from the left using the Left function of VBA to get the first name by the line First_Name = Left(Name, Number_of_Characters).
• Finally, we conclude the function by the line End Function.

Similar Readings:

• How to Return a Value in VBA Function (Both Array and Non-Array Values)
• Use VBA DIR Function in Excel (7 Examples)
• How to Call a Sub in VBA in Excel (4 Examples)
• Use TRIM Function in VBA in Excel (Definition + VBA Code)

2. Pulling out the User Name of an Email Address Using the Left Function of VBA in Excel

Here we’ve added one new column with the data set that contains the Email IDs of the employees.

Now we’ll develop a function using the Left function of VBA that will pull out the user names from the Employee IDs.

You can use the following VBA code:

⧭ VBA Code:

Function User_Name(Email)

Number_of_Characters = InStr(Email, "@") - 1

User_Name = Left(Email, Number_of_Characters)

End Function

Note: This code creates a function called User_Name.

⧭ Output:

Run this function in any cell of your worksheet with a name as the argument.

Here, in cell E4, we’ve entered the formula:

It has returned the user name of the email address in cell D4, M.Orwell.

Then you can drag the Fill Handle to extract out the user names of all the Email IDs.

⧭ Explanation of the Code:

• First, we declare a function called User_Name that takes any email address as the input by the line Function User_Name(Email).
• Then we find out the number of characters to be extracted from the left to get the user name using the InStr function of VBA by the line Number_of_Characters = InStr(Email, “@”) – 1 (We have to extract the number of characters before the symbol @. For [email protected], it’s 8.).
• Then the most important line. We extract the number of characters from the left using the Left function of VBA to get the user name by the line User_Name = Left(Email, Number_of_Characters).
• Finally, we conclude the function by the line End Function.

Things to Remember

• The Left function returns a given number of characters from not only a string but also from a number or a boolean value.

For example, Left(1234,2) will return 12.

And Left(False,3) will return Fal.

• The 2nd argument length must be a number. It can be either integer or fraction but must be a number.

If it’s a fraction, then the Left function automatically converts it to the nearest integer.

For example, Left(“Excel”,1.3)=Left(“Excel”,1)=E

Again, Left(“Excel”,1.6)=Left(“Excel”,2)=Ex

Conclusion

Using these methods, you can use the Left function of VBA in Excel to develop Macros and User-Defined functions. Do you have any questions? Feel free to ask us.

Further Readings

• How to Use VBA UCASE Function in Excel (4 Examples)
• Use MsgBox Function in Excel VBA (A Complete Guideline)
• Use LCase Function in VBA in Excel (With 4 Examples)
• Use VBA SPLIT Function in Excel (5 Examples)