String manipulation
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.
|