Take the 2-minute tour ×
Super User is a question and answer site for computer enthusiasts and power users. It's 100% free, no registration required.

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?

share|improve this question

1 Answer 1

up vote 2 down vote accepted

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

share|improve this answer
    
Wow, thanks for the answer! Can you give me an example? –  Blim Jun 22 '11 at 14:30
    
Also, for row C with the Data, how can I add the =Hyperlink formula without replacing the actual text? –  Blim Jun 22 '11 at 14:37
    
You'd better use another column with the formula. You can even hide the column C afterwards –  JMax Jun 22 '11 at 15:05
    
Thank you. When I create the new column, how can I have the formula generate the inputs automatically? So, =Hyperlink (F4,E4) (F5,E5) etc. –  Blim Jun 22 '11 at 16:51
    
not sure i understood your question but i'll give it a try : you can enter the formula in a new cell (let's say on G4) : =HYPERLINK(F4,C4) and then, you only have to drag it down (handyexceltips.com/2008/01/16/dragging-a-formula-down) to the end of you data. If it does not answer your question, feel free to let me know –  JMax Jun 23 '11 at 5:37

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.