Convert a List of Keywords into Links

I work with many lists of keyword terms and need to convert them into html anchor links quickly and automatically, for example such as this:

telephone
telephone directory
telephone numbers
telephone systems

should be converted into this:

<a href="http://www.example.com/-Telephone">telephone</a>
<a href="http://www.example.com/-Telephone+Directory">telephone directory</a>
<a href="http://www.example.com/-Telephone+Numbers">telephone numbers</a>
<a href="http://www.example.com/-Telephone+Systems">telephone systems</a>

I would like some option to Capitalize the first letter of each word in the href value, and maybe in the anchor text too.

At the moment I am doing some search and replace actions and pasting back and forward to speed this conversion process up, but it is still quite cumbersome, I need it to be in such a way that I can paste a list of terms and it outputs the html in another box that I can just copy.

It would also be a bonus if we could have an option to selectively format the output to encompass each anchor link in list tags, i.e.   <li><a href="#">link</a></li>  - sometimes we would have it with the list tags and sometimes without.  We usually have a our keyword list in Excel so a solution there would be ideal, but a webpage with some javascript could also work well.  Any ideas how to do this?  Thanks.
thyrosAsked:
Who is Participating?
 
TracyConnect With a Mentor VBA DeveloperCommented:
Actually, use this version instead, it removes case sensitivity when comparing strings by adding Option Compare Text to the top of the module.  You will still add exceptions to the array the same way I illustrated above.
Option Compare Text

Sub CreateHyperlinks()

    Dim i As Long 'Loop through rows
    Dim p As Integer 'Loop through array
    Dim lastRow As Long
    Dim listTag As Boolean
    Dim caPitalize As Boolean
    Dim webSite As String 'Base website address
    Dim strLink As String 'Output string
    Dim capList As Variant
    Dim strTemp As String
    
    webSite = "http://www.example.com/"
    lastRow = Range("A" & Rows.Count).End(xlUp).Row
    listTag = False
    caPitalize = False
    capList = Array(" BT ", " UK ", " LEC ", " AEG ") 'Make sure a space is before and after, so words are found, instead of letters within a word
        
    For i = 2 To lastRow
        If Cells(i, 2).Value = "Yes" Or Cells(i, 2).Value = "" Then listTag = True
        If Cells(i, 3).Value = "Yes" Or Cells(i, 3).Value = "" Then caPitalize = True
        Debug.Print i & " " & listTag & " " & caPitalize
        
        If caPitalize Then
            Cells(i, 1).Value = Application.WorksheetFunction.Proper(Cells(i, 1).Value)
        Else
        End If
        
        'Capitilization Exceptions
        strTemp = Cells(i, 1).Value
        For p = LBound(capList) To UBound(capList)
            Cells(i, 1).Value = Replace(strTemp, capList(p), capList(p), , , vbBinaryCompare)
            strTemp = Cells(i, 1).Value
            Cells(i, 1).Value = Replace(strTemp, RTrim(capList(p)), RTrim(capList(p)))
            strTemp = Cells(i, 1).Value
            Cells(i, 1).Value = Replace(strTemp, LTrim(capList(p)), LTrim(capList(p)))
            strTemp = Cells(i, 1).Value
        Next
        
        If listTag Then
            strLink = "<li><a href=" & """" & webSite & Replace(Cells(i, 1).Value, " ", "+") & """" & ">" & Cells(i, 1).Value & "</a></li>"
        Else
            strLink = "<a href=" & """" & webSite & Replace(Cells(i, 1).Value, " ", "+") & """" & ">" & Cells(i, 1).Value & "</a>"
        End If
        
        Cells(i, 4).Value = strLink
        
        listTag = False
        caPitalize = False
    Next i
    
End Sub

Open in new window

Book2-v4.xls
0
 
TracyVBA DeveloperCommented:
Try this, see attached example:
Sub CreateHyperlinks()

    Dim i As Long
    Dim lastRow As Long
    Dim listTag As Boolean
    Dim capitalize As Boolean
    Dim webSite As String 'Base website address
    Dim strLink As String 'Output string
    
    webSite = "http://www.example.com/"
    lastRow = Range("A" & Rows.Count).End(xlUp).Row
    listTag = False
    capitalize = False
        
    For i = 2 To lastRow
        If Cells(i, 2).Value = "Yes" Then listTag = True
        If Cells(i, 3).Value = "Yes" Then capitalize = True
        
        If listTag Then
            If capitalize Then
                strLink = "<li><a href=" & """" & webSite & Replace(Application.WorksheetFunction.Proper(Cells(i, 1).Value), " ", "+") & """" & ">" & Application.WorksheetFunction.Proper(Cells(i, 1).Value) & "</a></li>"
            Else
                strLink = "<li><a href=" & """" & webSite & Replace(Cells(i, 1).Value, " ", "+") & """" & ">" & Cells(i, 1).Value & "</a></li>"
            End If
        Else
            If capitalize Then
                strLink = "<a href=" & """" & webSite & Replace(Application.WorksheetFunction.Proper(Cells(i, 1).Value), " ", "+") & """" & ">" & Application.WorksheetFunction.Proper(Cells(i, 1).Value) & "</a>"
            Else
                strLink = "<a href=" & """" & webSite & Replace(Cells(i, 1).Value, " ", "+") & """" & ">" & Cells(i, 1).Value & "</a>"
            End If
        End If
        
        Cells(i, 4).Value = strLink
        
        listTag = False
        capitalize = False
    Next i
    
End Sub

Open in new window

Book2.xls
0
 
leakim971PluritechnicianCommented:
Use :


<script language="javascript">
	function strListToLink(list) {
		// get an array of link, each one is originaly separated by a comma
		var items = list.split(",");
		// for each link in the array
		for(var l=0;l<items.length;l++) {
			// create a new link element
			var alink = document.createElement("a");
			// set the href
			alink.setAttribute("href","http://www.example.com/-" + items[l]);
			// set the text of the link
			alink.innerHTML = items[l];
			// add the link in the page
			document.getElementById("listOfLink").appendChild(alink);
			// to put the link on the next line, create a br element and add it on the end of the div
			var br = document.createElement("br"); 
			document.getElementById("listOfLink").appendChild(br);
		}
	}
</script>

Open in new window

0
Upgrade your Question Security!

Your question, your audience. Choose who sees your identity—and your question—with question security.

 
leakim971PluritechnicianCommented:
Test page :


<!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Transitional//EN" "http://www.w3.org/TR/xhtml1/DTD/xhtml1-transitional.dtd">
<html xmlns="http://www.w3.org/1999/xhtml">
<head>
<meta http-equiv="Content-Type" content="text/html; charset=utf-8" />
<script language="javascript">
	function strListToLink(list) {
		// get an array of link, each one is originaly separated by a comma
		var items = list.split(",");
		// for each link in the array
		for(var l=0;l<items.length;l++) {
			// create a new link element
			var alink = document.createElement("a");
			// set the href
			alink.setAttribute("href","http://www.example.com/-" + items[l]);
			// set the text of the link
			alink.innerHTML = items[l];
			// add the link in the page
			document.getElementById("listOfLink").appendChild(alink);
			// to put the link on the next line, create a br element and add it on the end of the div
			var br = document.createElement("br"); 
			document.getElementById("listOfLink").appendChild(br);
		}
	}
</script>
</head>
<body onload="strListToLink('telephone,telephone directory,telephone numbers,telephone systems')">
<div id="listOfLink">
</div>
</body>
</html>

Open in new window

0
 
thyrosAuthor Commented:
@leakim971,

I am testing your page first, and here is some feedback:

The list you have is hardcoded into the html body tag and is comma separated, but I have a list of keywords, one per line.  The anchor href value is not converting the spaces between words into + characters or capitalizing the first letter either.  The html output just shows the links, so I would have to view the source to copy and paste that - it would have been preferable to have one html page, one text area box where I can paste a list of keyphrases, one per line, and then the javascript converts that into the links with the html source in another box below it.
0
 
thyrosAuthor Commented:
@broomee9,

Your solution looks interesting, but I couldn't get it to work.  I downloaded your attached file, enabled macros, entered keywords into the first column and nothing.

I noted that you have to select an option for 'List tags' and 'capitalize' on each row.  In our real world use though, we usually do this in batches, so if I have a list of 300 keywords I already know that we want them all to be capitalized and placed in a list tag.  On another occasion, we would want them all capitalized but not in a list tag.

Perhaps it would be better to have separate workbooks for each setting, or only have to set the value in one cell and let it apply to the full page?

Could you please clarify how to use your example too thanks, I am not sure on how to setup macros and stuff.  Thanks.
0
 
thyrosAuthor Commented:
@broomee9,

Ok I managed to figure out what I was doing wrong, I needed to click the developer > macro tab and run the macro from there.  This looks good now.

Could we please have the List Tags and Capitalize assumed as 'Yes' as default (i.e. if we left it blank), but No if we explicitly set it as such.

There are also some acronyms in the keywords, such as 'BT' or 'UK' but these would become 'Bt' and 'Uk' due to the capitalizing function... is it possible to add a list of exceptions so that if these terms appear as standalone words (i.e. not part of another word) then they become fully capitalized, so we would add a list of terms like this:

BT
UK
LEC
AEG

etc and if they appeared anywhere as a single word, such as bt or Bt then it would get converted to BT.

What we have so far works, but if we could get these enhancements that would be fantastic.
0
 
TracyVBA DeveloperCommented:
OK, try this version.
Book2-v2.xls
0
 
TracyVBA DeveloperCommented:
Attached is the code.

To add more terms to the list you would edit this line:

capList = Array(" BT ", " UK ", " LEC ", " AEG ") 'Make sure a space is before and after, so words are found, instead of letters within a word

Notice that there are spaces before and after each of the values, and that each of the values are in ALL caps.  This must be how you enter new values into the array.  Otherwise, the macro will NOT work properly.
capList = Array(" BT ", " UK ", " LEC ", " AEG ", " TEST ", " EXPERTS ", " TEST2 ")

Sub CreateHyperlinks()

    Dim i As Long 'Loop through rows
    Dim p As Integer 'Loop through array
    Dim lastRow As Long
    Dim listTag As Boolean
    Dim caPitalize As Boolean
    Dim webSite As String 'Base website address
    Dim strLink As String 'Output string
    Dim capList As Variant
    Dim strTemp As String
    
    webSite = "http://www.example.com/"
    lastRow = Range("A" & Rows.Count).End(xlUp).Row
    listTag = False
    caPitalize = False
    capList = Array(" BT ", " UK ", " LEC ", " AEG ") 'Make sure a space is before and after, so words are found, instead of letters within a word
        
    For i = 2 To lastRow
        If Cells(i, 2).Value = "Yes" Or Cells(i, 2).Value = "" Then listTag = True
        If Cells(i, 3).Value = "Yes" Or Cells(i, 3).Value = "" Then caPitalize = True
        Debug.Print i & " " & listTag & " " & caPitalize
        
        If caPitalize Then
            Cells(i, 1).Value = Application.WorksheetFunction.Proper(Cells(i, 1).Value)
        Else
        End If
        
        'Capitilization Exceptions
        strTemp = Cells(i, 1).Value
        For p = LBound(capList) To UBound(capList)
            Cells(i, 1).Value = Replace(strTemp, LCase(capList(p)), capList(p))
            strTemp = Cells(i, 1).Value
            Cells(i, 1).Value = Replace(strTemp, RTrim(LCase(capList(p))), RTrim(capList(p)))
            strTemp = Cells(i, 1).Value
            Cells(i, 1).Value = Replace(strTemp, LTrim(LCase(capList(p))), LTrim(capList(p)))
            strTemp = Cells(i, 1).Value
        Next
        
        If listTag Then
            strLink = "<li><a href=" & """" & webSite & Replace(Cells(i, 1).Value, " ", "+") & """" & ">" & Cells(i, 1).Value & "</a></li>"
        Else
            strLink = "<a href=" & """" & webSite & Replace(Cells(i, 1).Value, " ", "+") & """" & ">" & Cells(i, 1).Value & "</a>"
        End If
        
        Cells(i, 4).Value = strLink
        
        listTag = False
        caPitalize = False
    Next i
    
End Sub

Open in new window

0
 
thyrosAuthor Commented:
Thanks for this, I have tested your updated code and the links are converted as list tags and capitalize first letter as default, however I couldn't get the BT, UK etc acronyms to be capitalized fully.

i.e.

Uk Telephone Aeg                  <li><a href="http://www.example.com/Uk+Telephone+Aeg">Uk Telephone Aeg</a></li>
Aeg Telephone Directory Test Lec                  <li><a href="http://www.example.com/Aeg+Telephone+Directory+Test+Lec">Aeg Telephone Directory Test Lec</a></li>
Telephone Bt Numbers Bt                  <li><a href="http://www.example.com/Telephone+Bt+Numbers+Bt">Telephone Bt Numbers Bt</a></li>
Lec Telephone Bt Systems Uk                  <li><a href="http://www.example.com/Lec+Telephone+Bt+Systems+Uk">Lec Telephone Bt Systems Uk</a></li>

They are not being fully capitalized when converted over.  Any ideas? also would it still work if the acronym was at the beginning of the line, i.e.

bt telephone directory

Since it wouldn't have a space at the beginning but it would still be a separate word?  I appreciate all your help on this, what we have is sufficient but if that last thing can be ironed out that would be great.  Thanks.
0
 
TracyVBA DeveloperCommented:
Yes, try the version 4 workbook in this comment:  32281824
0
 
TracyVBA DeveloperCommented:
Yes, it will capitalize the value if it's the first word, in the middle or the last word.

        'Capitilization Exceptions
        strTemp = Cells(i, 1).Value
        For p = LBound(capList) To UBound(capList)
            Cells(i, 1).Value = Replace(strTemp, capList(p), capList(p), , , vbBinaryCompare)  '<-- this one captilizes it if it's in the middle
            strTemp = Cells(i, 1).Value
            Cells(i, 1).Value = Replace(strTemp, RTrim(capList(p)), RTrim(capList(p)))  '<-- this one capitalizes it if it's the last word
            strTemp = Cells(i, 1).Value
            Cells(i, 1).Value = Replace(strTemp, LTrim(capList(p)), LTrim(capList(p)))  '<-- this one capitalizes it if it's the first word
            strTemp = Cells(i, 1).Value
        Next
0
 
thyrosAuthor Commented:
Hi yes I didn't see your latest version until I already posted - this works very well now, excellent! Thanks very much, really appreciate your kind help on this.
0
 
thyrosAuthor Commented:
Brilliant and exceeded expectations, thanks a bunch. A+  :)
0
 
TracyVBA DeveloperCommented:
Thank you for your kind words.  I'm happy to help. :-)
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.