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]

Hi fred, you could change the formula to be:

=IF(A1="","",linkyfy(A1))

 

[fatfreddie]

Bingo!
Cheers Glenn.

 

[fatfreddie]

Hi fred, you could change the formula to be:

=IF(A1="","",linkyfy(A1))

Sure it works but it would be more convenient if I could integrate the above into the linkyfy function itself.
How would I do that ?
I am guessing one more "IF" ?

Thanks in advance
Fred

 

[GlennUK]

In the code:

If sInp = "" Then
   linkyfy=""
   Exit Function
End If

.. is one way.

 

[fatfreddie]

Indeed.

Thanks for your help.

F.