View Single Post
  #3   Report Post  
Posted to microsoft.public.excel.worksheet.functions
Claus Busch Claus Busch is offline
external usenet poster
 
Posts: 3,872
Default String manipulation

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