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 LEFTfunction 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 RIGHT 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…