Excel: convert a column with hyperlinks to just display the links
Excel: convert a column with hyperlinks to just display the links
Excel: convert a column with hyperlinks to just display the links
Re: Excel: convert a column with hyperlinks to just display the links
**Short answer:** you can't automate this without VBA.
Manual
This is on a Windows 7 computer; just replace the shortcuts with their corresponding Mac counterparts.
- Highlight a cell with a hyperlink.
- Press CTRL+K. This opens the hyperlink dialog box (see image below). Once it opens, you'll find that your cursor is already in the Address field.
- Press CTRL+A to highlight the entire URL.
- Press CTRL+C to copy it.
- Press ESC or Enter to close the Hyperlink dialog box.
- Paste the URL somewhere via CTRL + V.
VBA
Here's a VBA solution for those who can use it.
Create a VBA module with the following code:
```
Public Function GetURL(c As Range) As String
On Error Resume Next
GetURL = c.Hyperlinks(1).Address
End Function
```
To use, enter the following into any cell:
```
=GetURL(A1)
```
where A1 contains a hyperlink.
The function at work:
Manual
This is on a Windows 7 computer; just replace the shortcuts with their corresponding Mac counterparts.
- Highlight a cell with a hyperlink.
- Press CTRL+K. This opens the hyperlink dialog box (see image below). Once it opens, you'll find that your cursor is already in the Address field.
- Press CTRL+A to highlight the entire URL.
- Press CTRL+C to copy it.
- Press ESC or Enter to close the Hyperlink dialog box.
- Paste the URL somewhere via CTRL + V.
VBA
Here's a VBA solution for those who can use it.
Create a VBA module with the following code:
```
Public Function GetURL(c As Range) As String
On Error Resume Next
GetURL = c.Hyperlinks(1).Address
End Function
```
To use, enter the following into any cell:
```
=GetURL(A1)
```
where A1 contains a hyperlink.
The function at work: