# Excel Macro for Hyperlinks



## amwesolo (Jul 11, 2005)

Hi,

I am in need of a specialized Excel macro for editing hyperlinks. Specifically, what I need is a macro that will run through a list of hyperlinks, copy them to adjacent cells, and then knock off all but the last 5 letters of the link. This is to create a list of mutual fund tickers from a grouping of hyperlinks (the last 5 letters of every hyperlink is the ticker that I need). I have tried the "record a macro" function in excel, but it doesn't appear to work for this particular usage. I would appreciate any help and would be glad to send an example.

Thanks,

amwesolo


----------



## Rollin_Again (Sep 4, 2003)

Please post a sample workbook so that I may see the layout of your data.


Rollin


----------



## amwesolo (Jul 11, 2005)

Here it is. I need to have the tickers for each fund in the adjacent cell (under "tickers"). It's the last five letters of each hyperlink. Thanks a bunch!

amwesolo


----------



## Rollin_Again (Sep 4, 2003)

There is no attachment. Please re-post


Rollin


----------



## amwesolo (Jul 11, 2005)

whoops, here it is


----------



## Rollin_Again (Sep 4, 2003)

Here is the macro code you need. Just click in the first cells in column "B" and run the code.


```
Public Sub Hyperlinks()

vRow = Cells(Rows.Count, "A").End(xlUp).Row

Do While ActiveCell.Row <= Cells(Rows.Count, "A").End(xlUp).Row

If ActiveCell.Offset(0, -1).Hyperlinks.Count > 0 Then

vLink = ActiveCell.Offset(0, -1).Hyperlinks(1).Address

ActiveCell.Hyperlinks.Add Anchor:=Selection, Address:= _
vLink, SubAddress:="", ScreenTip:="", TextToDisplay:= _
Right(vLink, 5)

ActiveCell.Offset(1, 0).Select

Else

ActiveCell.Offset(1, 0).Select
End If

Loop

End Sub
```
Rollin


----------



## amwesolo (Jul 11, 2005)

Thanks very much!


----------

