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.

Left Function of VBA in Excel

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

Output of Excel VBA Left Function


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:

=Left(String,Length as Long)

Arguments:

Argument Required/Optional Explanation
String Required The string from the left of which the given number of characters will be returned. It’s not necessary for it to be a string always. It can be a number or even a boolean value.
Length Required The number of characters that will be returned. Must be a number (Can be both integer or fraction, but must be a number).

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.

Data Set to Use Excel VBA Left Function

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.

Code with Excel VBA Left Function

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:

=First_Name(C4)

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.

Function Developed with Excel VBA Left Function

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:


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.

New Data Set to Use Excel VBA Left Function

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:

=User_Name(D4)

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

Output of Using Function with Excel VBA Left Function

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

Rifat Hassan

Rifat Hassan

Hello! Welcome to my profile. Here I will be posting articles related to Microsoft Excel. I am a passionate Electrical Engineer holding a Bachelor’s degree in Electrical and Electronic Engineering from Bangladesh University of Engineering and Technology. Besides academic studies, I always love to keep pace with the revolution in technology that the world is rushing towards day by day. I am diligent, career-oriented, and ready to cherish knowledge throughout my life.

We will be happy to hear your thoughts

Leave a reply

ExcelDemy
Logo