Fill Cell that includes text in quotes plus data from other cells

[pigsfoot]

Apologies for the title, i couldn't find an easy way to explain what i an trying to do.

I have to create a CSV file to import address data into a CRM. The CSV expects the data to be input as follows

{"city": "", "state": "", "address": "East Suffolk House\r\nStation Road\r\nMelton\r\nWoodbridge", "country": "GB", "postcode": "IP12 1RT"}

The data i need build that line with is in cells as per the image below. Cell H1 would be where it says East Suffolk above and so on through to Postcode which is column L

The problem i'm having is because the CSV code includes quote's i can't seem to create a formula that includes them as text because the " means something within the formula itself.. .if that makes sense.

Is there a way around this ? Thanks

 

[myall_blues]

If I understand your requirement correctly then to get the quotes in you need to 'quote the quotes' as it were.

So for example to get the value of H1 into the address with quotes you would do =""" & H1 & """.

 

[Alex Blakenburg]

Murray is one quotation mark short, it would need to be 4 quotes:

="""" &H1&""""

Something like this might be cleaner.

=LET(x,CHAR(34),x & H1 & x)

It would help if you also showed us your current formula. If some of the values are hardcoded then using Substitute might reduce the length of the formula ie:

=LET(x,CHAR(34),
str,"|city|: ||, |state|: ||, |address|:" & x & A1 & x,
SUBSTITUTE(str,"|",CHAR(34)))