Reply
 
LinkBack Thread Tools Search this Thread Display Modes
  #1   Report Post  
Old September 11th 17, 10:37 PM posted to microsoft.public.excel.worksheet.functions
external usenet poster
 
First recorded activity by ExcelBanter: Sep 2017
Posts: 3
Default String manipulation

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

  #2   Report Post  
Old September 11th 17, 10:41 PM posted to microsoft.public.excel.worksheet.functions
external usenet poster
 
First recorded activity by ExcelBanter: Sep 2017
Posts: 3
Default String manipulation

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
  #3   Report Post  
Old September 11th 17, 11:26 PM posted to microsoft.public.excel.worksheet.functions
external usenet poster
 
First recorded activity by ExcelBanter: Apr 2011
Posts: 3,730
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
  #4   Report Post  
Old September 12th 17, 09:32 PM posted to microsoft.public.excel.worksheet.functions
external usenet poster
 
First recorded activity by ExcelBanter: Sep 2017
Posts: 3
Default 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.

  #5   Report Post  
Old September 13th 17, 07:49 AM posted to microsoft.public.excel.worksheet.functions
external usenet poster
 
First recorded activity by ExcelBanter: Apr 2011
Posts: 3,730
Default String manipulation

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


Reply
Thread Tools Search this Thread
Search this Thread:

Advanced Search
Display Modes

Posting Rules

Smilies are On
[IMG] code is On
HTML code is Off
Trackbacks are On
Pingbacks are On
Refbacks are On


Similar Threads
Thread Thread Starter Forum Replies Last Post
STRING MANIPULATION !! jay dean Excel Programming 1 April 27th 09 10:43 AM
Importing Long String - String Manipulation (INVRPT) (EDI EANCOM 96a) Brian Excel Programming 3 February 9th 06 04:38 PM
Importing Long String - String Manipulation (EDI EANCOM 96a) Brian Excel Programming 6 February 9th 06 01:27 PM
String manipulation!! Mannyluk Excel Programming 2 October 18th 04 12:42 PM
VBA String manipulation Chip Pearson Excel Programming 0 March 5th 04 12:02 AM


All times are GMT +1. The time now is 11:29 AM.

Powered by vBulletin® Copyright ©2000 - 2019, Jelsoft Enterprises Ltd.
Copyright 2004-2019 ExcelBanter.
The comments are property of their posters.
 

About Us

"It's about Microsoft Excel"

 

Copyright © 2017