How to use substitute function with query function in google spreadsheet

Question

I am trying to use substitute function inside a query function but not able to find the correct syntax to do that. My use case is as follows.

I have two columns Name and Salary. Values in these columns have comas ',' in them. I want to import these two columns to a new spreadsheet but replace comas in "Salary" column with empty string and retain comas in "Name" column. I also want to apply value function to "Salary" column after removing comas to do number formatting.

I tried with the following code but it is replacing comas in both the columns. I want a code which can apply the substitute function only to a subset of columns.

Code:

=arrayformula(SUBSTITUTE(QUERY(IMPORTRANGE(Address,"Sheet1!A2:B5"),"Select *"),",",""))

Result:

Converted v/s Expected Result

Note :

I have almost 10 columns to import and comas should be removed from 3 of them.

Answer 1

Based on your suggestions, I was able to achieve the objective by treating columns separately. Below is the code.

=QUERY({IMPORTRANGE(Address,"Sheet1!A3:A5"),arrayformula(VALUE(SUBSTITUTE(IMPORTRANGE(Address,"Sheet1!B3:B5"),",","")))},"Select * where Col2 is not null")

Basically, two IMPORTRANGE functions side by side for each column.

The same query on the actual data with 10 columns will look like this.

=QUERY({IMPORTRANGE(Address,"Sheet1!A3:C"),arrayformula(VALUE(SUBSTITUTE(IMPORTRANGE(Address,"Sheet1!D3:H"),",",""))),IMPORTRANGE(Address,"Sheet1!I3:J")},"Select * where Col2 is not null")

I used 3 IMPORTRANGE functions so that I can format the columns D to H by removing comas and changing them to number.