0

I have an Excel spreadsheet that contains various rows of data (some which contain carriage returns) and a cell at the end of each row that uses a formula to generate a JSON string. However, a problem has arisen where, if a row contains a cell that contains a carriage return, it will add double quotes, as well as quoting the entire row when I paste it into my text editor.

I’ve come to learn this is Excel’s default behaviour. How can I disable it or work around?

And don’t tell me to find a different solution. I would if I had the option but I’m working in a corporate environment, where I’ve been given the spreadsheet to work with.

  • Do you have other double quotes in your JSON string you need to keep? If not you could run a find and replace once you paste into your text editor. – Andi Mohr Oct 9 '13 at 16:31
  • @AndiMohr Yes. The string delimiters for each and every string. – Martin Bean Oct 9 '13 at 16:40
  • If my answer below is no good you could try this - add a marker string at the front of each line so you get something like this "ZZZZZZZZZZZ your string here". Then you can do a find and replace on "/n"ZZZZZZZZZZZ if you follow me! – Andi Mohr Oct 9 '13 at 16:50
0

This is actually not too tricky if you're happy to lose the carriage return. Simply wrap your line aggregation formula in this:

=SUBSTITUTE(C2,CHAR(10)," ")

where C2 is where you've placed your string aggregation formula - you could of course enter your entire string formula here instead.

This simply replaces the carriage return CHAR(10) with a space. When you copy into a text editor the offending double quotes no longer appear.

| improve this answer | |

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.