Last week we worked on how to strip off the characters from the left fo a string of characters using the **LEFT** function, and that worked a treat, *for part of the problem*. Now we need to figure out how to strip of a *variable* number of characters from the right side. The problem here is that we don’t know how many characters we want to strip off. Look at these examples:

1The name of a book

2The name of a movie

3The name of a podcast

In each of these examples the number of characters that we need to strip off varies. In example one it is 15 , example two is 16, and example 3 is 18. Unless we are prepared to change the function for *each* cell, we need to figure out a way to calculate the number of characters we want to strip, in an automated way.

So what *do* we know? We know that in these three examples, the number of characters we want to strip off is the total number of characters minus one character (the number).

1The name of a book

The total length of the text is 15 (14 for “The name of a book” and 1 for “1”).

There is a function called **LEN** (for length) that will tell us how many characters are in a cell.

Using our example, and assuming that this text is in the cell **A1**, the function would read:

**=LEN(A1)**

The value returned is 15.

Now we can use the value from the **LEN** function to feed into the **RIGHT** function.

Just as the **LEFT** function starts from the left side, the **RIGHT** function starts from the right side.

Again, using our example, and assuming our text is in cell A1:

**=RIGHT(A1, 15-1)**

We have told Excel to look at cell A1, and strip off, *starting from the right*, 14 (15-1) characters.

So far so good. For the final step, we need to include the results of the **LEN** function without manually typing it in.

There are several ways to do this, but to keep it simple, I am going to make the assumption that the **LEN** function is in cell D1. (The text is in A1, the results of the **LEFT**function are in B1, and the results of the **RIGHT** function will be in C1.)

We will refer to the results of the **LEN** function in our **RIGH**T function:

**=RIGHT(A1,D1-1)**

As you copy down the function to lower rows, the A1 reference will update to A2, and the D1 reference will update to D2, and so on. As long as you only have *one* character numbers to exclude (1-9), this formula will be fine. Once you have 10 to 99, you will need to change the ‘-1’ to ‘-2’.

Check out the video below to see this all in action. Meanwhile, keep soaring…