converting hyperlink to actual web address
 

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

converting hyperlink to actual web address
 

Hi,

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

--
Thanks,
Shane Devenshire


"Roger on Excel" wrote:

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

converting hyperlink to actual web address
 

Yes please Shane - that would work for me.

Thanks,

Roger

"ShaneDevenshire" wrote:

Hi,

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

--
Thanks,
Shane Devenshire


"Roger on Excel" wrote:

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

converting hyperlink to actual web address
 

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
--
If you find this post helpful pl. choose "Yes"...


"Roger on Excel" wrote:

Yes please Shane - that would work for me.

Thanks,

Roger

"ShaneDevenshire" wrote:

Hi,

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

--
Thanks,
Shane Devenshire


"Roger on Excel" wrote:

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

converting hyperlink to actual web address
 

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.
--
Thanks,
Shane Devenshire


"Roger on Excel" wrote:

Yes please Shane - that would work for me.

Thanks,

Roger

"ShaneDevenshire" wrote:

Hi,

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

--
Thanks,
Shane Devenshire


"Roger on Excel" wrote:

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

converting hyperlink to actual web address
 

Thanks Shane - very much appreciated - worked great

All the best, Roger

"ShaneDevenshire" wrote:

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.
--
Thanks,
Shane Devenshire


"Roger on Excel" wrote:

Yes please Shane - that would work for me.

Thanks,

Roger

"ShaneDevenshire" wrote:

Hi,

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

--
Thanks,
Shane Devenshire


"Roger on Excel" wrote:

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

converting hyperlink to actual web address
 

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
--
KATHERINE


"ShaneDevenshire" wrote:

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.
--
Thanks,
Shane Devenshire


"Roger on Excel" wrote:

Yes please Shane - that would work for me.

Thanks,

Roger

"ShaneDevenshire" wrote:

Hi,

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

--
Thanks,
Shane Devenshire


"Roger on Excel" wrote:

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

converting hyperlink to actual web address
 

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



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 wrote:

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
--
KATHERINE

"ShaneDevenshire" wrote:

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.
--
Thanks,
Shane Devenshire


"Roger on Excel" wrote:

Yes please Shane - that would work for me.

Thanks,

Roger

"ShaneDevenshire" wrote:

Hi,

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

--
Thanks,
Shane Devenshire


"Roger on Excel" wrote:

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

--

Dave Peterson

converting hyperlink to actual web address
 

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 ?

--
KATHERINE


"Dave Peterson" wrote:

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



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 wrote:

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
--
KATHERINE

"ShaneDevenshire" wrote:

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.
--
Thanks,
Shane Devenshire


"Roger on Excel" wrote:

Yes please Shane - that would work for me.

Thanks,

Roger

"ShaneDevenshire" wrote:

Hi,

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

--
Thanks,
Shane Devenshire


"Roger on Excel" wrote:

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

--

Dave Peterson

converting hyperlink to actual web address
 

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 he
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)


EXCEL HELP wrote:

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 ?

--
KATHERINE

"Dave Peterson" wrote:

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



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 wrote:

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
--
KATHERINE

"ShaneDevenshire" wrote:

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.
--
Thanks,
Shane Devenshire


"Roger on Excel" wrote:

Yes please Shane - that would work for me.

Thanks,

Roger

"ShaneDevenshire" wrote:

Hi,

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

--
Thanks,
Shane Devenshire


"Roger on Excel" wrote:

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

--

Dave Peterson


--

Dave Peterson