Extracting Domain Name from URL Column in Excel

[NWPA]

Hello,
I was wondering if there was any way of VBA coding to extract just the domain name and end suffix from a url column.
For Example
If I had 3 entries in a URL column

http://www.tek-tips.com

http://www.tek.tek-tips.com

http://www.tek.tip.tektips.com

Is there a VBA Script to only up tek-tips.com from all three. I have been trying to find a way in Access SQL but have not had any luck. I am not framiliar with VBA so any help would be appriciated.

Thank You in advance

 

[SkipVought]

Hi

Use the Split Function

Function DomainSuf(sStr As String)
   DomainSuf = Split(sStr, ".")(UBound(Split(sStr, ".")) - 1) & "." & Split(sStr, ".")(UBound(Split(sStr, ".")))
End Function

Skip,

_{[red]Be advised:[/red] Researchers have found another Descartes trueism, "Cogito ergo spud."
"I think; therefore, I YAM!}

 

[NWPA]

Hello,

I am sorry i have zero experience with VBA scripting in Office. Where exactly do i place this function Tools>>Macro>>VisualBasic Script Editor?

Do I need to name the url header DomainSuf? How does the Function know that i am pointing towards the URL Column? I am sorry if I sound like a complete moron its just i do not have any experience with VBA and very little with VB.

Thank You in advance

 

[SkipVought]

Paste in the VB Editor (alt+F11) in a MODULE -- not a Worsheet Object

Use on your worksheet just like any other function.

Skip,

_{[red]Be advised:[/red] Researchers have found another Descartes trueism, "Cogito ergo spud."
"I think; therefore, I YAM!}

 

[NWPA]

I pasted the function into a module (not Class Module) and still did not have any luck. Once again I appologize for not being knowledgeable.

 

[SkipVought]

How are you using the function. Explain where the source data is and how you are using the function.

Skip,

_{[red]Be advised:[/red] Researchers have found another Descartes trueism, "Cogito ergo spud."
"I think; therefore, I YAM!}

 

[NWPA]

I am sorry but I am struggling on this. I understand a Module is like a global reference throughout the workbook but I am having problems with how the function works. If I have the Url Column that I want altered in worksheet1 column A. Will this function alter Column A or put it in Column B? I am also struggling with how I can make the module apply to worksheet one. Like I have said several times I appologize for not having any knowledge in this area.

 

[NWPA]

I currently have in modules a Module1 with the DomainSuf function. The data is in Sheet 1 column A. The only purpose of this excel workbook is going to be importing a list of about 2,000 Urls into a column and attempting to capture the root domain and suffix every week. The problem is some of the domains have no sub domains and others have 1 or 2.

 

[SkipVought]

You cannot ALTER a cell using a formula or function. You must use the formula in ANOTHER cell and REFERENCE the cell you want to work on.

For instance, if your URL were in A3, then the formula in, lets say C3 would be
[tt]
=DomainSuf(A3)
[/tt]



Skip,

_{[red]Be advised:[/red] Researchers have found another Descartes trueism, "Cogito ergo spud."
"I think; therefore, I YAM!}

 

[NWPA]

Okay that makes complete sense. I am sorry i thought you were saying i needed a function in the sheet VBA. Works Perfect thanks a ton.