5

I've created an Excel file where a non-technical co-worker of mine organizes a bunch of data into rows and columns.

I then have formulas concatenate across those rows and columns and add some HTML tags (<td>, <tr>). Then I use the CLEAN() function to wrap the result of those concatenations. I can then copy and paste from that final cell into MailChimp to send out an HTML email newsletter.

I'd prefer to convert the Excel file into a Google Spreadsheet since it keeps a revision history and allows live collaboration.

However, since there is no CLEAN() function in Google Spreadsheets, if I copy from my Google Spreadsheet into HTML, there are lots of extra double-quotes that ruin everything.

Anyone know of a workaround?

Improve this question
4
  • Can you give us an example of the output you try to paste?
    – OnenOnlyWalter
    Nov 1, 2012 at 15:56
  • I don't want double-quotes added: " <table cellpadding=""0"" cellspacing=""0"" border=""0"" width=""645"" class=""couponBoundary""> <tr> <td width=""130"" class=""couponStoreLogoCell""> <div align=""center""> <img src=""/images/merchants/756_t.jpg""> </div> </td> </tr> </table>"
    – Ryan
    Nov 1, 2012 at 16:47
  • 2
    I had the same problem as you, but today when I copied the same stuff from the same source, the quotes are just gone. Tested with Notes and XCode (Mac softwares). Today no problem. Maybe Google fixed it... I didn't do any changes. I didn't even know about a CLEAN function.
    – Jonny
    Apr 18, 2013 at 1:46
  • In their later comment the OP mentions "There are no extra double-quotes in the actual contents; they just appear when I copy OUT from the spreadsheet into an HTML file." Those unwanted double quotes appear because Google Sheets automatically escapes text that contains newlines when copy-pasting. When you run the text through clean() or regexreplace(), those newlines get weeded out, and no escaping is necessary. The smartest solution would be to modify the formulas that produce the newlines in the first place. See What is the XY problem?
    – doubleunary
    Jan 21 at 5:37

4 Answers 4

Reset to default
3

As of 11-12-2013 the CLEAN() function is available in Google Spreadsheet:

I wasn't able to reproduce the results (now and back then, as did @Jonny on the 18th of April) but the CLEAN() function should remove the non-printable characters.

See following reference, on how to activate the new Google Spreadsheet:

Improve this answer
2

I should have thought of this sooner: my workaround is File > Publish to the Web. Now, whenever I edit the worksheets, the published webpage (viewable by my company's employees) automatically updates, and we can copy-and-paste from there.

Improve this answer
0

If the problem is the double-quotes, try wrapping your cell in this:

=SUBSTITUTE(A1,CONCAT(CHAR(34),CHAR(34)),CHAR(34))

A1: <table cellpadding=""0"" cellspacing=""0"" border=""0"" width=""645"" class=""couponBoundary""> <tr> <td width=""130"" class=""couponStoreLogoCell""> <div align=""center""> <img src=""/images/merchants/756_t.jpg""> </div> </td> </tr> </table>

B1: =SUBSTITUTE(A1,CONCAT(CHAR(34),CHAR(34)),CHAR(34))

Produces:

<table cellpadding="0" cellspacing="0" border="0" width="645" class="couponBoundary"> <tr> <td width="130" class="couponStoreLogoCell"> <div align="center"> <img src="/images/merchants/756_t.jpg"> </div> </td> </tr> </table>

Not the most elegant, but it gets the job done :)

Improve this answer
3
  • There are no extra double-quotes in the actual contents; they just appear when I copy OUT from the spreadsheet into an HTML file. In my case, the contents of A1 LOOKS fine (doesn't have double-double-quotes), but when I copy from it and then paste it into an HTML file, it adds unnecessary double-quotes. Thanks though.
    – Ryan
    Nov 2, 2012 at 16:03
  • Ahh, got it - which program are you pasting into?
    – OnenOnlyWalter
    Nov 2, 2012 at 16:21
  • Notepad, Notepad++, Netbeans, anything. It happens no matter where I paste it.
    – Ryan
    Nov 2, 2012 at 20:03
0

It's not a clean solution, but if you copy, then paste special >> as values somewhere else in your spreadsheet, THEN copy that cell, you will have a perfectly formatted HTML code.

Improve this answer

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.