How to join columns in single text with quotation marks, commas and parentheses?

1
votes

I have the following columns:

i   ì
o   ò
e   ě
u   ů
A   Â
z   ź
O   Ō
o   õ
A   À

And I would like, through formulas, to create a text only with SUBSTITUTE( multiplied by the number of rows in COLUMN B, would look like this:

SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(

And I would also like to merge COLUMN A with COLUMN B and look like this:

,"ì","i"),"ò","o"),"ě","e"),"ů","u"),"Â","A"),"ź","z"),"Ō","O"),"õ","o"),"À","A")

And as he added more rows in the COLUMNS A and B, it would be completed automatically. How could I proceed to resolve each of these issues?

Link to Spreadsheet: https://docs.google.com/spreadsheets/d/1JDOKIO2JncQRLdWR1vWfxcXR5z7IBOUJflNgbdMre7M/edit?usp=sharing

1
google-sheetsconcatenationgoogle-sheets-formulaarray-formulasgoogle-sheets-query

1 Answers

1
votes

try:

="="&REPT("SUBSTITUTE("; COUNTA(A:A))

and:

=ARRAYFORMULA(TRIM(QUERY(IF(A:A<>""; 
 ","&CHAR(34)&A:A&CHAR(34)&","&CHAR(34)&B:B&CHAR(34)&")"; );;999^99)))