Add "http://" Prefix to a List of Domains Formatted in Plain Text :: How Please?

February 21, 2006 10:05 PM

Hi, hoping some MS Excel or HTML wizards can help me out with this. The goal is as follows; format a list of domain names so I can publish them on a webpage with hyperlinks to their individual addresses (currently they are all in Excel with each domain occupying a single cell in a single row; they are currently formatted like this: "domain.com"). There are just to many to go through and add this to each one.

I'm thinking that one of these two solutions might work (just can't figure out how to do it!):
1) Use some formula in MS Excel to add the "http://" to each of the cells with a domain. Then just copy and paste into my HTML editor.

OR

2) Use some sort of code directly in the HTML editor to change "domain.com" into hyperlinked text.

This link came close, but just missed. Thanks so much for any help you might provide! Cheers!

posted by Jhaus to computers & internet (8 comments total)

Solution #1, using Excel

Say that Cell A1 contains example.com. Make Cell B1 say:
=CONCATENATE("http://",A1)
or
="http://" & A1

And then drag the lower right corner to all cells in column B, assuming column A has all your *.com fields. Voila! Column B is your answer.

Oh, I re-read and see that you have them in rows. Do the same thing, only use cell A2, and use Row 2 for your answers.
posted by SuperNova at 10:15 PM on February 21, 2006

You could probably just record a macro. Start recording, insert http:// at the start of the current cell, move down one cell, stop recording. Play macro however many times you need to.

If you need a full A HREF link, that would be more involved.. you'd have to write a short VBA script, probably. The basic algorithm would be:

Go to cell 1;
load cell in A$
erase cell
insert '<A HREF="http://' + A$ + '">' + A$ + '</A>'
Move down a cell
If cell not empty, repeat.

Note that you may have trouble with quotes... I used single quotes so that the double-quotes of the HTML link would come through. But I don't remember if VBA supports that syntax. You might have to make a quote variable containing the " character, and then glue it into the correct places in the string.

I'm also not sure if + is string concatenation in VBA... if it's not, just substitute whatever the right character is. It might be &.
posted by Malor at 10:16 PM on February 21, 2006

Malor, I think that's way too involved. Excel is decent at combining strings, even without VBA.

I worked up this to generate a hyperlink. This is working under the assumption that your first row (not column) is written like:
_______A__________B__________C_____
1| example.com | sample.com | website.com

You then add the following lines:
_______A________
1| example.com
2|<a href="http://
3| ">
4| </a>
5|=A2&A1&A3&A1&A4

which yields in A5 the following:
<a href="http://example.com">example.com</a>

I copied this directly from the sheet, as the default is to copy the answer from the formula, not the formula; should make your job easier.

You can always change the second A1 reference if you want the link to say something else.

Hope this helps!
posted by SuperNova at 10:27 PM on February 21, 2006 [1 favorite]

I do regularly do exactly what SuperNova does, only with my domains in columns. It works perfectly.
posted by aisforal at 10:49 PM on February 21, 2006

perl -nle 'print "<a href=\"http://$_\">$_</a>"' domains.txt

sed 's#.*#<a href="http://&">&</a>#' domains.txt

everything is easier in the unix.
posted by kcm at 11:39 PM on February 21, 2006

OOPS...I meant to say that they are in a single COLUMN, not a single Row...Sorry.

Regardless, this is some great stuff! I think I'll be able to work it out from here. Thanks so much for all of your help! How'd you guys get so knowledgeable with this stuff? Thanks again.
posted by Jhaus at 1:16 AM on February 22, 2006

Oh ok... columns makes more sense, to me anyway. Let me know if you need help converting what I put into columns, or with anything else. Hopefully this will take care of your issue.

How'd I learn this? I think it's a combination of one very basic class I took on MS Office, plus a lot of time spent saying, "I know Excel can solve this problem, let me find the formula to do it." Oh, and from reading AskMe. Seriously.
posted by SuperNova at 2:07 AM on February 22, 2006

The simplest way is to save the Excel spreadsheet as a text file. Open it in Microsoft Word. In Word, use search and replace. Search for

^p

That will find all line breaks. Replace it with

"</a>^p<a href="http://

Include the quote marks. Then you'll have to just fix the very first domain and the very last domain by hand. Save the Word file as text and then open it up in Excel again.
posted by Mo Nickels at 6:57 AM on February 23, 2006