How do I convert a column of text URLs into active hyperlinks in Excel

Question

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

User · Answer

This method works for me using the hyperlink function    HYPERLINK  http     amp B10 B10    Where B10 is the cell containing the text version of the URL  in this example

User · Answer

You can insert the formula  HYPERLINK  lt your cell gt   lt your cell gt   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

User · Answer

Pretty easy way for rather short lists    Double click on the box where the url is Enter   You have your link

User · Answer

If you copy the text contents into a new column and use    HYPERLINK  http     amp B10 B10     on your original column  Then use the   for the column so it looks like this    HYPERLINK  http     amp  B10  B10    That s the only way it worked for me on Excel 2010 on Windows 7  You can copy down the formula

User · Answer

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

User · Answer

Create a temporary new column of hyperlinks using formula  HYPERLINK   Copy that column into Microsoft Word  copy to clipboard only after Word is running   Copy everything in the new word document  ctrl a  then ctrl c   Paste into Excel  replacing the original column of text  Delete the temporary column with the formula

User · Answer

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    Create a formula     hyperlink      amp  A1  amp        Drag down Copy new formula column Paste Special Values-only over the original column Highlight column  click Ctrl-H  to replace   finding and replacing   with    somehow forces re-evaluation of cells   Cells should now be clickable as hyperlinks   If you want the blue underline style  then just highlight all cells and choose the Hyperlink style    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

User · Answer

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    Copy the column of addresses to Notepad   Keeping ALT-SHIFT pressed  extended your cursor from the top left corner to the bottom left corner  and type  hyperlink    This adds  hyperlink   at the beginning of each entry  Open  Replace  menu  Ctrl-H   activate regular expressions  ALT-G   and replace    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  Paste back the data in Excel  In practice  just copy the data and select the first cell of the column where you want the data to end up

User · Answer

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        lt  gt  0 Then                 CellsWithSpaces   CellsWithSpaces  amp        amp  Replace xCell Address                            GoTo Nextxcell             End If              If InStr xCell Formula   http    lt  gt  0 Then                 Hyperstring   Trim xCell Formula              Else                 Hyperstring    http      amp  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    amp  CellsWithSpaces      End If Application ScreenUpdating   True End Sub

User · Answer

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   amp 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

User · Answer

With Excel 2007 on Windows  I found these steps simplest    Select cells with the non-active URLs Copy Paste as hyperlink

User · Answer

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

User · Answer

Try this    HYPERLINK  mailto   amp A1  A1   Replace A1 with your text of email address cell

User · Answer

I shocked Excel didn t do this automatically so here is my solution I hope would be useful for others     Copy the whole column to clipboard Open this on your Chrome or Firefox   data text html  lt button onclick  document write document body querySelector  textarea   value split   n   map x   gt    lt a href       x       gt     x     lt  a gt    join   lt br gt      gt Linkify lt  button gt  lt br gt  lt textarea gt  lt  textarea gt    Paste the column on the page you just opened on the browser and press  Linkify  Copy the result from the tab to the the column on Excel   Instead step two  you can use the below page  first  click on  Run code snippet  then paste the column on it  x000D   x000D   lt button onclick  document write document body querySelector  textarea   value split   n   map x   gt    lt a href       x       gt     x     lt  a gt    join   lt br gt      gt Linkify lt  button gt  lt br gt  lt textarea gt  lt  textarea gt  x000D   x000D   x000D

User · Answer

For anyone landing here with Excel 2016  you can simply highlight the column  then click the Hyperlink tab located on the Home ribbon in the Styles box     Edit  Unfortunately  this only updates the cell style  not the function

User · Answer

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

User · Answer

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    lt table gt   lt tr gt  lt td gt  lt a href  https   twitter com keeseter status 578350771235872768 photo 1  gt https   twitter com keeseter status 578350771235872768 photo 1 lt  a gt  lt  td gt  lt  tr gt   lt tr gt  lt td gt  lt a href  https   instagram com p ys5ASPCDEV   gt https   instagram com p ys5ASPCDEV  lt  a gt  lt  td gt  lt  tr gt   lt tr gt  lt td gt  lt a href  https   igcdn-photos-g-a akamaihd net hphotos-ak-xfa1 t51 2885-15 10881854 329617847240910 1814142151 n jpg  gt https   igcdn-photos-g-a akamaihd net hphotos-ak-xfa1 t51 2885-15 10881854 329617847240910 1814142151 n jpg lt  a gt  lt  td gt  lt  tr gt   lt tr gt  lt td gt  lt a href  https   twitter com ranadotson status 539485028712189952 photo 1  gt https   twitter com ranadotson status 539485028712189952 photo 1 lt  a gt  lt  td gt  lt  tr gt   lt tr gt  lt td gt  lt a href  https   instagram com p 0OgdvyxMhW   gt https   instagram com p 0OgdvyxMhW  lt  a gt  lt  td gt  lt  tr gt   lt  table gt    Then I loaded the links html into my browser  copied  pasted into Excel  and the links were active

User · Answer

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    Change http   to file      Removed all type of white space conditions Changed 10k cell range condition to 100k     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        lt  gt  0 Then                 Hyperstring   Trim xCell Formula              Else                 Hyperstring    file       amp  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

User · Answer

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

User · Answer

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   amp    http   amp        amp    amp   amp   amp      stackoverflow com questions   amp A1 amp      amp     amp A1 amp      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  amp     amp    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

User · Answer

Easiest way here   Highlight the whole column click   insert    click   Hyperlink    click   place in this document     click ok   thats all

User · Answer

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

[excel-2007] How do I convert a column of text URLs into active hyperlinks in Excel?

Examples related to excel-2007

Examples related to hyperlink