Excel experts if you please

[markmarkymark]

Yo all

I wonder if any of you out there are able to manipulate text in excel? I have three columns.

In the first Column I have some terms such as:

Time
Thermal Neutrons
etc

In the second column I have the following terms which have been derived from the first column (not by excel but by something else). There is therefore no manipulation done in excel - it has just been pasted in:

/glossary/Time.html
/glossary/Thermal_Neutrons.html
/glossary/etc.html

In the the third column I want the following lines to be derived from the previous two columns:

<a href="/glossary/Time.html" target="_top" >Time</a>
<a href="/glossary/Thermal_Neutrons.html" target="_top" >Thermal Neutrons</a>
<a href="/glossary/etc.html" target="_top">etc</a>

What I want is the third column to be something like:

<a href="Second Column" target="_top">First Column</a>

I am just trying to save some time by having all the terms in the first two columns (I have 500!) joined into the standard format in the final column. I then have an easy cut and paste job for future planned work.

Anyone know how to do this - I'm sure it can not be that difficult?!!

many thanks

Mark

[Raistlin]

This should do the trick.....
__________________

Ok, try again.....

Ok, it won't let me upload an excel file.

try this formula in column three.

Assuming that A1 is "Time" and B1 is the URL etc


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

[bmxbandit]

do you just want the html code, or it to actually be a link?

EDIT: ah, been sorted already

[markmarkymark]

Hey thanks Raistlin!!

This works a treat. This is what makes this board so amazing - people prepared to help out like that. Very many thank!!

Mark

[MetaWraith]

you need to put a formula into the third column
which can then be dragged to replicate it throughout the column

put the cursor in a 3rd column cell and click on the "insert function" icon
select the "text" category of functions
then select the "concatenate" function

build up the formula as required, an example can be found here

http://www.techonthenet.com/excel/formulas/concat.htm

you should now realise that a suitable function could be derived to generate column 3 from columm 1 alone

all that remains now is to drag the formula throught the rest of the column (click bottom right corner of the cell)

Edit
took too long to type that and was beaten by Raistlin

[bmxbandit]

concatenate() went out of fashion a long time ago

[Raistlin]

Actually, the ampersand "&" in my equation is simply another form of concatenation.

And, you're welcome

[MetaWraith]

Originally Posted by bmxbandit

concatenate() went out of fashion a long time ago

I know that but it's still an easy way to do things and the wizard allows you to click on the cells you want included.