1

I have a query in a spreadsheet. What I need to do is output a bit of simple HTML text between each column being pulled out of another tab. Sample code looks like this:

=Query(Official!A7:P617, "SELECT '<tr>', A, '</td><td>', B, '</td><td>', C, '</td></tr>' WHERE A > 0 ORDER BY B ASC")

The problem is that sheets don't seem to like either the / character. How can I escape these values so I can get the desired output I want?

And because people will ask...

I'm doing this because I use this data to share information on a forum. Their WYSIWYG editor doesn't like copy-and-pasted data straight from Sheets. I've tried copy-and-pasting the data into a text document and cleaning it up, and it still doesn't work properly. According to others who create tabled information in Sheets and share the info on the same forum, this method is the best approach-- wrap the details and each field in table tags, and then paste it as a source, rather than into the WYSIWYG editor.

Yes, it's ugly but it does work. I now just need my query to play by the rules I've been given.

1

Your issue is not \ character but the fact that you try to repeat exactly the same column multiple times '</td><td>' which QUERY does not support. Therefore try:

=REGEXREPLACE(QUERY(Official!A7:P617, 
 "select '<tr>', A, '♦</td><td>', B, '♣</td><td>', C, '♠</td></tr>' 
  where A > 0 
  order by B asc"), "♦|♣|♠", "")
  • 1
    Thanks, adding arbitrary spaces fixed it too, and that doesn't require the REGEXREPLACE call. I had no clue the data couldn't be the same. What a weird issue, IMHO. – RLH Apr 3 at 14:47

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

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