HELP! Brand new user to excel and having problems...

[rdenny1900]

Hi there. Brand new to the board and I am trying to find the answer to a
question. I need some serious help here from a total beginner to excel.
I have never used functions.

I have a list of 500 domain names that are listed in the first column
in excel. In the second column I have more text. In the Third column I
am trying to have the text of the first and second column inserted into
a phrase in the second column. Example:

Lets say this is in column 1:
COLUMN1TEXT

This is in Column 2:
COLUMN2TEXT

This is what I want in column 3:
<a href="http://COLUMN1TEXT" target="_blank"
title="COLUMN2TEXT">COLUMN1TEXT</a><br>

Does anyone know how in the world to do this? Do I need to create a
function to do this? I would really appreciate any help anyone can give
me!! Thanks, Ron

 

[Max]

Not sure, but see whether this helps a bit ..
(we're just concatenating the bits together)

Put in C1:
="<a href=http://"&A1&" target="&"_ "&" title="&B1&">"&A1&"</a><br>"
Copy down

If col C returns what you want, then you could freeze the values in the col
via
an in-place copy > paste special > values > ok

 

[rdenny1900]

Wow, what a quick reply......and thank you!!

Ok, I am starting to get excited because its kind of working.

When I put in this forumula:

="<a href=http://"&A1&" target="&"_ "&" title="&B1&">"&A1&"</a><br>"

In column 3 I get:

<a href=http://ABORTIONLA.WS target=_ title=ABORTION
LAWS>ABORTIONLA.WS </a><br>

Theres a couple of problems. First is that I need it to say:

<a href="http://ABORTIONLA.WS" target="_blank" title="ABORTION
LAWS">ABORTIONLA.WS </a><br>

So pretty much its missing the quotation marks. Thats not the big
issue because I can fix that stuff later with replace command.

The big issue is that when I paste the formula in column 3 after the
first line, (example: Line 2, Line 3, Line 4, etc...) it keeps giving
me the values from LINE 1 (column 1 and 2). I see that the variables
A1 and B1 keep getting used but I guess I need the values from each
individual line to corrospond to that line. Like for line two I guess
the variables need to say A2 and B2. I am not sure how to do that tho
and typing in the new values in the formula are just not feasable. I
am sure I am just doing something stupid because I have never used this
program before, but cant get it to work.

Honestly, I cant believe I got it working this much!!

Thank you so much for all your help!
Ron

 

[Bob Phillips]

Wow, what a quick reply......and thank you!!

Theres a couple of problems. First is that I need it to say:

<a href="http://ABORTIONLA.WS" target="_blank" title="ABORTION
LAWS">ABORTIONLA.WS </a><br>

So pretty much its missing the quotation marks. Thats not the big
issue because I can fix that stuff later with replace command.

The big issue is that when I paste the formula in column 3 after the
first line, (example: Line 2, Line 3, Line 4, etc...) it keeps giving
me the values from LINE 1 (column 1 and 2). I see that the variables
A1 and B1 keep getting used but I guess I need the values from each
individual line to corrospond to that line. Like for line two I guess
the variables need to say A2 and B2. I am not sure how to do that tho
and typing in the new values in the formula are just not feasable. I
am sure I am just doing something stupid because I have never used this
program before, but cant get it to work.

If you put it into C1 say, then copy and paste into C2 and you should see it
update automatically. I do

 

[Greg]

Not sure, but see whether this helps a bit ..
(we're just concatenating the bits together)

Put in C1:
="<a href=http://"&A1&" target="&"_ "&" title="&B1&">"&A1&"</a><br>"
Copy down

Not quite.

I know exactly what original poster is trying to do. I do it all the
time. You need to use the char(34) function to generate the quotation
marks that are required in the URL.

This has been tested and should work fine:

="<a href="&CHAR(34)&"http://"&A1&CHAR(34)&"
target="&CHAR(34)&"_blank"&CHAR(34)&"
title="&CHAR(34)&B1&CHAR(34)&">"&A1&"</a><br>"

rdenny, you need to make sure that all of this goes in as one line in
cell C1.

Once you have C1 working properly, select the cell (not all the text
in the formula, but the entire cell itself) and copy. Then select
cells C2 through whatever the last line is, and paste. The references
to A1 and B1 will update automatically.

 

[Max]

Thanks for the follow through, Bob ! Between your response and that posted
by Greg in the other branch, believe the OP would have got it working by now
...

Fast! It took Max 1 and a half hours <vbg>

Ah, was having dinner and washing up over here
when the post came through <bg>