How to retain only the website name

0

Hi Team,

Good Evening.

I need answer to only one question

For example,

let us imagine you have ten webpages of different websites in excel.

https://bro4u.com/watertank-cleaning-bangalore

https://www.housejoy.in/interior-renovation-construction/interior-design

https://www.urbanclap.com/bangalore-ac-service-repair

Now the three hyperlinks are in microsoft excel sheet

How can I retain only the website name, and delete the link for the webpages?

For example, in https://bro4u.com/watertank-cleaning-bangalore

I need only https://bro4u.com and delete the words that is watertank-cleaning-bangalore. Is there a formula I can use. Kindly inform

Answer
Discuss

Answers

0

Hello Sathya,

Your question isn't clear on whether the original URL is in a hyperlink or a worksheet cell. The UDF (User Defined Function) below can extract the full address or the nett URL from a hyperlink. It must be pasted to a standard code module which you must insert in the VBA editor. None of the pre-existing code modules is suitable. Once code is added to your workbook it must be saved as macro-enabled (xlsm format).

Function GetURL(Cell As Range, _
                Optional Shorten As Boolean)
    ' 07 Jun 2019

    Dim Fun As String
    Dim Sp() As String

    Application.Volatile
    On Error Resume Next
    ' ignores all but the first cell of a multi-cell range
    Fun = Cell.Cells(1).Hyperlinks(1).Address
    If Err Then
        Fun = "No hyperlink"
        Err.Clear
    Else
        If Shorten Then
            On Error GoTo 0
            Sp = Split(Fun, "//")
            Fun = Split(Sp(UBound(Sp)), "/")(0)
        End If
    End If

    GetURL = Fun
End Function

In the Worksheet the UDF is called like any other function, for example,

=GetURL(A2)
or
=GetURL(A2,True)

Copy down as you would with any built-in function. If the Shorten parameter is True the function will return the nett URL without extensions. A2 is a cell. It also can be a range of several cells, like A2:A10. But the function will look at only the first cell. If the cell doesn't contain a hyperlink "No hyperlink" is returned.

In case you don't need to extract the URL from a hyperlink the formula below will shorten an address typed in a cell (B2 in my example).

=LEFT(B2,FIND("/",B2,IFERROR(FIND("//",B2),-1)+2)-1)
Discuss

Discussion

Hi The URL link is in the excel sheet cell. I just need the website name and not the web page. Thank you for the kind response and for taking the initiative. If you can help me.
Sathya Jun 7, '19 at 12:33 pm
Wake up man! The solution to your problem is in the formula provided in my answer. All you got to do is open the attached workbook and look. Reading my post attentively would have given you a clue. Let me know if you have a real problem.
Variatus (rep: 2998) Jun 7, '19 at 9:52 pm
Add to Discussion

Answer the Question

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