Automatically hyperlink-ify large number cells containing URLs in Excel 2010

Question

I have an Excel spreadsheet with around 900 cells each of which contains a URL, which I've pasted in from another program.

If I select a cell and hit F2, then hit Enter, Excel adds a hyperlink to the cell, whose Address is the cell contents.

Is there a mechanism in Excel 2010 to repeat this step for my many hundreds of cells, without having to do the F2 Enter step manually?

(I know that I could automate it in AutoHotKey, but I'm hoping not to need to do that)

Clarification: I specifically want to end up with hyperlinks added to my existing cells, rather than creating a second column which contains hyperlinks.

Things I tried:

1. Creating a temporary extra column, with =Hyperlink(A1) in A2, as per user301746's answer, and then using Paste Special to copy-and-paste first the formatting, and then the value over A1. This appeared to work, in that the cells in A1 etc were shown in blue and underlined, so they looked like hyperlinks, but actually there weren't linked - it had only copied the hyperlink display style.

Answer 1

so I found a cheat method to this question, I had the same problem, needed to turn about 4000 cells into hyperlinks

I copied and pasted the entire column into a google sheet, the gsheet automatically turned all links into active links, then I re copied the column from the gsheet and pasted it back into the excel spreadsheet.

Answer 2

You could use =Hyperlink(A1) in A2 to do what you want, then copy it downwards with the cell fill tool.

Hope that helps.

Answer 3

OK, I've found a (slightly ugly) series of steps that at least ends up with hyperlinked text in the original column, using user301746's suggested use of the HYPERLINK function, but with only one column.

1. Copy all the Excel 2010 cells that contain the URLs
2. Paste the URLs into Notepad++
3. In Notepad++, convert all the URLs to Excel HYPERLINK formulae - using the mechanism described in this answer
   1. Control-H to bring up replace dialog
   2. Change the Search Mode to Regular Expression
   3. Enter (http.*) as the search criteria (find any line with http in it, and store in variable \1
   4. Enter =HYPERLINK\(\"\1\"\) as the replace criteria - replace what was found in the previous step with itself contained in HYPERLINK function call
   5. Replace All - now all your link lines are enclosed, any remaining lines must be titles
4. Copy all the text from Notepad++
5. Paste it over the original cells in Excel 2010. Now the Excel cell cells will all be hyperlinks, but they won't be formatted to look like hyperlinks
6. With the pasted cells still selected, click on the Hyperlink style button, in the Styles section of the Home tab of the Ribbon, to make them look like hyperlinks.

Answer 4

As an alternative to autohotkey it's fairly easy to code in vba. The following code would do what you want for alls the cells in column A starting in row 2.

Sub add_links()
Dim lRow As Long

    With ActiveSheet
        lRow = .Range("A" & .Rows.Count).End(xlUp).Row
        For Each c In .Range("A2:A" & lRow)
            ActiveSheet.Hyperlinks.add anchor:=c, Address:=c.Value
        Next
    End With

End Sub

Answer 5

@gtwebb thank you this was what I needed.

I modified it a bit to add input for which column

Sub add_links_Input_Column()
Dim lRow As Long
Dim ColHead As String

ColHead = InputBox("Enter Column Letter", "Identify Column", [c1].Value)
If ColHead = "" Then Exit Sub

    With ActiveSheet
        lRow = .Range(ColHead & .Rows.Count).End(xlUp).Row
        For Each c In .Range(ColHead & "2:" & ColHead & lRow)
            ActiveSheet.Hyperlinks.Add anchor:=c, Address:=c.Value
        Next
    End With

End Sub