REMOVE TRAILING SLASH FROM URL
If you want to remove the trailing slash from URL in Excel, there is a formula that uses the LEFT function and the LEN function to delete a slash from a path or URL. The procedure for doing this is demonstrated in this article below.
To remove a trailing slash from a path or a URL in Excel using the LEFT and LENS functions, follow the procedure below:
If you need to clean up names that are not in a proper case, you can use a simple formula based on PROPER functions. In the example the formula in C5 is:
Click on Formulas on the menu bar in Excel.
Next, click on Text.
Scroll down the list that displays and click on LEFT.
The dialogue box will display as shown in the example below:
Figure 1. Accessing the LEFT function to remove slash from URL
- In the text field, enter the following:
- B7 is the address of the cell containing the URL from which you wish to remove a trailing slash. In the example below
- Thereafter, click on OK.
It will show you the result displayed in the screenshot below:
Figure 2. Results of remove slash from URL using LEFT and LEN functions
Interpretation of the Result
In the example displayed in Figure 2 above, the list on the left comprises of URLs containing trailing slashes, that is, they each end in an “/”. The list on the right comprises of URLs from which the trailing slashes have been removed using the LEFT and LEN functions in Excel as described above. This has been achieved by using the formula
B7 is the address of the cell containing the URL from which a trailing slash is intended to be removed. You can easily look at the table and verify that the URL has been removed by ph
PROPER,TRIM physically each URL on the left and its corresponding URL on the right.
Another way to remove a trailing slash from a URL is to simply type the formula above into the cell on the right of the corresponding URL from which you wish to remove a trailing slash. Hit the enter key when you finish typing and the result will show.
Irrespective of whether you use the first or second method, you will have to apply it to each individual URL on the left column. That is, for every URL address on the left, you will put the cursor on its corresponding cell on the right and type in the formula indicating the appropriate cell address.
The way the formula functions
What this formula does essentially is to use the LEFT function to enter the URLs beginning from the left. The expression below is used by the formula to dictate the characters that should be entered:
The total number of characters represented by the expression above, the characters represented by the expression below are subtracted:
If the last character in the URL address is a slash, one character is subtracted from the right. However, if the last character is not a slash, nothing is subtracted from the right. The final output is displayed in the column on the right without the slash being retained.