Display Results of Original Value After Concatenating in HYPERLINK Form

[censo]

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
	A	B	C	D
1	Tracking ID	URL	CURRENT RESULT	DESIRED RESULT
2	123456789	https://www.fedex.com/apps/fedextrack/?action=track&trackingnumber=TESTSEARCH&cntry_code=ca_english	https://www.fedex.com/apps/fedextrack/?action=track&trackingnumber=123456789&cntry_code=ca_english	123456789
Sheet1

Cell Formulas
Range	Formula
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!

 

[VeryForgetful]

Do you have a list of the different carrier syntax?, It's difficult as you need to know where to place the tracking ID which will be different depending on the carrier.

This is what I had come up with until I saw the last part of your post

=HYPERLINK("https://www.fedex.com/apps/fedextrack/?action=track&trackingnumber="&A2&"&cntry_code=ca_english",A2)<strike></strike>

 

[censo]

Do you have a list of the different carrier syntax?, It's difficult as you need to know where to place the tracking ID which will be different depending on the carrier.

This is what I had come up with until I saw the last part of your post

=HYPERLINK("https://www.fedex.com/apps/fedextrack/?action=track&trackingnumber="&A2&"&cntry_code=ca_english",A2)<strike></strike>

Wow - that works great! If I could only accomplish what you honed in on towards the end, that would be phenomenal. Here's the URLs for the different carriers. Note that 'TESTSEARCH' is what would be replaced with the actual tracking ID

Excel 2013 32 bit
	A	B
1	Carrier	URL
2	FedEx	https://www.fedex.com/apps/fedextrack/?action=track&trackingnumber=TESTSEARCH&cntry_code=ca_english
3	UPS	https://wwwapps.ups.com/WebTracking/track?track=yes&trackNums=TESTSEARCH
4	OnTrac	http://www.ontrac.com/trackingres.asp?tracking_number=TESTSEARCH&x=11&y=11
5	Estes	http://www.estes-express.com/cgi-dta/edn419.mbr/output?search_criteria=TESTSEARCH
Sheet1

 

[VeryForgetful]

Are the tracking ID's for each carrier unique, for example does one carrier always start with a certain prefix or a certain number of charscters?

 

[censo]

Oh they're all different and they always change. I see what you're thinking but the solution would quickly break once the tracking IDs change (because of their incrementing nature).

 

[VeryForgetful]

See if the attached example is ok for you. You will need to select the company from the dropdown then the link will automatically populate.

https://1drv.ms/x/s!AilrZgAqVaaqhG1X0Ehh6Hi2E99d

 

[censo]

Very nice - that works for me! How to implement now?

Thanks again for your continued assistance.

 

[VeryForgetful]

If you need help implementing send me your file

 

[censo]

Thanks. There's no file to send, just a blank spreadsheet. Whenever I add a tracking ID and choose the drop-down the results should populate how you have in your example.

I know how to add the data validation to have the drop down effect, other than that what does the function like look for what you put together?

 

[VeryForgetful]

On a separate sheet called Carriers I used the list of carriers and urls you privuded above for the vlookup.

Then in the sheet containing the ID's I used this formula in cell C2.

=IF(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))

Basically, what it is doing is a url lookup based on the selection in the dropdown list for the relevant carrier. It then replaced TESTSEARCH with the actual tracking ID. The whole formula is wrapped inside a hyperlink formula which uses the actual tracking ID as the hyperlink display name.

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))