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.
You can create a user defined function (UDF) to extract the data.
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
This should work:
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.
For this you can take help of VBA programming in excel. you can get lots of pdf and tuts regarding it.