Replace part of a URL in Excel 2013 with the contents of a cell

[Hentr]

Hi There

I'm stuck...Please help..

I'm a request fulfilment manager, and I have an Excel sheet which is an extract from our Helpdesk system. It's 70.000+ rows. Here is my challenge:

In colum A I have a unique number for the specific Request i.e. RFxxxxx and in colum B I have an URL that I have copied from the browser adressline, but in the URL a specific RFxxxxx number http://hpsm.regsj.intern/sm/index.do?ctx=docEngine&file=request&query=number="RF00021941%22&action=&title=Request%20RF00021941 (this URL is copied to all rows in the spreadsheet)

What I would like to do is to replace the bold text with the data (text) in colum A, whitch is similar in format, how do I do this using VBA or a Macro in all rows, top to bottom?

Kind Regards
Henrik Troels-Hansen

 

[sergioMabres]

Re: How to replace part of a URL in Excel 2013 with the contents of a cell

Hi Hentr,
If you have de code RF00001 in cell A2 you could write ="http://hpsm.regsj.intern/sm/index.do...y=number%3D%22"&A2&"%22&action=&title=Request%20&"A2
In cell B2 to get what you want
Cheers
Sergio

 

[Hentr]

Re: How to replace part of a URL in Excel 2013 with the contents of a cell

Hi Sergio

I'm afraid that doesn't work, for some reason the information from A2 dosn't get copied into the string in cell B2, when you click the link, it's not in the URL in the browser... Or am I missing a step?

[TABLE="width: 1076"]
<colgroup><col><col></colgroup><tbody>[TR]
[TD]Number[/TD]
[TD]Link[/TD]
[/TR]
[TR]
[TD]RF00010316[/TD]
[TD]="http://hpsm.regsj.intern/sm/index.do?ctx=docEngine&file=request&query=number%3D%22"&A2&"%22&action=&title=Request%20"&A2[/TD]
[/TR]
[TR]
[TD]RF00010317[/TD]
[TD]="http://hpsm.regsj.intern/sm/index.do?ctx=docEngine&file=request&query=number%3D%22"&A2&"%22&action=&title=Request%20"&A3[/TD]
[/TR]
[TR]
[TD]RF00010318[/TD]
[TD]="http://hpsm.regsj.intern/sm/index.do?ctx=docEngine&file=request&query=number%3D%22"&A2&"%22&action=&title=Request%20"&A4[/TD]
[/TR]
</tbody>[/TABLE]

 

[sergioMabres]

Re: How to replace part of a URL in Excel 2013 with the contents of a cell

Hi Sergio

I'm afraid that doesn't work, for some reason the information from A2 dosn't get copied into the string in cell B2, when you click the link, it's not in the URL in the browser... Or am I missing a step?

[TABLE="width: 1076"]
<tbody>[TR]
[TD]Number[/TD]
[TD]Link[/TD]
[/TR]
[TR]
[TD]RF00010316[/TD]
[TD]="http://hpsm.regsj.intern/sm/index.do?ctx=docEngine&file=request&query=number%3D%22"&A2&"%22&action=&title=Request%20"&A2[/TD]
[/TR]
[TR]
[TD]RF00010317[/TD]
[TD]="http://hpsm.regsj.intern/sm/index.do?ctx=docEngine&file=request&query=number%3D%22"&A2&"%22&action=&title=Request%20"&A3[/TD]
[/TR]
[TR]
[TD]RF00010318[/TD]
[TD]="http://hpsm.regsj.intern/sm/index.do?ctx=docEngine&file=request&query=number%3D%22"&A2&"%22&action=&title=Request%20"&A4[/TD]
[/TR]
</tbody>[/TABLE]

Sory my bad the formula was missplaced an Ampersand &
The formula for B2 should be

="http://hpsm.regsj.intern/sm/index.do...y=number%3D%22"&A2&"%22&action=&title=Request%20"&A2

 

[Hentr]

Re: How to replace part of a URL in Excel 2013 with the contents of a cell

Hi Sergio

Thanks for all your help, but I hope you can help some more..

The "data" in cell B2 is now correct, but underlying is the formula, and the formula is the string that gets put in the browser address line.

See picture and note the diference between what's in the cell contra what's in the cell line..

http://prntscr.com/m8v68d

How do I format the B2 cell so that it's the "data" in the cell that is the string forwarded to the browser..

I really hope that you can see through my lack of skills in Excel and help me.

Kind Regards
Henrik

 

[sergioMabres]

Re: How to replace part of a URL in Excel 2013 with the contents of a cell

Hi Henrik
Sorry, I do not understand your request, could you please explain "How do I format the B2 cell so that it's the "data" in the cell that is the string forwarded to the browser." further
Sergio

 

[Hentr]

Re: How to replace part of a URL in Excel 2013 with the contents of a cell

Hi Sergio

The visible data in Cell B2 is: [TABLE="width: 951"]
<tbody>[TR]
[TD="class: xl65, width: 951"]http://hpsm.regsj.intern/sm/index.do?tx=docEngine@file=request&query=number%3D%22RF00010316%22&action=&title=Request%20RF00010316 And it's a result of the formula in the same cell B2, which is:
="http://hpsm.regsj.intern/sm/index.do?ctx=docEngine@file=request&query=number%3D%22"&A2&"%22&action=&title=Request%20"&A2

My problem is that it's the formula (in BOLD) is the string passed to the webbrowser and not the result of the formula, which is the string that I need to pass to the webbrowser when clicked in order to call the right Request in our Helpdesk system. I hope that cleared up the question.

Kind Regards
Henrik

[/TD]
[/TR]
</tbody>[/TABLE]

 

[sergioMabres]

Re: How to replace part of a URL in Excel 2013 with the contents of a cell

Try this

[COLOR=#444444][FONT=Calibri]=HYPERLINK("http://hpsm.regsj.intern/sm/index.do...y=number%3D%22"&A2&"%22&action=&title=Request%20"&A2)[/FONT][/COLOR]

 

[Hentr]

Re: How to replace part of a URL in Excel 2013 with the contents of a cell

Hi Sergio

I'm sorry to say that, that doesn't work either: The string passed is: http://hpsm.regsj.intern/sm/index.d...=number=""&A2&""&action=&title=Request "&A2&"
And that does not work. The "formula" string looks like this: =HYPERLINK("http://hpsm.regsj.intern/sm/index.do?ctx=docEngine@file=request&query=number%3D%22"&A2&"%22&action=&title=Request%20"&A2)

I'm at a loss... If you wan't to quit, I will fully understand, what I can't understand is why this is a problem at all...

Kind regards
Henrik

 

[sergioMabres]

Re: How to replace part of a URL in Excel 2013 with the contents of a cell

I think I know what is happenning could be 2 things:
1. Can you see the part of the URL where it says "sm/index.do...t%20" the part ... is wrong ... is a way of excel to shorten a URL you need the real URL not the shortened URL
2. Do this test Copy the result of the formula

[COLOR=#333333]="http://hpsm.regsj.intern/sm/index.do...y=number%3D%22"&A2&"%22&action=&title=Request%20"&A2[/COLOR]

to a notepad, see what it says, then copy from the notepad to the browser and press Enter to see if it works
And tell me what hapens
Cheers
Sergio