Excel: I copy pasted list 200 links from website to Excel and I want to extract text from the link

Question

The links appear hyperlinked with blue line in Excel, I want to extract the text from the link e.g. The url http:\sea.craigslist.com appears as "seattle" in excel. I want to extract the text "sea" out of the link and paste in a next column.

Answer 1

This should work:

=LEFT(RIGHT(C3,LEN(C3)-7),FIND(".",RIGHT(C3,LEN(C3)-7))-1)

Paste this code in the cell where you want the "sea" to be. Replace C3 in the above code with the cell that has link like http://sea.something.com. It'll display sea in the result cell.

Note: This will only work if your link has "http://" in the beginning of the link.

Answer 2

For this you can take help of VBA programming in excel. you can get lots of pdf and tuts regarding it.

http://www.adnug.com/book_reviews/Programming%20Excel%20with%20VBA%20_3_.pdf

Answer 3

You can create a user defined function (UDF) to extract the data.

• Open your workbook.
• Open the VBA Editor by pressing Alt+F11
• Insert a new module by using the menu Insert -> Module
• Copy and paste in the function code listed below
• Close the VBA Editor by using the menu File -> Close and return to Microsoft Excel
• Assuming the first link is in cell A1, in cell B1 type in the formula =GetPart(A1)
• Copy the formula down as required

The formula should return the value 'sea' in cell B1

Function GetPart(cell As Range) As String

    Dim iStart As Integer
    Dim iEnd As Integer
    Dim iLen As Integer

        If (cell.Range("A1").Hyperlinks.Count <> 1) Then
            GetPart = ""
        Else
            GetPart = cell.Range("A1").Hyperlinks(1).Address
            iStart = InStr(1, GetPart, "//") + 2
            iEnd = InStr(1, GetPart, ".")
            iLen = iEnd - iStart
            GetPart = Mid(GetPart, iStart, iLen)
        End If

End Function