Hello,
I need to remove the final backslash on some URLs I have on a list. Not every cell in the column has a backslash at the end of the cell.
What is the best formula for me to use?
I have attached a worksheet as an example. Please note: The URLs I am using as examples are from the public website, IMDB.
Thank you in advance, for your help.
Best,
Carol
This might work if all URLs follow the same pattern:
=IFNA(TEXTBEFORE(A2,"/",8),A2)
Ali
Enthusiastic self-taught user of MS Excel who's always learning!
Don't forget to say "thank you" in your thread to anyone who has offered you help.
You can reward them by clicking on * Add Reputation below their user name on the left, if you wish.
Forum Rules (updated August 2023): please read them here.
Another option is you have a variable number of /Formula:
Please Login or Register to view this content.
Try this
The old fashion way:
=IF(RIGHT(A2)="/",LEFT(A2,LEN(A2)-1),A2)
Drag down
/LGS
Last edited by LARS GULYAS; 03-13-2024 at 10:28 PM.
Hey, Carol
To remove the final backslash from URLs in Excel, you can use the following formula:
=IF(RIGHT(A1,1)="/",LEFT(A1,LEN(A1)-1),A1)
Here's how it works:
RIGHT(A1,1) extracts the last character from the cell A1 (which should be the backslash).
IF(RIGHT(A1,1)="/",...) checks if the last character is a backslash.
If the last character is a backslash, LEFT(A1,LEN(A1)-1) removes the last character from the cell A1.
If the last character is not a backslash, it returns the original cell value A1.
You can apply this formula to your entire column of URLs. Drag down the formula or double-click the fill handle (the small square at the bottom right corner of the cell with the formula) to fill it down to apply to all cells in the column.
Here's how you can apply it to your worksheet:
Insert a new column next to your list of URLs.
Enter the formula =IF(RIGHT(A2,1)="/",LEFT(A2,LEN(A2)-1),A2) into the first cell of the new column (assuming your URLs are in column A starting from cell A2).
Press Enter to apply the formula.
Drag down the formula to apply it to all cells in the column.
You'll now have your URLs without the final backslash in the new column. You can then copy and paste them back to replace the original URLs if needed.
This formula will effectively remove the final backslash from URLs in your list.
Try also:
=LEFT(A2,LEN(A2)-1)
That will take the last character away even if it isn't a backslash - look at the sample in post #1.
One for luck
=LEFT(A2,LEN(A2)-IF(RIGHT(A2,1)="/",1,0))
or since urls don't contain spaces
=SUBSTITUTE(TRIM(SUBSTITUTE(A2,"/"," "))," ","/")
Last edited by ByteMarks; 03-14-2024 at 05:46 AM.
Thank you, All, for your help!!!
ByteMarks, Your formula works
Tommy90 - the second formula (since there are no spaces) worked
CarissaKirby, Your formula works as well. Thanks for the explanation too!
I hope everyone has a nice day!
Carol
My formula works, too!
If that takes care of your original question, please choose Thread Tools from the menu link above and mark this thread as SOLVED.
Also, if you have not already done so, remember that you can reward anyone who offered you help towards a solution for your issue by clicking the small star icon (* Add Reputation) located in the lower left corner of the post in which the help was given. By doing so you can add to the reputation(s) of each of those who offered help.
You can also do them all at once, using this:
=LET(A,A2:A20,REPLACE(A,LEN(A)+(RIGHT(A)<>"/"),1,""))
Just delete ALL expected results first! Adjust the bit in red to suit.
Glenn
None of us get paid for helping you... we do this for fun. So DON'T FORGET to say "Thank You" to all who have freely given some of their time to help YOU.
Temporary addition of accented to illustrate ongoing problem to the TT: L? fh?ile P?draig sona dhaoibh
Glad to help & thanks for the feedback.
There are currently 1 users browsing this thread. (0 members and 1 guests)
Posting Permissions
LinkBack URL
About LinkBacks
Register To Reply
Bookmarks