Giúp em lấy link liên kết trong excel (1 người xem)
- Thread starter dongpv.hr
- Ngày gửi
Người dùng đang xem chủ đề này
quangdiepctmbk
Thành viên hoạt động
- Tham gia
- 2/4/08
- Bài viết
- 169
- Được thích
- 52
- Nghề nghiệp
- Ky su
Bạn muốn lấy Link nào vậy?
- Tham gia
- 2/8/10
- Bài viết
- 5,225
- Được thích
- 9,027
- Giới tính
- Nam
Một hình thức quảng cáo chăng?
Ah...được rồi...cảm ơn anh chị.
How to extract a URL from a hyperlink on Excel
By KAREN | Published: FEBRUARY 18, 2010
Today, I faced the problem of extracting URLs from a long list of hyperlinked text. I had two options:
First is to do it manually:
1. Right-click a hyperlink.
2. From the Context menu, choose Edit Hyperlink. Excel displays the Edit Hyperlink dialog box.
3. Select and copy (Ctrl+C) the entire URL from the Address field of the dialog box.
4. Press Esc to close the Edit Hyperlink dialog box.
5. Paste the URL into any cell desired.
But then I have a long list of hyperlinks in my Excel worksheet. If I had to do this for each and every single hyperlink, this can get tedious very very quickly. So the second option is to get the URLs using a macro.
The following example can be useful when extracting hyperlinks from tables of links that have been copied into Excel, when doing post-processing on Excel web queries, or getting the email address from a list of “mailto:” hyperlinks.
Extracting a URL from a hyperlink on Excel is easy!
1. Open up a new workbook.
2. Get into VBA (Press Alt+F11)
3. Insert a new module (Insert > Module)
4. Copy and Paste the Excel user defined function below
5. Get out of VBA (Press Alt+Q)
6. Use this syntax for this custom Excel function: =GetURL(cell,[default_value])
Function GetURL(cell As range, _
Optional default_value As Variant)
'Lists the Hyperlink Address for a Given Cell
'If cell does not contain a hyperlink, return default_value
If (cell.range("A1").Hyperlinks.Count <> 1) Then
GetURL = default_value
Else
GetURL = cell.range("A1").Hyperlinks(1).Address
End If
End Function
This entry was posted in Macros. Bookmark the permalink. Post a comment or leave a trackback: Trackback URL.
How to extract a URL from a hyperlink on Excel
By KAREN | Published: FEBRUARY 18, 2010
Today, I faced the problem of extracting URLs from a long list of hyperlinked text. I had two options:
First is to do it manually:
1. Right-click a hyperlink.
2. From the Context menu, choose Edit Hyperlink. Excel displays the Edit Hyperlink dialog box.
3. Select and copy (Ctrl+C) the entire URL from the Address field of the dialog box.
4. Press Esc to close the Edit Hyperlink dialog box.
5. Paste the URL into any cell desired.
But then I have a long list of hyperlinks in my Excel worksheet. If I had to do this for each and every single hyperlink, this can get tedious very very quickly. So the second option is to get the URLs using a macro.
The following example can be useful when extracting hyperlinks from tables of links that have been copied into Excel, when doing post-processing on Excel web queries, or getting the email address from a list of “mailto:” hyperlinks.
Extracting a URL from a hyperlink on Excel is easy!
1. Open up a new workbook.
2. Get into VBA (Press Alt+F11)
3. Insert a new module (Insert > Module)
4. Copy and Paste the Excel user defined function below
5. Get out of VBA (Press Alt+Q)
6. Use this syntax for this custom Excel function: =GetURL(cell,[default_value])
Function GetURL(cell As range, _
Optional default_value As Variant)
'Lists the Hyperlink Address for a Given Cell
'If cell does not contain a hyperlink, return default_value
If (cell.range("A1").Hyperlinks.Count <> 1) Then
GetURL = default_value
Else
GetURL = cell.range("A1").Hyperlinks(1).Address
End If
End Function
This entry was posted in Macros. Bookmark the permalink. Post a comment or leave a trackback: Trackback URL.
