I have a column in excel, wherein I have all the website url values. My question is I want to turn the url values to active links. There are about 200 entries in that column with different urls in all cells. Is there a way I can create active hyperlinks to all the cells without writing a macro.
This question is related to
excel-2007
hyperlink
OK, here's a hokey solution, but I just can't figure out how to get Excel to evaluate a column of URLs as hyperlinks in bulk.
="=hyperlink(""" & A1 & """)"
=
with =
(somehow forces re-evaluation of cells).The hyperlink style alone won't convert to clickable links, and the "Insert Hyperlink" dialog can't seem to use the text as the address for a bunch of cells in bulk. Aside from that, F2 and Enter through all cells would do it, but that's tedious for a lot of cells.
This method works for me using the hyperlink function:
=HYPERLINK("http://"&B10,B10)
Where B10
is the cell containing the text version of the URL (in this example).
I had a list of numbers that feed into url's I want hotlinked. For example I have Column A with question numbers (i.e., 2595692, 135171) and I want to turn these question numbers into hotlinks and to display only the question numbers.
So I built a text-only hyperlink pointing to Column A, and copied it down for all my question numbers:
="=HYPERLINK("&"""http""&"":"""&""&"&"&"""//stackoverflow.com/questions/"&A1&""""&","&A1&")"
Then I copy - paste value this column of text hyperlinks to another column.
You end up with a column of text that looks like the following:
=HYPERLINK("http"&":"&"//stackoverflow.com/questions/2595692",2595692)
Then I selected these pasted items and ran the F2Entry Macro that follows:
Sub F2Enter()
Dim cell As Range
Application.Calculation = xlCalculationManual
For Each cell In Selection
cell.Activate
cell = Trim(cell)
Next cell
Application.Calculation = xlCalculationAutomatic
EndSub
I then deleted the text entry column and Column A.
I ended up with a single column of hotlinked question numbers:
2595692
135171
etc.
Cheers
The simplest way in Excel 2010: Select the column with the URL text, then select Hyperlink Style from the Home tab. All URLs in the column are now hyperlinks.
Also double clicking each cell at the end of the URL text and adding a blank or just enter will also produce a hyperlink. Similar to the way you have to create URL links in MS Outlook emails.
Here's a way I found. I'm on a Mac using Excel 2011.
If column B had the text values you want to be hyperlinks, put this formula in the cell C1 (or D1 or whatever as long as it's a free column): =HYPERLINK(B1,B1)
This will insert a hyperlink with the location as the link text and the "friendly name" as the link text. If you have another column that has a friendly name for each link, you could use that too.
Then, you could hide the text column if you didn't want to see it.
If you have a list of IDs of something, and the urls were all http://website.com/folder/ID
, such as:
A1 | B1
101 | http://website.com/folder/101
102 | http://website.com/folder/102
103 | http://website.com/folder/103
104 | http://website.com/folder/104
you could use something like =HYPERLINK("http://website.com/folder/"&A1,A1)
and you wouldn't need the list of urls. That was my situation and worked nicely.
According to this post: http://excelhints.com/2007/06/12/hyperlink-formula-in-excel/ this method will work in Excel 2007 as well.
If you copy the text contents into a new column and use:
=HYPERLINK("http://"&B10,B10)
on your original column. Then use the $
for the column so it looks like this:
=HYPERLINK("http://"&$B10,$B10)
That's the only way it worked for me on Excel 2010 on Windows 7. You can copy down the formula.
Pretty easy way for rather short lists:
You have your link ;)
Thank you Cassiopeia for code. I change his code to work with local addresses and made little changes to his conditions. I removed following conditions:
http:/
to file:///
Sub HyperAddForLocalLinks()
Dim CellsWithSpaces As String
'Converts each text hyperlink selected into a working hyperlink
Application.ScreenUpdating = False
Dim NotPresent As Integer
NotPresent = 0
For Each xCell In Selection
xCell.Formula = Trim(xCell.Formula)
If InStr(xCell.Formula, "file:///") <> 0 Then
Hyperstring = Trim(xCell.Formula)
Else
Hyperstring = "file:///" & Trim(xCell.Formula)
End If
ActiveSheet.Hyperlinks.Add Anchor:=xCell, Address:=Hyperstring
i = i + 1
If i = 100000 Then Exit Sub
Nextxcell:
Next xCell
Application.ScreenUpdating = True
End Sub
I shocked Excel didn't do this automatically so here is my solution I hope would be useful for others,
data:text/html,<button onclick="document.write(document.body.querySelector('textarea').value.split('\n').map(x => '<a href=\'' + x + '\'>' + x + '</a>').join('<br>'))">Linkify</button><br><textarea></textarea>
Instead step two, you can use the below page, first, click on "Run code snippet" then paste the column on it
<button onclick="document.write(document.body.querySelector('textarea').value.split('\n').map(x => '<a href=\'' + x + '\'>' + x + '</a>').join('<br>'))">Linkify</button><br><textarea></textarea>
_x000D_
For me I just copied the entire column which has the URLs in text format into another application (say Evernote), and when they were pasted there they became links, and then I just copied them back into Excel.
The only thing here is you need to make sure the data you copy back lines up with the rest of the columns.
Put the URLs into an HTML table, load the HTML page into a browser, copy the contents of that page, paste into Excel. At this point the URLs are preserved as active links.
Solution was proposed on http://answers.microsoft.com/en-us/mac/forum/macoffice2008-macexcel/how-to-copy-and-paste-to-mac-excel-2008-a-list-of/c5fa2890-acf5-461d-adb5-32480855e11e by (Jim Gordon Mac MVP)[http://answers.microsoft.com/en-us/profile/75a2b744-a259-49bb-8eb1-7db61dae9e78]
I found that it worked.
I had these URLs:
https://twitter.com/keeseter/status/578350771235872768/photo/1 https://instagram.com/p/ys5ASPCDEV/ https://igcdn-photos-g-a.akamaihd.net/hphotos-ak-xfa1/t51.2885-15/10881854_329617847240910_1814142151_n.jpg https://twitter.com/ranadotson/status/539485028712189952/photo/1 https://instagram.com/p/0OgdvyxMhW/ https://instagram.com/p/1nynTiiLSb/
I put them into an HTML file (links.html) like this:
<table>
<tr><td><a href="https://twitter.com/keeseter/status/578350771235872768/photo/1">https://twitter.com/keeseter/status/578350771235872768/photo/1</a></td></tr>
<tr><td><a href="https://instagram.com/p/ys5ASPCDEV/">https://instagram.com/p/ys5ASPCDEV/</a></td></tr>
<tr><td><a href="https://igcdn-photos-g-a.akamaihd.net/hphotos-ak-xfa1/t51.2885-15/10881854_329617847240910_1814142151_n.jpg">https://igcdn-photos-g-a.akamaihd.net/hphotos-ak-xfa1/t51.2885-15/10881854_329617847240910_1814142151_n.jpg</a></td></tr>
<tr><td><a href="https://twitter.com/ranadotson/status/539485028712189952/photo/1">https://twitter.com/ranadotson/status/539485028712189952/photo/1</a></td></tr>
<tr><td><a href="https://instagram.com/p/0OgdvyxMhW/">https://instagram.com/p/0OgdvyxMhW/</a></td></tr>
</table>
Then I loaded the links.html into my browser, copied, pasted into Excel, and the links were active.
If you don't want to make a macro and as long as you don't mind an additional column, then just create a new column alongside your column of URLs.
In the new column type in the formula =HYPERLINK(A1)
(replacing A1 with whatever cell you are interested in). Then copy the formula down the rest of the 200 entries.
NOTE: This solution does not work if the cell A1 contains a string longer than 255 characters. It results in a #VALUE!
error
Easiest way here
You can insert the formula =HYPERLINK(<your_cell>,<your_cell>)
to the adjacent cell and drag it along all the way to the bottom. This will give you a column with all the links. Now, you can select your original column by clicking on the header, right-click, and select Hide
.
If adding an extra column with the hyperlinks is not an option,
the alternative is to use an external editor to enclose your hyperlink into =hyperlink("
and ")
, in order to obtain =hyperlink("originalCellContent")
If you have Notepad++, this is a recipe you can use to perform this operation semi-automatically:
=hyperlink("
. This adds =hyperlink("
at the beginning of each entry.$
(end of line) with "\)
. This adds a closed quote and a closed parenthesis (which needs to be escaped with \
when regular expressions are activated) at the end of each line.There is a very simple way to do this. Create one hyperlink, and then use the Format Painter to copy down the formatting. It will create a hyperlink for every item.
I found that none of the methods here worked if the hyperlink did not include http:// as they linked to local locations.
I also wanted to fool-proof the script as the users would not be able to maintain it themselves and I would not be available.
It will only run on cells in a selected range if they contain a dot and no spaces. It will only run for up to 10,000 cells.
Sub HyperAdd()
Dim CellsWithSpaces As String
'Converts each text hyperlink selected into a working hyperlink
Application.ScreenUpdating = False
Dim NotPresent As Integer
NotPresent = 0
For Each xCell In Selection
xCell.Formula = Trim(xCell.Formula)
If xCell.Formula = "" Or InStr(xCell.Formula, ".") = NotPresent Then
'Do nothing if the cell is blank or contains no dots
Else
If InStr(xCell.Formula, " ") <> 0 Then
CellsWithSpaces = CellsWithSpaces & ", " & Replace(xCell.Address, "$", "")
GoTo Nextxcell
End If
If InStr(xCell.Formula, "http") <> 0 Then
Hyperstring = Trim(xCell.Formula)
Else
Hyperstring = "http://" & Trim(xCell.Formula)
End If
ActiveSheet.Hyperlinks.Add Anchor:=xCell, Address:=Hyperstring
End If
i = i + 1
If i = 10000 Then Exit Sub
Nextxcell:
Next xCell
If Not CellsWithSpaces = "" Then
MsgBox ("Please remove spaces from the following cells:" & CellsWithSpaces)
End If
Application.ScreenUpdating = True
End Sub
With Excel 2007 on Windows, I found these steps simplest;
Create the macro as here:
On the Tools menu in Microsoft Excel, point to Macro, and then click Visual Basic Editor. On the Insert menu, click Module. Copy and paste this code into the code window of the module. It will automatically name itself HyperAdd.
Sub HyperAdd()
'Converts each text hyperlink selected into a working hyperlink
For Each xCell In Selection
ActiveSheet.Hyperlinks.Add Anchor:=xCell, Address:=xCell.Formula
Next xCell
End Sub
When you're finished pasting your macro, click Close and Return to Microsoft Excel on the File menu.
Then select the required cells and click macro and click run.
NOTE Do NOT select the whole column! Select ONLY the cells you wish to be changed to clickable links else you will end up in a neverending loop and have to restart Excel! Done!
Try this:
=HYPERLINK("mailto:"&A1, A1)
Replace A1 with your text of email address cell.
Source: Stackoverflow.com