Search code examples
google-sheetsgoogle-query-language

String manipulation on column in Google Sheet Query

Is it possible to do a string manipulation of data in a Google Sheet query?

I want to do something like the following:

=QUERY(someRange!A:Z, SELECT A, SUBSTITUTE(B, "OCH", ""))

The idea being that all rows' column B is 'OCHXXXXXXX' where X is a digit. I would like to get rid of the chars.

On a side note... Is this possible in MySQL? How?

Solution

  • This formula might work for you:

    =QUERY({Sheet1!A:A,ARRAYFORMULA(SUBSTITUTE(Sheet1!B:B,"OCH","")),Sheet1!C:Z})