Super User is a question and answer site for computer enthusiasts and power users. Join them; it only takes a minute:

Sign up
Here's how it works:
  1. Anybody can ask a question
  2. Anybody can answer
  3. The best answers are voted up and rise to the top

I'm trying to construct a hyperlink from a URL and a name in different cells.

a1 = http://www.example.com/
b1 = example
c1 = <a href="http://www.example.com/">example</a>

What I want that is the URL in A1 to is made into a hyperlink with the name taken from B1:

<a href="http://www.example.com/">example</a>

I created a formula, but it's not working

<a href=&”=a1”>&”=b1”</a>

should result in

<a href="http://www.example.com/">example</a>
share|improve this question
2  
What topics did you find online and why did they not work? Can you link them please - so we know what you've tried. Also please try to clarify your aim of the formula. – HaydnWVN Aug 2 '12 at 9:31
1  
Welcome to Super User! We had a hard time trying to figure out what you were actually asking. Please check my edit to your post and tell us if that is what you wanted. Also check the source of this post to see the formatting I used. Think about your readers, they might not be able to guess what you really want. – slhck Aug 2 '12 at 9:34

It is very difficult to understand your question, especially when you don't respond to comments/answers.

That said, here's one interpretation of your question:

You want cell C1 to display the text shown in cell B to be a hyperlink to the URL specified in Cell A.

To achieve this, type this into cell C1

=HYPERLINK(A1,B1)

An alternative interpretation is that you want a formula in cell C1 that returns the value

<a href="http://www.example.com/">example</a>

to achieve this, type this into cell C1

="<a href=""" & A1 & """>" & B1 & "</a>"
share|improve this answer

The word you want is Concatenate.

=CONCATENATE("<a href=",CHAR(34),A1,CHAR(34),">", B1, "</a>")
share|improve this answer

Try using this formula in C1

="<a href="&""""&A1&""">"&B1&"</a>"

I will try and break this down to explain it to you.

Firstly, every formula starts with an "=" in excel. Everything that you do not want excel to try and evaluate you put in double quotes for example excel will display this

"<a href="

As just text. but will display

"<a href="& A1

as

<a href =[Whatever was in cell A1]
share|improve this answer

I managed to do it with an extra sheet in excel with some coding.

I created an extra Excel sheet called that I use for pieces of coding. I put it in the end of all my normal sheets that I use and use the naming <[name]> to indicate that it is technical stuff.

I created the following parts of codes in cells in this sheet, as text:

Cell A1: 1. Hyperlink generation formula (just a title for my own to remember what is this for)

Cell A3: < a href=" (without the space between < and a)

Cell A4: ">

Cell A5: < /a> (without the space in between < and /)

In my normal sheets I have the two columns, filled with the data: 1: TITLE 2: URL

In total the following formula now works, by getting this coding text data from the sheet:

='>Lists<'!$A$3&'SEO Pages Posts'!D2&'>Lists<'!B4&'SEO Pages Posts'!C2&'>Lists<'!$B$5

An extra explanation of how this formula works:

It is a construct of 5 input fields, divided by the & symbols in this formula:

  1. coding from the Lists sheet cell A3, locked cell with double $ to lock cell

  2. URL, made variable so you can copy and paste this formula to the next row

  3. coding from the Lists sheet cell A4, locked cell with double $ to lock cell
  4. TITLE, made variable so you can copy and paste this formula to the next row
  5. coding from the Lists sheet cell A5, locked cell with double $ to lock cell

Take a moment to digest all of this, and this should be a solution for you, by just having a working formula BUT with an extra sheet for the coding.

If I do it in another way I got errors with creating "< a href=" " (without spaces), where the formula gots confused with the double " in the end.

Wish you all the best.

share|improve this answer

You must log in to answer this question.

Not the answer you're looking for? Browse other questions tagged .