Excel/VBA Right Function

What is the Right function in VBA?

The Right function returns a specified number of characters from the right of the string.

It takes two arguments – both are required:

Right(string, length)

An example of using Right function with a string in VBA

Before showing you an example of Excel sheet data, let us have a look at a string variable in VBA.

We will use the following string:

“VBA is cool”

In the program below, we will use four value for the length argument:

Sub right_ex()

Dim str As String

str = "VBA is cool"

MsgBox ("Last four letters: " & Right(str, 4))

End Sub

Output:

Using Excel Sheet data with Right function

In this example, we will use an Excel cell in the Right function to get the last eight letters from the text.

The source text and returned text are shown in the image after the code.

VBA code:

Sub right_ex()

MsgBox ("Last eight letters in D8 cell: " & Right(Range("D8"), 8))


End Sub

Result:

Using Range of cells with the Right function example

In this program, we will use a range of B2 to B11 cells to demonstrate the use of the Right function in VBA.

The last three characters of each Product Name will be written to the F column’s cells.

For that, we will use a For..Each loop as follows:

Sub right_ex()

Dim rng As Range

Dim x


Set rng = Range("B2:B11")

x = 2


For Each cell In rng

    Range("F" & x).Value = Right(cell, 5)

    x = x + 1

Next

End Sub

Result:

What if we use Right with a Numeric or Currency column?

As Price is a currency column in our sample sheet. Let us get the last two characters/numbers from the price column by using the Right function. The range in that case, is C2:C11:

Sub right_ex()

Dim rng As Range

Dim x

Set rng = Range("C2:C11")

x = 2

'Get last two cahracters from price column

For Each cell In rng

    Range("F" & x).Value = Right(cell, 2)

    x = x + 1

Next


End Sub

Result: