Oct 24 2022 09:54 AM
Hi,
I am using the 'CONCATENATE' formula to pull data from different cells into one.
I also need to include Double Double quotations around each field and include a Comma between each piece of data from each cell.
Currently this is my formula:
=CONCATENATE(CHAR(34),A2,",",CHAR(34),CHAR(34),B2,CHAR(34),CHAR(34),",",CHAR(34),CHAR(34),C2,CHAR(34),CHAR(34),",",CHAR(34),CHAR(34),D2,CHAR(34),CHAR(34),",",CHAR(34),CHAR(34),E2,CHAR(34),CHAR(34),",",CHAR(34),CHAR(34),F2,CHAR(34),CHAR(34),",",CHAR(34),CHAR(34),G2,CHAR(34),CHAR(34),",",CHAR(34),CHAR(34),H2,CHAR(34),CHAR(34),",",CHAR(34),CHAR(34),I2,CHAR(34),CHAR(34),",",CHAR(34),CHAR(34),J2,CHAR(34),CHAR(34),",",CHAR(34),CHAR(34),K2,CHAR(34),CHAR(34),",",CHAR(34),CHAR(34),L2,CHAR(34),CHAR(34),",",CHAR(34),CHAR(34),M2,CHAR(34),CHAR(34),",",CHAR(34),CHAR(34),N2,CHAR(34),CHAR(34),",",CHAR(34),CHAR(34),O2,CHAR(34),CHAR(34),",",CHAR(34),CHAR(34),P2,CHAR(34),CHAR(34),",",CHAR(34),CHAR(34),Q2,CHAR(34),CHAR(34),",",CHAR(34))
As you can see this is a huge formula which works but looks very cumbersome.
Any ideas of a formula that I could use to simplify this?
Regards,
Sam
Oct 24 2022 11:11 AM
This might work for you if you have 365:
=LET(quotes,CHAR(34)&CHAR(34),joined,REDUCE("",A2:Q2,LAMBDA(a,v,a&",""es&v"es)),REPLACE(joined,1,1,""))
If you have 365 but no access to REDUCE, you could also simplify the formula with LET.
Oct 24 2022 11:17 AM - edited Oct 24 2022 12:39 PM
The result of your formula is:
"A,""B"",""C"",""D"",""E"",""F"",""G"",""H"",""I"",""J"",""K"",""L"",""M"",""N"",""O"",""P"",""Q"","
But from your description it would be:
"A","B","C","D","E","F","G","H","I","J","K","L","M","N","O","P","Q"
I have listed two formulas for the second case.
If you use Excel for Microsoft 365 or Excel for the Web you can use this Formula.
=SUBSTITUTE(SUBSTITUTE(ARRAYTOTEXT(A2:Q2,1),"{",""),"}","")
Or from Excel 2019
=CHAR(34)&TEXTJOIN(CHAR(34)&","&CHAR(34),TRUE,A2:Q2)&CHAR(34)
And with double double quotes:
=CHAR(34)&CHAR(34)&TEXTJOIN(CHAR(34)&CHAR(34)&","&CHAR(34)&CHAR(34),TRUE,A2:Q2)&CHAR(34)&CHAR(34)
Oct 24 2022 11:43 AM