How to get 500 hyperlink address efficiently?
 

I have 500 datarow in a excel file. The text in the first column inclues
hyperlink address. I need to get all hyperlink address, but I don't want to
copy them one by one.
How do I get them efficiently？

How to get 500 hyperlink address efficiently?
 

How about using another column of formulas:

=hyperlink(a1,"Click me")
or
=hyperlink("http://" & a1, "click me")

It'll depend on what you have in those cells.

liru wrote:

I have 500 datarow in a excel file. The text in the first column inclues
hyperlink address. I need to get all hyperlink address, but I don't want to
copy them one by one.
How do I get them efficiently？

--

Dave Peterson

How to get 500 hyperlink address efficiently?
 

I can't see the hyperlink address directly.
The address is hidden.
For example,
I see Google, but I don't see http://www.google.com.

If I want to get the address, I will do four steps.
step1:click right button
step2:edit hyperlink
step3:cpoy address
step4:past it to other column

It's so boriing because I have 587 rows.

Do you have any good way to solve the problem?

"Dave Peterson" wrote:

How about using another column of formulas:

=hyperlink(a1,"Click me")
or
=hyperlink("http://" & a1, "click me")

It'll depend on what you have in those cells.

liru wrote:

I have 500 datarow in a excel file. The text in the first column inclues
hyperlink address. I need to get all hyperlink address, but I don't want to
copy them one by one.
How do I get them efficiently？

--

Dave Peterson

How to get 500 hyperlink address efficiently?
 

You can use a User defined function to retrieve the link.

Option Explicit
Function GetURL(Rng As Range) As String
Application.Volatile

Set Rng = Rng(1)

If Rng.Hyperlinks.Count = 0 Then
GetURL = ""
Else
GetURL = Rng.Hyperlinks(1).Address
End If
End Function

So if you had a hyperlink in A1, you could put =getURL(a1) in that adjacent
cell.

Be aware that if you change the hyperlink, then this formula cell won't change
until your workbook calculates.

If you're new to macros, you may want to read David McRitchie's intro at:
http://www.mvps.org/dmcritchie/excel/getstarted.htm

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:
=getURL(a1)


liru wrote:

I can't see the hyperlink address directly.
The address is hidden.
For example,
I see Google, but I don't see http://www.google.com.

If I want to get the address, I will do four steps.
step1:click right button
step2:edit hyperlink
step3:cpoy address
step4:past it to other column

It's so boriing because I have 587 rows.

Do you have any good way to solve the problem?

"Dave Peterson" wrote:

How about using another column of formulas:

=hyperlink(a1,"Click me")
or
=hyperlink("http://" & a1, "click me")

It'll depend on what you have in those cells.

liru wrote:

I have 500 datarow in a excel file. The text in the first column inclues
hyperlink address. I need to get all hyperlink address, but I don't want to
copy them one by one.
How do I get them efficiently？

--

Dave Peterson


--

Dave Peterson

How to get 500 hyperlink address efficiently?
 

Thank you very much.
You solve my problem.

--
liru from Taiwan.


"Dave Peterson" wrote:

You can use a User defined function to retrieve the link.

Option Explicit
Function GetURL(Rng As Range) As String
Application.Volatile

Set Rng = Rng(1)

If Rng.Hyperlinks.Count = 0 Then
GetURL = ""
Else
GetURL = Rng.Hyperlinks(1).Address
End If
End Function

So if you had a hyperlink in A1, you could put =getURL(a1) in that adjacent
cell.

Be aware that if you change the hyperlink, then this formula cell won't change
until your workbook calculates.

If you're new to macros, you may want to read David McRitchie's intro at:
http://www.mvps.org/dmcritchie/excel/getstarted.htm

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:
=getURL(a1)


liru wrote:

I can't see the hyperlink address directly.
The address is hidden.
For example,
I see Google, but I don't see http://www.google.com.

If I want to get the address, I will do four steps.
step1:click right button
step2:edit hyperlink
step3:cpoy address
step4:past it to other column

It's so boriing because I have 587 rows.

Do you have any good way to solve the problem?

"Dave Peterson" wrote:

How about using another column of formulas:

=hyperlink(a1,"Click me")
or
=hyperlink("http://" & a1, "click me")

It'll depend on what you have in those cells.

liru wrote:

I have 500 datarow in a excel file. The text in the first column inclues
hyperlink address. I need to get all hyperlink address, but I don't want to
copy them one by one.
How do I get them efficiently？

--

Dave Peterson


--

Dave Peterson