This post comes on the tail end of a post I wrote the other day about top websites to submit your start-up. When writing that post I found myself needing to take a whole list of URLs and convert them into HTML styled hyperlinks. So each URL was formatted like this:

<a href="insetlink" target="_blank" rel="nofollow">hyperlink text</a>

Now obviously this is super easy to do because if you run a blog, you know enough html to write a hyperlink. But how do you do it in bulk? How do you format a list of a 100 URLs into this format without having to go one by one to make this happen? My first thought…Google Spreadsheets!

Why Use Google Spreadsheet to Make HTML Hyperlinks in Bulk

There are definitely more advanced individuals who can make this happen within WordPress, but for me whenever I need to do anything automated I turn to Excel. This is especially true when the logic makes sense for Excel. For example, writing formulas, repeating bits information, etc.

Writing the Formula to Make HTML Hyperlinks in Google Spreadsheets

Once I realized I wanted to use Google Spreadsheets to make this possible, I needed to figure out the right formula to use. Now if you have ever tried to look up an excel formula, it seems there are a million experts that have the solution you need. Each one has their own method they recommend. For me, I usually get lost because I’m not familiar enough with Excel when people start throwing out various terms like Macros. I am however, familiar enough to attempt to make some formulas work.

The formula that worked for me was the following:

Please note: I had to do an image because trying to insert the code was creating issues. You can get the actual formula in the Google Sheet.

This formula allowed me to add the specific link information and attributes to the respective cells in order to convert my list of URLs into a HTML hyperlinks.

Steps to Make HTML Hyperlinks in Google Spreadsheet

Since I created a Google Spreadsheet template that you can use, please reference that when going through these steps.

Step 1: Open the Google Spreadsheet

You can find the Google Spreadsheet here.

Step 2: Make a Copy of the Google Spreadsheet

Once the document is open, go to File > Download as > Microsoft Excel. The document will be downloaded to your desktop with the formulas contained.

Step 3: Add your URL Information

When you open the newly downloaded Excel file on your desktop, you will notice the first 3 rows contain Octatools information. You can leave these or delete them. The next row down, row 4 is where you will start to enter your details.

Below is a breakdown of what should go in each cell:

• A4- This is where you will put in the URL you want inserted into the formula
• B4- This is where you will put in the attributes you want for the HTML code
• C4- This is where you will put in the text you want for the hyperlink
• D4- This is where the formula is and will auto populate with the information from the other cells

Step 4: Add All URLs into Google Spreadsheet

The point of this tutorial was to allow you to add a big list of URLs and have the formula convert this into a list of hyperlinks for your website or blog. In order to complete this task, you need to do the following:

1. Paste all of your URLs in the A column
2. Drag the B column all the way down to where the URLs in column A stop
3. Drag the C column all the way down to where the URLs in column A and B stop
4. Drag the D column all the way down to where the URLs in column A, B, and C stop

This should populate all of the cells with the correct information. This is obviously assuming you want to use the same link attributes and hyperlink text for all links. If you don’t adjust the contents of the B column and C column according to your needs.

Step 5: Copying the URL List

This is slightly tricky, because if you just copy column D and try to paste it into a HTML supported location, you won’t get the information generated from the formula. In order for you to copy this information you need to do the following:

1. With the Excel document open, click File > Save As
2. Save as Text (tab Delimited)
3. Open your preferred browser and pull up a new Google Spreadsheet
4. With new Google Spreadsheet open, go File > Import > Upload > Select newly saved Txt document
5. When the new box pops up, select Import Action > Insert new sheet (s) and Separator character > Detect automatically
6. This will import all of the information, including the text from our formula into the Google Spreadsheet
7. You’ll notice in column D the HTML hyperlink formula that we want to copy into our HTML document- Copy this list

Step 6: Copy and Paste URL List into HTML Document

Now we are ready to add our URL list into the preferred HTML document. Just copy the information contained in column D and paste that wherever your heart desires. You should now see a formatted HTML list with the desired attributes and anchor text you needed.

About the Author

Michael Keating

Twitter Google+

Mike is a prolific digital marketing strategist, entrepreneur and SEO specialist who understands how to drive results using integrated digital strategies. He is one of the founders of Octatools and is excited about the opportunity to help DIY SEOs and business owners get results online.