search find replace substutute

**Blake 7** · 09-18-2011, 09:06 AM

Hi guys,

If anyone can suggest a better thread tittle please let me know.

I have attached a sample showing the desired result which will make this easier to follow.

In cell B2 there is a hyperlink which I have copied and pasted, the url pertains to a particular web page for the value in cell A2.

I would like a formula to save me time from going to each web page from the values listed in col A and copying and pasting the URL.

Therefore, in B3 I would like a formula which copies everything in the URL above (B2) from the first h until the first = sign as below:-

http://www.caravanclub.co.uk/Caravan...ult.aspx?site=

and then inserts the value in col b (and this is where i am struggling as, there needs to be + signs between the words, For eg B3 should be inserted as

Blackmore +Caravan+Club+Site

and then the last part of the url which in the case above is (and wil always be &arrivalDate=16/09/2011&departureDate=19/09/2011

So, as an eg B3 should say

http://www.caravanclub.co.uk/Caravan...site=Blackmore +Caravan+ Club+ Site&arrivalDate=16/09/2011&departureDate=19/09/2011

My way would be to create 3 helper cols and use the len function for the two parts of the hyperlink and the third cell adding the contents of col A but inserting a + in the blanks and THEN in B joining the lot together.

But this seems a bit long winded - can anyone suggest something better?

Thanks for looking

**martindwilson** · 09-18-2011, 09:24 AM

how about
=HYPERLINK($D$5&SUBSTITUTE(A2," ","+")&$D$6)
or even
=HYPERLINK($D$5&SUBSTITUTE(A2," ","+")&$D$6,A2)
or even
=HYPERLINK(LEFT($B$2,(94))&SUBSTITUTE(A2," ","+")&RIGHT($B$2,(48)),A2)

**zbor** · 09-18-2011, 09:28 AM

Well, you jusdt named bunch of function. I think you should be more focused on your problem: How to create hyperlink from a text

Maybe this?

=HYPERLINK("http://www.caravanclub.co.uk/CaravanClubApps/Applications/UK%20Sites/CalendarResult.aspx?site="&
SUBSTITUTE(A3," ","+")&"&arrivalDate=16/09/2011&departureDate=19/09/2011")

**Blake 7** · 09-18-2011, 09:34 AM

Hi Martin / Zbor - thanks both for your suggestions. I'll try them out now and report back. Hope that you are both well. How's that beautiful bbq of yours Z?