Remove trailing slash from url

The goal is to remove the forward-slash ("/") from the URLs in column B when it is present as the last character. When a URL does not end with a forward slash ("/") the original URL should be returned without modification. Despite the fact that Excel offers many functions designed to work with text strings, there is not an entirely straightforward way to solve this problem. The simplest method is to use a formula based on the LEFT function with help from LEN and RIGHT.

LEFT formula solution

The solution shown in the worksheet shown is based on the LEFT function. The formula in cell D5 is:

=LEFT(B5,LEN(B5)-(RIGHT(B5)="/"))

The tricky part of this formula is that it is conditional. If a URL ends in a forward slash ("/") it is removed. If a URL does not end with a forward slash, it is returned unchanged. This could be accomplished with the IF function inside the formula, as explained later in the article. However, the formula above uses a different approach which takes advantage of the fact that Excel will convert TRUE to 1 and FALSE to zero when prompted by a math operation. This technique is sometimes referred to as Boolean logic and is a way to make parts of a formula conditional without using the IF function.

Video: Boolean algebra in Excel

The basic idea

At the core, this formula uses the LEFT function to return text starting from the left. If we just wanted to always remove the last character from a text string (regardless of the character) we could use a simple formula like this:

=LEFT(B5,LEN(B5)-1)

Here, the LEN function is used to get a total count of characters in cell B5, from which we subtract 1. The result is returned to LEFT as the num_chars argument, and LEFT then returns all but the last character. This works great, but we need to remove the last character only when it is a forward slash. This means that we need to add conditional logic that will return LEN(B5)-1 if there is a trailing slash and LEN(B5) if there is not a trailing slash.

Conditional logic

To work out the number of characters that should be returned conditionally, the formula uses this expression:

LEN(B5)-(RIGHT(B5)="/")

Here, total characters are calculated with the LEN function as before. From this result, the result of the following expression is subtracted:

RIGHT(B5)="/"

This expression uses the RIGHT function to extract the last character on the right, then tests the result to see if it is a forward slash ("/"). If the expression above returns TRUE, the math operation of subtraction will cause Excel to convert the TRUE to 1. If the expression returns FALSE, Excel will convert FALSE to zero. Therefore, when a URL ends in a forward slash "/", we subtract 1 from the count returned by LEN and RIGHT returns all but the last character. However, when a URL does not end in "/", we subtract zero from the count returned by LEN and RIGHT returns the entire string unchanged.

Note: Normally, we would also give RIGHT the number of characters to extract, which is called num_chars. However, num_chars will default to 1 if not provided, so we omit it here since we only want the last character.

With the IF function

The conditional logic trick above is clever, but the resulting formula is a bit cryptic. A more traditional formula below uses the IF function to accomplish the same thing:

=LEFT(B5,IF(RIGHT(B5)="/",LEN(B5)-1,LEN(B5)))

Although slightly longer, the conditional logic in this formula is easier to read, and the formula returns the same result in the end. Select the best option to use based on your personal preference.