I will tell you how useful is To_text function in Google Sheets. Once I landed in a problem in importing data from one of my Google Sheets. That time I realized how useful the To_text function is. What was that problem?

I wanted to import one column from one of my sheets. But the column had dates entered in text format, date format, time values, text string and lots of blank rows in between. I want to import only the rows that contain values.

The solution was using the To_text function with Query and Importrange. Let me begin with the basic tips on how to use To_text function in Google Sheets.

Usage of To_text Function in Google Sheets

In Excel or Google Sheets, you can put an apostrophe (‘) in front of a number or date or any value to make it text without losing its format. The To_text function does the same.

The syntax of Google Sheets To_text Function:

TO_TEXT(value)

Example:

See how the To_text formulas in C13, C14…C17 converted to date, currency, time and numbers to text while retaining the format.

You can use Google Sheets To_text function in Array Formula too. In such cases try the formula similar to the one below. Why should one go with numerous formulas when To_text supports array?

=ArrayFormula(to_text(A13:A17))

Use of Google Sheets To_text Function in Query

You must learn the use of To_text in Query as it may be very useful while consolidating rows or importing rows.

The use of Regex in Query Match clause is the best way to filter specific values in a column. But many users are not well conversed in the use of Regex in Query Match Clause.

Similar: Google Sheets Query to Filter Numbers Only From Mixed Data Column

See one example where I’m going to use the To_text function in Query.

In column A I’ve mixed type of data. So when I use Query, it may put me in a puzzle! It’s like what to put in the “Where” clause to filter only the rows that contain values.

So what I’ve done is, first converted values in column A to text and then used the<>'' operator in the “Where” clause which is “not equal to space” that applicable in filtering text values.

But don’t forget to use the Array Formula while using the To_text function in an Array.

Similarly, You can use the To_Text function in Importrange to import a column with mixed content data.

Use of To_text in Importrange in Google Sheets

Here just refer to the above formula. You only need to change the range A1: A reference within the Importrange formula.

=ArrayFormula(query(to_text(IMPORTRANGE("Blah.. Blah..Blah..","Sheet1!A1:A")),"Select Col1 where Col1<>''"))

Conclusion

In situations like the above, you will realize that all the functions in Google Sheets are there for some purpose. Hope you’ve enjoyed the stay!