Display Results of Original Value After Concatenating in HYPERLINK Form

censo

Board Regular
Joined
Dec 25, 2015
Messages
161
Office Version
  1. 2013
Platform
  1. Windows
Greetings,

When I enter a tracking ID in Cell A2 (just a static value), I would like to be able to make that a clickable link relative to a specific URL syntax at a carrier's website. I can accomplish that by concatenating the URL with the tracking ID value in Cell A2. Understandably, I get the results of the concatenated string whereby:

- the result of the function is not a HYPERLINK, and
- the actual displayed result is the URL

What I would love to happen is to have the value of what's in Cell A1 be the result of the function and have it be a clickable hyperlink. A bonus would even be to have just one column for this (possible embed the function into a conditional formatting) where whenever the tracking ID is pasted into Column A, it automatically generates the desired result.



Excel 2013 32 bit
ABCD
1Tracking IDURLCURRENT RESULTDESIRED RESULT
2123456789https://www.fedex.com/apps/fedextrack/?action=track&trackingnumber=TESTSEARCH&cntry_code=ca_englishhttps://www.fedex.com/apps/fedextrack/?action=track&trackingnumber=123456789&cntry_code=ca_english123456789
Sheet1
Cell Formulas
RangeFormula
C2="https://www.fedex.com/apps/fedextrack/?action=track&trackingnumber="&A2&"&cntry_code=ca_english"



THE CHALLENGE:

The URL syntax works for FedEx. The moment I use a different carrier, the solution won't work. I have a list of URL syntax for various carriers. Is it possible to use a more complex solution that perhaps involves Vlookup or Index/Match functions to accomplish this? I suppose I would need an additional column to identify the tracking ID as a particular carrier to know which URL syntax to use, wouldn't I?

Thanks!
 
Last edited:
.

Actually, try changing the formula to.

=IF(OR(A2="",B2=""),"",HYPERLINK(REPLACE(VLOOKUP(B2,Carriers!$A$2:$B$5,2,0),FIND("TESTSEARCH",(VLOOKUP(B2,Carriers!$A$2:$B$5,2,0))),10,A2),A2))

That worked incredibly well (sorry the late response).

This is exactly what I needed - awesome! Thanks again for your contribution.
 
Upvote 0

Excel Facts

When they said...
When they said you are going to "Excel at life", they meant you "will be doing Excel your whole life".

Forum statistics

Threads
1,223,898
Messages
6,175,274
Members
452,628
Latest member
dd2

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top