How do I Transfer the Cell Data from one Column to the Hyperlink Address for a Corresponding Column's cells?

Question

How do I transfer the cell data from one column to the Hyperlink address for a corresponding column's cells in Excel 2010?

Example:

Cell F4: www.microsoft.com
Cell F5: www.google.com

Cell C4: Microsoft
Cell C5: Google

I would like to make the cells in Column C hyperlinks to the addresses listed in Column F.

Is there a way?

Answer 1

You can use the HYPERLINK formula :

Either in another column:

=HYPERLINK(F4,C4)

Either if your URL are all the same, you can use Left, Right and Mid functions. Tell us if you need an example.

[EDIT : an example !] Let's assume your URL begins with "www." and ends with a dot "." and several characters (assuming we don't know how many).

=HYPERLINK(F4,PROPER(MID(F4;5;LEN(F4)-SEARCH(".";F4;LEN(F4)-5)-1)))

My excel version is not in english so tell me if it does not work. At least, i hope it will give you some clues on how to manage it.

Btw, some caveats :

• domain names cannot be longer than 4 char (cf. LEN(B1) - 5 part of the formula)
• if there are some dots inside the name, they will remain and the PROPER formula will upper the case --> this can be treated by a REPLACE formula

Hope that will help,

Regards,

Max