=IF(LEN(Excel Cell Value)>0,HYPERLINK(CONCATENATE("First part of the web address",(Excel Cell Value),"Second part of the web address"),Excel Cell Value),"")
I don't understand this. Let me try to break it down -
If(Len(cell value)>0) - if the cell isn't empty, do TRUE
TRUE - Hyperlink(Concatenate(first, (cell value), second), (cell value)
FALSE - ""
Now let's see how hyperlink works
Hyperlink(link location, friendly name)
For you this is
link location = concatenate(first, value, second)
friendly name = value
You are assigning the friendly name to be the cell value. So, unless you have something like -
A1 = Google
A2 = Hyperlink(Concatenate("https://www.",A1,".com",A1))
A2 = Google
This won't work. The only thing you'll be able to do is use VBA to go out to the page and gather information, or use something like -
A1 = Google
A2 = Searching Website
A3 = Hyperlink(Concatenate("https://www.",A1,".com",A2))
A3 = Searching Website
To get the title via VBA -
Sub gettitle()
Dim ie As Object
Set ie = CreateObject("InternetExplorer.Application")
ie.navigate "http://superuser.com/"
While ie.busy
DoEvents
Wend
Dim title As String
title = ie.document.title
MsgBox (title)
End Sub
Okay, to make a function return the hyperlink with the title, you'll need a User Defined Function (UDF) -
Function GetTitle(site As Range) As String
Dim title As String
Dim ie As Object
Set ie = CreateObject("InternetExplorer.Application")
ie.navigate site
While ie.busy
DoEvents
Wend
title = ie.document.title
ie.Quit
GetTitle = title
End Function
This will go to the webpage target and return the title. So now, say you have a webpage in cell A1 - now you need to call your function for the title -
A2 = GetTitle(A1)
A3 = Hyperlink(A1,A2)
http://seotoolsforexcel.com/htmltitle/ – STTR – 2015-06-10T16:50:27.153
I would prefer to just use base excel since I don't want to require every person that wants to use the spreedsheet to have to download an add on – Sam – 2015-06-10T17:01:21.933