IMPORTRANGE + SUBSTITUTE

1
votes

I have a spreadsheet "X", this spreadsheet, contains 2 sheets, "sheet1" and "sheet2"

On 'sheet2' i have this on the A1 cell (i import from a shared spreadsheet all data that is shared):

=QUERY(IMPORTRANGE("URL_SPREADSHEET";"'sheet_that_i_import'!A4:U");"SELECT Col1,Col4,Col3,Col11,Col16,Col14";)

on the column 'Col14', I have some records named 'COMERCIAL'

On the 'sheet1' i get some data(some columns, not all) from 'sheet2', but I need to SUBSTITUTE or REPLACE the string on 'Col14'

If the string is 'COMERCIAL', I need to replace/substitute with 'POLO'

I tried to use importrange+substitute but with no success, maybe the syntax.

1
google-sheetsgoogle-sheets-formulaarray-formulasgoogle-sheets-queryimportrange

1 Answers

1
votes

try:

=ARRAYFORMULA(SUBSTITUTE(QUERY(IMPORTRANGE("SPREADSHEET_ID"; "'sheet_name'!A4:U");
 "select Col1,Col4,Col3,Col11,Col16,Col14"; 0); "COMERCIAL"; "POLO"))