Google Sheets QUERY + SUBSTITUTE to convert dot to coma

user_13278849 0
Viewed 59

Here is my Google Sheets :

My datas

I would like to import "QUERY" column M,N,O,P,Q,R into another tab (same sheet) but convert dot "." to coma "," from column Q and R.

I don't know if SUBSTITUTE is the best way and also don't know how to use this inside a QUERY formula.

This formula don't work : =QUERY({Hoopzi_FlatFileAllOrdersReportbyLastUpdate!M2:Q;SUBSTITUTE(Hoopzi_FlatFileAllOrdersReportbyLastUpdate!Q2:R;".";","))}; "SELECT *")

Thanks for your help :)

google-query-language google-sheets number-formatting substitution arrays
2 Answers

Try

=query({Hoopzi_FlatFileAllOrdersReportbyLastUpdate!M2:P\arrayformula(1*substitute(Hoopzi_FlatFileAllOrdersReportbyLastUpdate!Q2:R;".";","))};"select *")
user_150193800

use:

=INDEX({Hoopzi_FlatFileAllOrdersReportbyLastUpdate!M2:P\
 SUBSTITUTE(Hoopzi_FlatFileAllOrdersReportbyLastUpdate!Q2:R; "."; ",")*1})
user_56326290
Related