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

Question

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!

ApplePie · Accepted Answer · 2013-05-03T15:05:01.087

26

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)

edited May 03 '13 at 15:05

answered May 03 '13 at 14:56

ApplePie

8,814
5
39
60

score 6 · Answer 2 · answered May 03 '13 at 15:02

6

=hyperlink("http://www.site.com/page?id="&B1) *english version *

answered May 03 '13 at 15:02

Alan Waage

603
4
12

score -1 · Answer 3 · answered May 03 '13 at 15:12

The loop looks something like his:

Dim row As Integer
Dim snumber As Variant
Dim TestCell As String
Dim lastcell As Integer

Sheets("data").Select


lastcell = Sheets("data").Cells(Rows.Count, "A").End(xlUp).row

For row = 1 To lastcell
    TestCell = "A" & row
    snumber = Range(TestCell).Value
    If snumber <> "" And snumber <> "0" Then
        Range(TestCell).Select
        ActiveSheet.Hyperlinks.Add Anchor:=Selection, Address:= _
        "url goes here", TextToDisplay:=snumber
    End If
Next row

I don't see how this is even close to what OP wants. – ApplePie May 03 '13 at 18:29 — ApplePie, May 03 '13 at 18:29

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

3 Answers3