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

Sorry, line-breaks appeared to be lost

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

Format of names as above (without the or whatever your newsreader uses
to denote quotes)

S

Hi Stephan,

Am Mon, 11 Sep 2017 16:37:39 -0500 schrieb Stephan Bird:

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)

Your names in column A from A1 on.
Then try:
=HYPERLINK("https://www.ncbi.nlm.nih.gov/pubmed/?term="&LEFT(A1,FIND(",",A1)-1)&"+"&MID(A1,FIND(",",A1)+2,1)&"[au]",MID(A1,FIND(",",A1)+2,1)&"
"&LEFT(A1,FIND(",",A1)-1)&" Publications")

Or do it with VBA:

Sub CreateHyperlink()
Dim LRow As Long, i As Long
Dim varData As Variant, varTmp As Variant

Const strTmp = "https://www.ncbi.nlm.nih.gov/pubmed/?term="

With ActiveSheet
LRow = .Cells(.Rows.Count, "A").End(xlUp).Row
varData = .Range("A1:A" & LRow)
For i = LBound(varData) To UBound(varData)
varTmp = Split(varData(i, 1), ", ")
.Hyperlinks.Add _
anchor:=Cells(i, "B"), _
Address:=strTmp & varTmp(0) & "+" & Left(varTmp(1), 1) & "[au]", _
TextToDisplay:=Left(varTmp(1), 1) & " " & varTmp(0) & "
Publications"
Next
End With
End Sub

If the names are in another column modify the references into the code.


Regards
Claus B.
--
Windows10
Office 2016

On Tue, 12 Sep 2017 00:26:17 +0200, Claus Busch wrote:

Your names in column A from A1 on.
Then try:
=HYPERLINK("https://www.ncbi.nlm.nih.gov/pubmed/?term="&LEFT(A1,FIND
(",",A1)-1)&"+"&MID(A1,FIND(",",A1)+2,1)&"[au]",MID(A1,FIND(",",A1)+2,1)&"
"&LEFT(A1,FIND(",",A1)-1)&" Publications")

Or do it with VBA:

Sub CreateHyperlink()
Dim LRow As Long, i As Long Dim varData As Variant, varTmp As Variant

Const strTmp = "https://www.ncbi.nlm.nih.gov/pubmed/?term="

With ActiveSheet
LRow = .Cells(.Rows.Count, "A").End(xlUp).Row varData = .Range("A1:A"
& LRow)
For i = LBound(varData) To UBound(varData)
varTmp = Split(varData(i, 1), ", ")
.Hyperlinks.Add _
anchor:=Cells(i, "B"), _
Address:=strTmp & varTmp(0) & "+" & Left(varTmp(1), 1) &
"[au]", _
TextToDisplay:=Left(varTmp(1), 1) & " " & varTmp(0) & "
Publications"
Next
End With End Sub

Thanks Claus. I've not tried the second way as I was trying to avoid
macros but the first works as desired (just had to work my way through
the mids and finds)

Stephan.

Hi Stephan,

Thanks Claus. I've not tried the second way as I was trying to avoid
macros but the first works as desired (just had to work my way through
the mids and finds)

a little bit shorter:
=HYPERLINK("https://www.ncbi.nlm.nih.gov/pubmed/?term="&SUBSTITUTE(LEFT(A1,FIND(",",A1)+2),", ","+")&"[au]",MID(A1,FIND(",",A1)+2,1)&" "&LEFT(A1,FIND(",",A1)-1)&" Publications")

Claus