Hi all,

In a column of cells, I have names in the format:

Bird, Stephan
May, Theresa
Trump, Donald
Putin, Vladimir
Duck, Donald
Mouse, Mickey

I'd like to manipulate these into another cell - hyperlinked to "https://
www.ncbi.nlm.nih.gov/pubmed/?term=Bird S[au]" with display text "S Bird
Publications" (for the first example)

I have come up with a long winded set of concatenations - HYPERLINK
(CONCATENATE("https://www.ncbi.nlm.nih.gov/pubmed/?term=",(LEFT(CELL,(FIND
(",",CELL,1))-1)),"+",LEFT((RIGHT(CELL,(LEN(CELL)-((FIND(",",CELL,1))
+1)))),1),"[au]"),CONCATENATE(LEFT(RIGHT(CELL,(LEN(CELL)-(FIND(",",CELL,1)
+1))),1)," ",LEFT(CELL,FIND(",",CELL,1)-1)," Publications"))

but wonder if there's a neater way of avoiding all the LEFTs and RIGHTs?

Surnames may be double barrelled, and there may be more than one forename
which may cause some things to fail. I'd rather use standard built-ins
from Excel (as early as 2003) and not use other cells as intermediates
(which is probably why this is a bit ugly - it was built piecewise from
various manipulations)

I know about swapping & in for concatenate but have not implemented it
yet.

Kind regards

Stephan