Take the 2-minute tour ×
Super User is a question and answer site for computer enthusiasts and power users. It's 100% free, no registration required.

In Excel, I have a column of links that display text instead of the actual link.

I actually need the links, is there a way to bulk convert the entire column so that the links are exposed? Otherwise I will have to click on each link, open it in a browser, and copy-and-paste the address...

Also, I'm in Excel 2008 for Mac so it looks like I can't do macros...

share|improve this question

4 Answers 4

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.

  1. Highlight a cell with a hyperlink.
  2. 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.
  3. Press CTRL+A to highlight the entire URL.
  4. Press CTRL+C to copy it.
  5. Press ESC or Enter to close the Hyperlink dialog box.
  6. Paste the URL somewhere via CTRL + V.

enter image description here

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:

enter image description here

share|improve this answer
1  
That's a great snippet. For those wondering how to add this code to their worksheet... Press Alt + F11 to show the Visual Basic Editor. Then click "Insert -> Module". Paste the code Ellesa provided into the text area and you can then close the VBE and start using the function. –  BA_Webimax Feb 14 at 15:19

The best option I've found is to use the worksheet function HYPERLINK() to covert the URLs to links in another column. For example, if you have the text http://stackoverflow.com in A1, in B1 you could enter the following:

=HYPERLINK(A1,A1)

This will give you the URL as a hyperlink in B1. Of course you can fill this formula down to get your column of links.

Now, if you want to move these links into column A, there are two more steps. First, select all your links in column B, copy them, and then right-click in the same range. Choose 'Paste Special' then 'Values', and click 'OK'. This will replace the formulas in these cells with the contents of the cells. Next, you copy the links in B and just paste them into A over your old text.

share|improve this answer
    
This seems to do the opposite of what the question asks for. –  Scott Nov 18 '13 at 18:11
    
This does not work if the URL is over 255 characters. –  Nathan Jan 7 '14 at 18:31

This may sounds like a brute force method, but it's the best way I've come up with. (I discovered this page when I had the same question myself an hour ago... you could spend all day looking, but sometimes it's just easier to do it and be done with it.)

1) Copy and paste just the cells containing the hyperlinks to a new spreadsheet.

2) Export the spreadsheet, click "Change File Type," select "Save As Another File Type" and save it as a web page (aka an HTML file).

3) Open the web page in your browser and view the source.

4) Copy and paste the source into an text editor.

5) Using the Find/Replace function of the text editor, remove the leading and trailing text of each hyperlink. (In other words, replace said text with nothing: "") Note that this text should be identical for each hyperlink.

6) You should now have a text file with nothing but hyperlinks. Copy and paste as needed back into your original spreadsheet or use however/wherever you wish.

share|improve this answer

I'm sure there is with VBA however in the mean time right click, H, and copy and paste the address into the screen to display box

share|improve this answer

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.