Return to VBA Code Examples

VBA Left (& Left$) Function – Extract Text From Left

This tutorial will demonstrate how to use the Left VBA Function.

Left Function

The VBA Left function returns the first n characters from a string.

VBA Left Function n First Characters

The VBA Left function returns the first n characters from a string:

VBA Left Function n First Characters in a Variable

As shown above, you can define a string simply by entering text surrounded by quotation marks. But the LEFT Function will also work with string variables. These examples will extract the first n characters from a string variable.

VBA Left Function n First Characters in a Cell

Strings can be defined in VBA code but also you can use values from cells. Read the value of a cell, keep it in a string variable, and extract n first characters from that Worksheet Cell value.

left cell value vba

 

 

 

 

VBA Left Function Trim off the Last Letter

To remove letters from the end of a string, use the LEFT Function along with the LEN Function.

The VBA LEN function counts the number of characters in a string:

By combining the functions, we can remove a certain number of characters from the end of the string:

VBA Left to Extract First Name

To extract the first name from a string with a full name, use the Left Function along with the Instr Function.

The VBA Instr function searches for a substring inside a string and returns the position number of the substring.

By combining the functions, we can extract the first word from a phrase:

VBA Coding Made Easy

Stop searching for VBA code online. Learn more about AutoMacro - A VBA Code Builder that allows beginners to code procedures from scratch with minimal coding knowledge and with many time-saving features for all users! automacro

Learn More!!

VBA Left$ Function vs Left Function

Both the Left and the Left$ function will return the same answer if the variable where the data is being returned to is declared as a string. However, if the variable has been declared as a variant, then the Left function is allowed to return NULL values whereas the Left$ function can only return string values.

If, for example, you are truing to return some data from an Access query, but the data returned is a NULL value, the Left$ function will return an error.

Have a look at the code below:

leftleft string

In the above example, even though our variable has been declared as a variant, the Left$ function prevents the variable being populated with the NULL value.

In the code below however, the variable is populated with the NULL value.

left left populate variant