Search code examples
excelexcel-2007excel-formula

How do I create URLs in Excel based on data in another cell?

Consider the following Excel sheet:

     A             B                       C
1 ASX:ANZ      ANZ:ASX       http://www.site.com/page?id=ANZ:ASX
2 DOW:1234     1234:DOW      http://www.site.com/page?id=1234:DOW
3 NASDAQ:EXP   EXP:NASDAQ    http://www.site.com/page?id=EXP:NASDAQ

I need a formula for the B and the C column. In the B column I need the values of the A column to be split on : and the two resulting parts to be reversed, see the three examples. In the C column, I need the result from B to be added to a (hardcopy) URL (http://www.site.com/page?id=) to form a link.

Who can help me out? Your help is greatly appreciated!

Solution

  • Alright. I don't normally spoon feed answers but here you go.

    In B:

    =MID(A1, FIND(":", A1, 1)+1, LEN(A1) - FIND(":",A1,1)) & ":"&MID(A1,1,FIND(":",A1,1)-1)

    In C:

    =HYPERLINK("http://www.site.com/page?id="&B1)