Trying to simplify Formula

Copper Contributor

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

 

3 Replies

@McilwaineSam 

This might work for you if you have 365:

=LET(quotes,CHAR(34)&CHAR(34),joined,REDUCE("",A2:Q2,LAMBDA(a,v,a&","&quotes&v&quotes)),REPLACE(joined,1,1,""))

 

If you have 365 but no access to REDUCE, you could also simplify the formula with LET.

@McilwaineSam 

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)

 

 

@McilwaineSam 

Another 365 option:

 

=TEXTJOIN(",",,BYCOL(A2:Q2,LAMBDA(col,""""""&col&"""""")))