#1 — Work with Active URLS

Don Tomoff
Nov 29, 2016 · 4 min read
Excel data file with list of active hyperlinks (Web URLs)

Introduction

I am a huge fan of the ASAP Utilities Excel-Addin tool. I can’t imagine using Excel without the functionality ASAP provides.

I am going to highlight my most frequent / favorite uses. The tool has not only made many tasks I need to perform in Excel much easier, but allowed me to do some things that prior to ASAP were impossible (at least for me…).


The first use I am going to highlight is working with Web URL’s which comes up periodically.

I use InstaPaper (see my prior Medium post) and periodically download an HTML format file of my saved content. I like to extract this information for permanent reference in a data file I maintain.


Step by Step — Download and Convert Weblinks

Here we go:

In this case, I am going to copy the links into Excel and “shape” them to provide the data structure I need. Here is the HTML file (downloaded from InstaPaper) opened in MS Word.

Instapaper HTML download viewed in MS Word

Copy the links and paste them into Excel. A total of 400+ links are pasted into Excel.

Hyperlinked HTML copied into Excel

Next, I want to EXTRACT the URL links from this and place them in the column to the right of the description.

  • Select “ASAP Utilities → Web → Extract hyperlinks…
ASAP Utilities “Extract Hyperlinks” menu option
ASAP Utilities “Extract Hyperlinks” Dialog Box
HTML Links with URLs Extracted by ASAP Utilities

Ok, that’s done. Now, I want to REMOVE THE HYPERLINK from the description (I just want a TEXT description). Then, I want the URLs to be an ACTIVE HYPERLINK that I can click on and go the the referenced content.

  • Select the rows, select “ASAP Utilities → Web → 7. Remove all hyperlinks from selected cells
ASAP Utilities “Remove Hyperlinks” menu option
Excel data — text description and web URLs

Now, convert the URLs an active hyperlink.

  • Select “ASAP Utilities → Web → 5. Activate hyperlinks (create from cell values)…
ASAP Utilities “Activate Hyperlinks” menu option
ASAP Utilities “Activate Hyperlinks” Dialog Box

Done — now the data is ready for the next steps!

Excel — Final data list ready for import to master data file

This data set gets added to my master data file containing content I have accumulated over the last 5 years. The content list is easily searchable and is an invaluable resource for my client sessions, presentations and speaking events.

Excel — Final data list resource

There you go. Certainly not an everyday or conventional use of ASAP Utilities, but it is ONE feature that I have integrated into my workflow and I can’t imagine not having it!


About Don

Don Tomoff is a “recovering CPA”, who is passionate about helping professionals and organizations keep up and adapt to the changing business world that we operate in.

One lesson learned over the years is that all of us, regardless of organization type or size, struggle with similar issues — primarily information management and presentation, and effective use of our time. Let’s change that…one person at a time!

Connect with Don!

LinkedIn, Flipboard, Twitter, Snapchat

Or, just Google me…I’m everywhere

Send Don an email

Let’s Excel

Up your Excel game — Tips, tricks and efforts that have exponentially increased my productivity!

Don Tomoff

Written by

Invenio Advisors. It’s time for DIFFERENT— On a mission to challenge the status quo to a more productive and effective end… #digital #Excel #data #analytics

Let’s Excel

Up your Excel game — Tips, tricks and efforts that have exponentially increased my productivity!

Welcome to a place where words matter. On Medium, smart voices and original ideas take center stage - with no ads in sight. Watch
Follow all the topics you care about, and we’ll deliver the best stories for you to your homepage and inbox. Explore
Get unlimited access to the best stories on Medium — and support writers while you’re at it. Just $5/month. Upgrade