Trimming URLs in Excel

If you find a massive list of blogs, websites or similar that you want to add to a list, it can be a pain in the neck.

Getting each URL individually from this list for example = massive ball ache

Lis of blogs
http://topnonprofits.com/lists/nonprofit-blogs/

Here’s how to make the process as easy as possible:

Press ctrl + U (or right click and ‘view source/code’)

Search for the name of the first blog/website in the list and copy all the code down to the last blog/website in the list.

Paste all of the code that you have just copied into the Buzzstream tool

Buzzstream
http://tools.buzzstream.com/link-building-extract-urls


Copy and paste the extracted URLs into an excel document.

If the URLs still need trimming, try this:

URL trimmer excel

 

In a cell, let’s say cell E3 – add this code –

=LEFT(A3,FIND("/",A3,1)-1)

With your URLs copied, right click on cell A3 and paste all of your URLs

In Cell E3, you should see a shortened version of the URL   – the code will removed any text after the first forward slash /

Note – you may have to remove the http:// first, otherwise the code will shorten everything to http:/

The code to remove the first 7 characters (and therefore the http://) is =RIGHT(A3,LEN(A3)-7)

 

To remove everything after the final trailing slash (e.g. tracking parameters) use the following:

=SUBSTITUTE(A2,TRIM(LEFT(RIGHT(SUBSTITUTE(A2,"/",REPT(" ",100)),100),100)),"")

In fact, just use this spreadsheet:
URLShortner_BlogLists

Advertisements

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Google+ photo

You are commenting using your Google+ account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

w

Connecting to %s