Google Spreadsheets equivalent of Excel CLEAN() formula

Question

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?

Answer 1

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

• https://support.google.com/docs/answer/3267340

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:

• https://support.google.com/drive/answer/3541068

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.

Answer 3

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 :)

Answer 4

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.