How to convert series of strings into links (regex ?)

[fatfreddie]

Ladies, Gentlemen
I have following task:
I have description of thousands of movies in excel table format.
There is one column for directors names and one for actors names.
Each cell in these columns may contain none, one or more names, if more than one they are separated by commas.
say
A2=Sarah Miles, Joss Ackland, Greta Scacchi, Charles Dance, Geraldine Chaplin

I need to "linkyfy" them - convert each of these names into fargments of html so when they are published in a online shop they become links to search results containing themselves, ie
Sarah Miles becomes

<a href="/search.php?subcat=&text=Sarah+Miles" target="_blank">Sarah Miles</a>

so it will look like
Sarah Miles

But all names in a result must be still in one cell, separated by commas as in the source so A2 becomes:

<a href="/search.php?subcat=&text=Sarah+Miles" target="_blank">Sarah Miles</a>, <a href="/search.php?subcat=&text=Joss+Ackland" target="_blank">Joss Ackland</a>, <a href="/search.php?subcat=&text=Greta+Scacchi" target="_blank">Greta Scacchi</a>, <a href="/search.php?subcat=&text=Charles+Dance" target="_blank">Charles Dance</a>, <a href="/search.php?subcat=&text=Geraldine+Chaplin" target="_blank">Geraldine Chaplin</a>

so it looks like this:
Sarah Miles, Joss Ackland, Greta Scacchi, Charles Dance, Geraldine Chaplin

So far I have managed to do it by splitting these cells to columns by commas, applying concatenate with parts of the "link" to each one separately then merging them together.
Very messy and headache inducing procedure (and error prone too).

What I need is a formula that for given structure of the "link" say:

<a href="/search.php?subcat=&text=FIRSTNAME+MIDDLENAME+SECONDNAME" target="_blank">FIRSTNAME MIDDLENAME SECONDNAME</a>

could be applied in one go to a range of cells containing these names.
Please note in firts part of the link the spaces have to be replaced by "+"
Also, if the cell is empty we skip it.

As you know by now I am not an Excel expert by no means so every bit of help will be appreciated

Thank you
Fred

 

[GlennUK]

You could do this with formulas if you installed Laurent Longre's free MOREFUNC add-in. You could use the WMID function to separate the names, use formulas to generate the HTML string per name, and join them together again using the MCONCAT function.

Download here:
http://xcell05.free.fr/downloads/Morefunc.zip

Is that any use to you?

 

[fatfreddie]

Hmm...
I have looked into MOREFUNC documentation and found that WMID is limited to 253 characters length so this will be the first problem (occasionally these cells are up to 400 or so)

But apart from that I am afraid this is beyond my meagre abilities with excel - wouldn't know how to nest these functions especially the number of names in each cell can vary from 0 to 10 randomly.
Could you please get me started on this ?

Thanks in advance
Fred

 

[GlennUK]

If the number of names can only go up to 10, I don't see how you'd get a character string of more than 253 to break the WMID function. The WMID would be used to split the names. So you'd have 10 cells to break the names ... and 10 cells to convert the names to HTML, and one more formula using MCONCAT to join the results together.

Does that sound possible?

 

[fatfreddie]

Glenn
It sounds possible indeed as that's what I was doing so far by hand (using handy ASAP Utilities)
Splitting text into columns, replacing, concatenating, merging and so on.
This is fine when working on a large database - I can operate on long columns.
Not so with adding new records - takes too much time to fiddle with every one.
What I am dreaming of is "one click" solution: mark the range, click, "linkify".

I am afraid I don't know how to program such macro or whatever you may call it.

Fred

 

[GlennUK]

Once you have set up formulas they can be copied to new areas easily. You wouldn't have to create them from scratch all the time. Have you installed MOREFUNC yet?

 

[fatfreddie]

Yep, I have installed it.
The formulas themselves are a problem

 

[Sandeep Warrier]

How about a UDF?

Function linkyfy(sInp As String) As String
    Dim lCount As Long, vSplitName As Variant
    Dim Temp As String
    If InStrB(sInp, ",") > 0 Then
        vSplitName = Split(sInp, ",")
        For lCount = LBound(vSplitName) To UBound(vSplitName)
            Temp = Temp & "<a href=""/search.php?subcat=&text=" & Replace$(WorksheetFunction.Trim(vSplitName(lCount)), " ", "+") & _
                                            "target=""_blank"">" & WorksheetFunction.Trim(vSplitName(lCount)) & "</a>" & ", "
        Next lCount
        linkyfy = Left$(Temp, Len(Temp) - 2)
    Else
        linkyfy = "<a href=""/search.php?subcat=&text=" & Replace$(sInp, " ", "+") & "target=""_blank"">" & sInp & "</a>"
    End If
End Function

<b>Excel 2010</b><table cellpadding="2.5px" rules="all" style=";background-color: #FFFFFF;border: 1px solid;border-collapse: collapse; border-color: #BBB"><colgroup><col width="10px" style="background-color: #DAE7F5" /><col /><col /></colgroup><thead><tr style=" background-color: #DAE7F5;text-align: center;color: #161120"><th></th><th>A</th><th>B</th></tr></thead><tbody><tr ><td style="color: #161120;text-align: center;">1</td><td style=";">Sarah Miles, Joss Ackland, Greta Scacchi, Charles Dance, Geraldine Chaplin</td><td style=";"><a href="/search.php?subcat=&text=Sarah+Milestarget="_blank">Sarah Miles</a>, <a href="/search.php?subcat=&text=Joss+Acklandtarget="_blank">Joss Ackland</a>, <a href="/search.php?subcat=&text=Greta+Scacchitarget="_blank">Greta Scacchi</a>, <a href="/search.php?subcat=&text=Charles+Dancetarget="_blank">Charles Dance</a>, <a href="/search.php?subcat=&text=Geraldine+Chaplintarget="_blank">Geraldine Chaplin</a></td></tr><tr ><td style="color: #161120;text-align: center;">2</td><td style=";">Sarah Miles</td><td style=";"><a href="/search.php?subcat=&text=Sarah+Milestarget="_blank">Sarah Miles</a></td></tr><tr ><td style="color: #161120;text-align: center;">3</td><td style=";">Joss Ackland</td><td style=";"><a href="/search.php?subcat=&text=Joss+Acklandtarget="_blank">Joss Ackland</a></td></tr><tr ><td style="color: #161120;text-align: center;">4</td><td style=";">Greta Scacchi</td><td style=";"><a href="/search.php?subcat=&text=Greta+Scacchitarget="_blank">Greta Scacchi</a></td></tr><tr ><td style="color: #161120;text-align: center;">5</td><td style=";">Charles Dance</td><td style=";"><a href="/search.php?subcat=&text=Charles+Dancetarget="_blank">Charles Dance</a></td></tr><tr ><td style="color: #161120;text-align: center;">6</td><td style=";">Geraldine Chaplin</td><td style=";"><a href="/search.php?subcat=&text=Geraldine+Chaplintarget="_blank">Geraldine Chaplin</a></td></tr></tbody></table><p style="width:3.6em;font-weight:bold;margin:0;padding:0.2em 0.6em 0.2em 0.5em;border: 1px solid #BBB;border-top:none;text-align: center;background-color: #DAE7F5;color: #161120">Sheet1</p><br /><br /><table width="85%" cellpadding="2.5px" rules="all" style=";border: 2px solid black;border-collapse:collapse;padding: 0.4em;background-color: #FFFFFF" ><tr><td style="padding:6px" ><b>Worksheet Formulas</b><table cellpadding="2.5px" width="100%" rules="all" style="border: 1px solid;text-align:center;background-color: #FFFFFF;border-collapse: collapse; border-color: #BBB"><thead><tr style=" background-color: #DAE7F5;color: #161120"><th width="10px">Cell</th><th style="text-align:left;padding-left:5px;">Formula</th></tr></thead><tbody><tr><th width="10px" style=" background-color: #DAE7F5;color: #161120">B1</th><td style="text-align:left">=linkyfy(<font color="Blue">A1</font>)</td></tr></tbody></table></td></tr></table><br />

 

[fatfreddie]

Sandeep
Thank you very much!

This works great indeed, just had to correct some missing spaces and quotations in the outcome.
So for the record now the code is

Function linkyfy(sInp As String) As String
    Dim lCount As Long, vSplitName As Variant
    Dim Temp As String
    If InStrB(sInp, ",") > 0 Then
        vSplitName = Split(sInp, ",")
        For lCount = LBound(vSplitName) To UBound(vSplitName)
            Temp = Temp & "<a href=""/search.php?subcat=&text=" & Replace$(WorksheetFunction.Trim(vSplitName(lCount)), " ", "+") & """" & " target=""_blank"">" & WorksheetFunction.Trim(vSplitName(lCount)) & "</a>" & ", "
        Next lCount
        linkyfy = Left$(Temp, Len(Temp) - 2)
    Else
        linkyfy = "<a href=""/search.php?subcat=&text=" & Replace$(sInp, " ", "+") & """" & " target=""_blank"">" & sInp & "</a>"
    End If
End Function

so the tresulting links are like that:

<a href="/search.php?subcat=&text=James+Brolin" target="_blank">James Brolin</a>

Thank God for MrExcel forum !

Kind regards
Fred

 

[fatfreddie]

One more thing:
Would it be possible to modify it to ignore empty cells ?
Now when it gets to an empty cell the result is:

<a href="/search.php?subcat=&text=" target="_blank"></a>

Many thanks
Fred