converting hyperlink to actual web address

[Roger on Excel]

I have hyperlinksin a column in my spreadsheet.

I would like the cells adjacent to show the actual web address.

How do i do this?

Thanks in advance,

Roger

 

[ShaneDevenshire]

Hi,

I believe you would need to create a VBA routine to do this, do you want that?

 

[Roger on Excel]

Yes please Shane - that would work for me.

Thanks,

Roger

 

[Sheeloo]

Try
'Assuming you have your hyperlinks in Col A
'This will extract links and paste in Col B
Sub HyperLinksShow()
Dim i
Dim LastRow
LastRow = Range("A65536").End(xlUp).Row
For i = 1 To LastRow
On Error Resume NExt
Cells(i, 2).Value = Cells(i, 1).Hyperlinks(1).Name
Next i
End Sub

 

[ShaneDevenshire]

Hi,

Here is a function you can enter in the spreadsheet

Function myHyperlink(cell As Range) As String
On Error Resume Next
If cell.Hyperlinks(1).Address <> "" Then
myHyperlink = cell.Hyperlinks(1).Address
ElseIf Err <> 0 Then
myHyperlink = ""
Else
myHyperlink = cell.Hyperlinks(1).Name
End If
End Function

If your hyperlink is in cell A1 then in B1 enter =myHyperlink(A1)

This function returns a hyperlink address if there is one, otherwise if the
cell has a hyperlink that is not a typical hyperlink address it returns the
name. Finally if there is no hyperlink in the cell it return nothing.

If you want a macro to enter the hyperlink in the cell without a formula, then

Sub HLinks()
Dim cell As Range
On Error Resume Next
For Each cell In Selection
If cell.Hyperlinks(1).Address <> "" Then
cell.Offset(0, 1).Value = cell.Hyperlinks(1).Address
ElseIf Err = 0 Then
cell.Offset(0, 1).Value = cell.Hyperlinks(1).Name
End If
Next cell
End Sub

To execute this macro you select all the cells with/without hyperlinks and
run it, the macro will populate the cell to the right similar to the function
above but it will not be a formula.

 

[Roger on Excel]

Thanks Shane - very much appreciated - worked great

All the best, Roger

 

[EXCEL HELP]

Can you help me?
I would like to show the actual email address that is in a hyperlink formula
with out having to click on each hyperlink and cut and paste the email
address from the To: line on an email.
Can you email me I really need help (e-mail address removed)

 

[Dave Peterson]

If you use that =myHyperlink() function, you'll see something like:

mailto:[email protected]

So you could strip that mailto: stuff off by:

=mid(myhyperlink(a1),8,255)
(where A1 contains the hyperlink to the email address.)

 

[EXCEL HELP]

Dave, when i put in the formulas:
=myhyperlink(a1)
or
=mid(myhyperlink(a1),8,255)

(where A1 contains the hyperlink to the email address.)

in both instances i get #name? in the cell.

Can I email you a copy of my excel sheet so you can see the data I am
working with ?

 

[Dave Peterson]

No thanks.

Did you install that user defined macro that Shane wrote for you.

If you're new to macros:

Debra Dalgleish has some notes how to implement macros here:
http://www.contextures.com/xlvba01.html

David McRitchie has an intro to macros:
http://www.mvps.org/dmcritchie/excel/getstarted.htm

Ron de Bruin's intro to macros:
http://www.rondebruin.nl/code.htm

(General, Regular and Standard modules all describe the same thing.)

========
Short course:

Open your workbook.
Hit alt-f11 to get to the VBE (where macros/UDF's live)
hit ctrl-R to view the project explorer
Find your workbook.
should look like: VBAProject (yourfilename.xls)

right click on the project name
Insert, then Module
You should see the code window pop up on the right hand side

Paste the code in there.

Now go back to excel.
Into a test cell and type:
=myhyperlink(a1)
Where A1 contains a hyperlink.

Then if that works, try:
=mid(myhyperlink(a1),8,255)