Take the 2-minute tour ×
Web Applications Stack Exchange is a question and answer site for power users of web applications. It's 100% free, no registration required.

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 ("", ""). 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?

share|improve this question
    
Can you give us an example of the output you try to paste? –  OnenOnlyWalter Nov 1 '12 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 '12 at 16:47
    
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 '13 at 1:46
add comment

4 Answers 4

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

share|improve this answer
    
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 '12 at 16:03
    
Ahh, got it - which program are you pasting into? –  OnenOnlyWalter Nov 2 '12 at 16:21
    
Notepad, Notepad++, Netbeans, anything. It happens no matter where I paste it. –  Ryan Nov 2 '12 at 20:03
add comment

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.

share|improve this answer
add comment
up vote 0 down vote accepted

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.

share|improve this answer
add comment

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:

share|improve this answer
add comment

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

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