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

  • Thread starter Thread starter StargateFan
  • Start date Start date
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.
 
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
 
Back
Top