Break URL into individual pieces in excel?


Hi, how can I quickly break a list of url's up so that each piece of it is in a different cell?

I have a list of thousands of them and want to break them up like this:

will become this, where each bullet point is a new cell that would be to the right of the url:

  • www
  • url
  • com
  • directory
  • directory
  • stuff
  • ?abc=2&def=2

Also, the url can begin with http or https and it could also end like this: ".html" or this ".php" - I was told the fact that there is another period in there can mess things up sometimes.

Thanks in advance!




Easier said than done. In fact, considering how many URLs are parsed every day, there must be a better way of doing this than what you find in the attached workbook but that is beyond my capability. Please try the code in the attached workbook. Press Alt+F11, read the comments in the code, place teh cursor anywhere in the procedure called 'SplitURLs' and press F5 to run it.

The code deals with the URLs I randomly collected. I feel rather certin that there must be some combinations I didn't consider. If they don't turn up in your own collection we may never know. However, in order to facilitate future modification I have structed the code to deal with each section of the URL in a separate procedure.

I decided to write all directories in one column which makes for extra rows rather than unaligned colomns. That can be modified in the individual procedures, too.

You should also know that each time you run the code the output worksheet is totally cleared and new data are written into it. If you wish to preserve a previous result rename the sheet you wish to keep or change the Target tab's name in the code. The program will create a new sheet by the name it finds in the code.


Answer the Question

You must create an account to use the forum. Create an Account or Login