Automatically Extracting the Domain Names from a list of Websites in Excel 2007

[retardlike69]

I am trying to create a function in Excel that can take various different listings of websites in a column and place just the primary domain into another column as the first column is filled with web addresses.

Like taking these (currently in column C starting in row 4):
http://www.abc.com/reader
https://farm.cow.org
splat.net
www.cat.co.uk/meow
http://xyz.com


And outputting to the corresponding rows in column J with:
www.abc.com
farm.cow.org
splat.net
www.cat.co.uk
xyz.com


Related Thread: http://www.excelforum.com/excel-form...-from-url.html

(I'm a noob at Excel, but I'm learning pretty fast.) I am currently using Excel 2007 with various macros in the workbook already. Any code suggestions would be greatly appreciated. I've been working on this trying to learn for acouple of days now to no avail.

[retardlike69]

Would a series of "If"..."Then" statements work with trying some search method for "//" and "/" be the best options?
Any code suggestions or video tutorial suggestions would be most welcome. I've been searching now for over a week, but I'm likely not searching for the right terms.

[Pepe Le Mokko]

You could eventually start with the idea of using
Ctrl+H
replace what *//
with (lave blank)
Replace all

And then the same for /*

[retardlike69]

Thanks for the input Pepe.
But I would prefer to do this by automation or script since I will likely be looking at hundreds, if not thousands, of websites.

[Pepe Le Mokko]

Why not record a macro if you want. Ctrl+H is not on a cell per cell basis but can be applied to an entire workbook

[retardlike69]

So, how would the macro work then? I would need some function to handle all the different varitions of addresses like I noted earlier. They are all different in their formating:
http://www.abc.com/reader
https://farm.cow.org
splat.net
www.cat.co.uk/meow
http://xyz.com

That's the hard part that I was hoping someone had a good solution for.

[Kyle123]

Assuming all in a column starting at A1

Please Login or Register  to view this content.

[retardlike69]

Thanks Kyle123.
So, should I use the output column as as function and change the subs to functions while using...

Please Login or Register  to view this content.

...in the cell I want output to?

I'm not sure how to try this with what I have thus far.

[Kyle123]

No, you said you wanted it done through automation, not through a function.

Put your urls in column A of sheet1

Put the code in a new module, then run it.

[retardlike69]

OK, I see now, but there are three problems.

1. Some of the URL's (ex http://www.abc.com/reader.html) are making two lines like
* abc.com
* reader.html

2. Is there a way to make the columns coordinate to rows? Right now my list starts down a few rows, but the list of extracted URL's start at the top...

3. The cells that are blank, but have formatting (a border) are returning a value of "#N/A" ...it there a good way to tell it to be blank, or not fill anything in?

Thanks! This looks like this is well on its way to making this happen.

[Kyle123]

This is why it is always better to upload a sample workbook with all the permutations of how your urls can exist and where to start from.

What row row your urls start on?

This will sort out the .html, but you need to tell us of any other extensions you have

Please Login or Register  to view this content.

[Andrei Kononenko]

I tried to apply the same code for my file but got line starting with .offset highlighted with fault 13 notification.
Any idea's why it is the case ?

[Kyle123]

What's the error say? You really need to upload a workbook

Edit oops, you're not the op. The data has to be in sheet 2 and start in A3

[Andrei Kononenko]

I'm using a dutch version of excel so I don' have the English description of the fault. However, I think "Types do not match" should say something to you. Right?
I tried to apply the code for column X "URL aanbod" in my file "URL AANBOD". Please don't go mad. It's Dutch

Thanks in advance for your help.

Andrei

[Kyle123]

It won't work for a few reasons, firstly you need to use column x, secondly some of the cells have missing values and thirdly you have more than one url per cell.

The best thing to do is start your own thread, explain what you want to do and upload your workbook

[Andrei Kononenko]

I appreciate your suggestion.

[retardlike69]

Thanks again Kyle123...

Please Login or Register  to view this content.

I suppose adding ".htm", ".shtml", ".php", ".jsp", ".js", ".cgi", ".asp", and ".aspx" extensions would pretty much cover everything.

But the bigger problem would be getting the rows to output on the same row as the URL put into the function.
For example, there are some gaps in the column and the output is ignoring the correlation between rows and output.

My input column starts at D4, there is data is cell D6, D9, D14, D15...and so forth.
The function is placing (accurate but misplaced) data starting at K4 (offset of 7 in my function), the outputs the D6 input to K5, D9 input to K6, and so forth. After it reaches the end of the list, it places "#N/A" into each column thereafter.
(I think the #N/A output is caused by the function expecting a data input due to the use of borders and cell coloration...) Maybe there is a way to output "" if there is is not data input?

[Kyle123]

Ok, it's time to upload a workbook

[retardlike69]

Here's a barebones copy of the database I'm building...

EDIT: I added in some generic URL's for you.
P.S. Right now the "code" is in a button...that was being used for testing purposes. Of course, I wanted to make it automatic after filling in the cell asking for the website URL.

[Kyle123]

There aren't any urls...

[retardlike69]

Uploaded a fixed copy. (Sorry about that.) Try again please.

[Kyle123]

Try this:

Please Login or Register  to view this content.

Then:

=CleanString(D2)  

Copied down etc

[retardlike69]

Wonderful! That is exactly what I needed!!!
+REP

Thanks for all your hard work and help in getting a clean working solution!

To other Users here...be sure to note the correct code to access to function is:

Please Login or Register  to view this content.

[Cutter]

@ retardlike69

Welcome to the forum.

Based on your last post it seems that you are satisfied with the solution(s) you've received but you haven't marked your thread as SOLVED. I'll do that for you now but please keep in mind for your future threads that Rule #9 requires you to do that yourself. If your problem has not been solved you can use Thread Tools (located above your first post) and choose "Mark this thread as unsolved".
Thanks.

Also, as a new member of the forum, you may not be aware that you can thank those who have helped you by clicking the small star icon 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 those who helped.