Left, Right

The Left and Right functions are used to chop characters from a string. Use Left to chop characters from the start of the string; use Right to chop characters starting from the end of the string. In between the round brackets of Left and Right you type the number of characters you want to chop. If you miss out the number of characters to chop then Left and Right extract just one character from the start or end of the string. Some example might clear things up.

Create a new Sub and try this code out:

Dim Email As String

Email = "myaddress@myisp.com"

MsgBox Left(Email, 9)

MsgBox Right(Email, 9)

The first two lines just set up a String variable and place an email address in the Email variable. The third line is a message box that uses the Left function:

MsgBox Left(Email, 9)

When you run the code you'll see that the message box displays the first 9 characters of the email address, everything to the left of the @ sign.

The fourth line is this:

MsgBox Right(Email, 9)

The Right function will display 9 characters starting from the final character in the email address, everything to the right of the @ sign.

That's fairly straightforward, we're sure you'll agree. But now for a more complex use of Left and Right.

Suppose you have a full name in cell A1 in this format:

David Gilmour

However, suppose you want to have the surname first then the first name. This format:

Gilmour, David

You can use Left, Right and the InStr Functions to achieve this.

Create a new Sub and call it LastFirst . Now set up four variables, three Strings and an Integer:

Dim FullName As String
Dim FirstName As String
Dim LastName As String
Dim SpacePos As Integer

Place the full name in the FullName variable:

FullName = "David Gilmour"

Now use InStr to locate the position of the space in the name:

SpacePos = InStr(FullName, " ")

To get just the first name you can start at the beginning of the full name and go up to the SpacePos minus 1:

FirstName = Left(FullName, SpacePos - 1)

The reason to deduct 1 from the SpacePos variable is because the InStr function will return the position of the space, a value of 6 for our name. The final character of the first name, however, is 1 less than this, as David only has 5 characters in it.

To get the last name, we need something slightly different. The starting position is the length of the full name minus the length of the first name. This will get us the correct number of characters to grab starting from the right of the name. The code is this:

LastName = Right(FullName, Len(FullName) - Len(FirstName))

So as the final parameter of Right we have this:

Len(FullName) - Len(FirstName))

This uses the Len function to get the length of the FullName and FirstName variables.

Finally, display the results in a message box:

MsgBox (LastName & ", " & FirstName)

We have the LastName variable first and then the FirstName . The two are separated by concatenation symbols (&). We also need a comma, and we have this in double quotes so that VBA sees it as text. So we're saying, "Join together the Last Name, then a comma, then the First Name".

The whole of your code, then, should look like this:

Run your code and you should see this message box:

Click OK to return to your code. Now type a new name. Change this line, for example:

FullName = "David Gilmour"

to this:

​ FullName = "William Shakespeare"

Run your code again and the message box will display this:

That final exercise illustrates that the more string functions you know and are comfortable with the more you can achieve in your programming.

(NOTE: There is an easier way to do that exercise by using the Split function. But this uses something called an array. You haven't covered arrays yet, which is why we're doing it the hard way!)