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.

What is the purpose of the query if it selects all (`"Select *"`)? — Max Makhrov, Aug 27 '18 at 14:57

score 4 · Accepted Answer · answered Sep 10 '18 at 10:52

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.

score 1 · Answer 2 · answered Aug 28 '18 at 08:00

My suggestion is to use 2 formulas and more space in your sheets.

Formula #1: get the data and replace commas:

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

Formula #2: to convert text into numbers:

=arrayformula (range_of_text_to_convert * 1)

Notes:

using 2 formulas will need extra space, but will speed up formulas (importrange is slow)
the second formula uses math operation (*1) which is the same as value formula.

Working perfectly. Thanks – Khurram Ishaque Feb 16 '19 at 08:08 — Khurram Ishaque, Feb 16 '19 at 08:08

score 0 · Answer 3 · answered Aug 27 '18 at 13:43

0

Try this. I treats the columns separately.

=arrayformula(QUERY({Sheet1!A2:A5,SUBSTITUTE(Sheet1!B2:B5,",","")},"Select *"))

answered Aug 27 '18 at 13:43

Ed Nelson

10,015
2
27
29

score 0 · Answer 4 · edited May 07 '20 at 13:57

Thanks to Ed Nelson, I was able to figure out this:

=arrayformula(QUERY({'Accepted Connections'!A:R,SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE('Accepted Connections'!A:R,"AIF®",""),"APA",""),"APMA®",""),"ASA",""),"C(k)P®",""),"C(k)PS",""),"CAIA",""),"CBA",""),"CBI",""),"CCIM",""),"","")},"Select *"))

That removed all the text I didn't need in specific columns.

How to use substitute function with query function in google spreadsheet

4 Answers4