shortening a URL

[Mikeda2]

Hello!

I have a very long column (almost 3000 cells) of URLs. I would like to replace them in the second column with shortened versions, basically cut them at the fourth slash.

Let me illustrate:

www.plant.edu/wm/mail/window/apple/seed

would turn into

www.plant.edu/wm/mail/window

Could you tell me how can I used functions in Excel to do it? I am not very advanced when it comes to Excel and I need help. Thank you very much.

[VBA Noob]

Does this help

=HYPERLINK(LEFT(A1,FIND("^",SUBSTITUTE(A1,"/","^",4))-1))

VBA Noob

[escelinen]

I don't see any functions to solve this. So I made little VBA-code to handle it.
Long URLs in Column A begins with row 1. Fixed URLs will be placed in Column B. Here is the code for that. Hope it's helping you.

Please Login or Register  to view this content.

[Mikeda2]

Thank you both of you for the fast posts.

I tried to enter the formula, VBA Noob, but it does not seem to be working. I get error message.

I will try your solutions on Monday, Escelien. I am off work now and I want to solve the problem beginning of next week.

Thank you very much again! It is great to see such fast responses.

[VBA Noob]

Here's an example

VBA Noob

[Mikeda2]

Thank you, VBA Noob. Unfortunately, I cannot open the file. Could you try posting it again?

[Paul]

If you have Windows XP, save the file to your PC and right-click on it and choose Explore. You'll then see the Excel file and can double-click to open it.

If you don't have Windows XP, you'll need a zip utility to extract the Excel file from the .zip file (such as WinZip, WinRAR, PKZip, etc.)

[Mikeda2]

Thank you, Paul! That was very helpful. Unfortunately, I still get value error when I input that in Excel

[Mikeda2]

Solved the problem! The formula worked, thank you so much. I would have never done it if not for your help.

[VBA Noob]

Good to hear you got it sorted

VBA Noob

[Mikeda2]

I got a new twist to the problem. How can I get the formula to return everything up to the fourth slash? Right now, whenever the URL is shorter than 4 slashes, I get error value.

Thanks a lot for helping out!