Concatenating cells with ones that contain html tags and quotation marks?

S

StargateFan

I have 2 columns of text that I need to integrate with html code so
that I can just copy the concatenated text to plug into an html
database of information that will get uploaded to the net. Is there a
way to do this?

Since the concatenating code is sometimes like this, it seems (as per,
http://www.officearticles.com/excel/concatenate_in_microsoft_excel.htm):

=A2&" "&B2

where extra "text" (in the form of a blank space here between
quotation marks) is added with the ampersands.



How could we do this type of thing, where my exact text to add I'm
showing below in parantheses?:

= (<body><p>) & G2 & (</p><p> </p><p><a href=") & I2 & ("
target="_blank"><img alt="http://...imageURl.jpg" class="jive-image"
src="http://...imageURl.jpg" /></a></p></body>)

There are a lot of quotation marks in the HTML text to add which seems
to interfere with the quotation marks that are required to encase
additional text.

How can we handle all these html quotation marks and broken brackets
and equal signs when concatenating?

Thanks.
 
P

Pete_UK

To concatenate literal strings you need to enclose them within double
quotes. It can become complicated when the literal string itself
contains quotes, so I usually make use of CHAR(34) which is the same
as ". So, change all your literal quotes (i.e. those that are needed
within the HTML codes) to CHAR(34), like this in your example:

= "<body><p>" & G2 & "</p><p> </p><p><a href=" & CHAR(34)
& I2 & CHAR(34) & "target=" & CHAR(34) & "_blank" & CHAR(34)
& "><img alt=" & CHAR(34) & "http://...imageURl.jpg" & CHAR(34)
& " class=" & CHAR(34) & "jive-image" & CHAR(34) & "src=" &
CHAR(34) & "http://...imageURl.jpg" & CHAR(34) & " /></a></p></body>"

I've manually split the lines so that we don't get line breaks at
unfortunate points when they appear in your newsgroup reader, but this
is all one formula.

Of course, you could put some of these strings in other cells, so if
you had these strings in the cells quoted:

M2: <body><p>
N2: </p><p> </p><p><a href="
O2: "target=" _blank"><img alt="
P2: http://...imageURl.jpg"
Q2: class="jive-image"src=" NOTE: leading space
R2: /></a></p></body>

then your formula would be:

=M$2 & G2 & N$2 & I2 & O$2 & P$2 & Q$2 & P$2 & R$2

This could be copied down if only G2 and I2 were to change in a series
of similar statements.

Hope this helps.

Pete
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

excel adds quotation marks when pasting text 6
How to copy only the numbers that are between quotation marks in new cells 1
problem with automatic quotation marks 1
Concatenation with Quotation Marks? 1
Removing Quotation Marks in Field Display Text 1
Word macro that can call cells out of Excel for a find and replace 0
Quotation Marks and automatic application thereof 0
Outlook 2003 - pasting html into appointment shows tags 0

Top