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

excel excel-2007 excel-formula
Pr0no picture Pr0no · May 3, 2013 · Viewed 33.1k times · Source

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!

Answer

ApplePie picture ApplePie · May 3, 2013

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)

Related questions

Excel formula to reference 'CELL TO THE LEFT'

I'm trying to do conditional formatting so that the cell color will change if the value is different from the value in the cell left of it (each column is a month, in each row are the expenses on certain …

Read More
Excel how to find values in 1 column exist in the range of values in another

I have two columns- column A which extends upto 11027(values) and column I which extends to 42000(values).Both the columns contains some code details. Something like this A B q123 as124 as124 gh456 ff45 q123 dd1 dd2 xx2 xx3 xx4 …

Read More
PivotTable to show values, not sum of values

I'm wanting to display a pivot table and for it to show me the actual values, one on each row, rather than a sum of the values. E.g. Name Jan Feb Mar Apr Bob 12 10 4 3 5 James 2 6 8 1 15 etc. My starting point …

Read More