Trim a URL

[Sharid]

I want to trim a url with vba code so the url will give me the domain name ONLY, from the url below I want the word google

Urls will be in A1 down as the list is dynamic. I will have a command button on on a userform, url will be in fourth sheet called "Sheet4"

• This is in B1 down =RIGHT(A1,LEN(A1)-12) and gives me, google.com/search?q=google+news&oq=google+&aqs=chrome.0.69i59j0l5j69i57j0.8860j0j8&sourceid=chrome&ie=UTF-8
• This is in C1 down =LEFT(B1,FIND(".",B1)-1) and this Give me google

However,
The first formula only works on https and not http as I have to -12 and the second formual looks for the first "." and removes every thing after it and -1
Also the formual is in two columns, so three columns are used A,B,C, where as I wanted it to change all the results in column A and then convert to values and not show as formulas,

Thanks having a look

 

[Eric W]

Try:

=TRIM(RIGHT(SUBSTITUTE(LEFT(A1,MIN(SEARCH({".com",".org",".edu",".gov"},A1&".com.org.edu.gov"))-1),".",REPT(" ",999)),999))

assuming you only have the 4 basic identifiers.

 

[Sharid]

Eric W

Thanks for your reply, the basic identifiers could be any thing, .co.uk .uk, .ca .au and so on will this be an issue?

I tried this and got an error on the second line

Private Sub CommandButton1_Click()
    Worksheets("Sheet4").Range("B1:B" & Range("A" & Rows.Count).End(xlUp).Row).Formula = "=RIGHT(A1,LEN(A1)-12)"
    Worksheets("Sheet4").Range("C1:C" & Range("B" & Rows.Count).End(xlUp).Row).Formula = "=LEFT(B1,FIND(""."",B1)-1)"""
End Sub

 

[Eric W]

Hmm, I'd forgotten the international identifiers. Try this instead:

=TRIM(MID(SUBSTITUTE(A1,".",REPT(" ",999)),20,999))

and if you want to encode that in VBA try:

Worksheets("Sheet4").Range("B1:B" & Range("A" & Rows.Count).End(xlUp).Row).Formula = "=TRIM(MID(SUBSTITUTE(A1,""."",REPT("" "",999)),20,999))"

Note that the interior double quotes must be doubled so VBA can recognize that they're not the enclosing quotes.

 

[Sharid]

The vba is the way I wish to go, Almost there Eric could you take a look at this as you can see it has chopped parts of the characters off

These are showing as http but will be a mix of http and https
Column B should read as such
arnoldclark
van-deals
withamgarage
browningsuzuki

 

[Eric W]

Oops, sorry, change the 20 to 999. Also note that the formula finds what's between the first 2 periods. So for davidbaileygarage.co.uk you'll get davidbaileygarage, not davidbaileygarage.co, but I think that's what you want.

 

[Sharid]

Eric W

Thanks, that is super duper. Thank god you replied, I was about to post my half hearted work around. I will save myself the embarrassment.

 

[Eric W]

Glad it works for you!