In Google Sheets, the Query function is widely used in different data manipulation techniques. Here let me introduce you, I think it’s in its first kind, how to filter rows if all the columns have text content. I mean I only want to Filter a row if all the cells in that row contain text. If any cell has a date or numeric content in that row or even blank, I want to exclude such rows from the filtered output.
In this Spreadsheet example, I only want the row 3 and 5, which I’ve highlighted, in the filtered output. Because as you can see, other rows have a mixed type of data like text and numbers and even blank cells.
In order to filter rows if all the Columns have text content in Google Sheets, as I’ve mentioned I am going to use the Query function. Here we are going to filter rows that only having text in all the columns.
But in this case, the Query is not enough. We should make use of the ISTEXT function with a logical test. Here we go.
How to Filter Rows If All the Columns Have Text Content
Unlike my earlier Google Sheets tutorials, here I am following a different approach. If you are new to here, I’ll tell you what procedure I was following in the past. Earlier it was like first introducing you the main formula and then explaining how to develop that formula. Here it’s on the opposite. Here I start forming the master formula from the beginning.
Step 1:
You can test whether a cell content is a Text or not by using the ISTEXT function as below.
=istext(A2)
This formula will return TRUE if the cell content is text else it will return FALSE. Now how to use ISTEXT in an Array or multiple column range?
=ArrayFormula(ISTEXT(A2:D6))
In my example, I’ve four columns. So in a four column range, I can use the ISTEXT formula as above. This formula will only return TRUE or FALSE.
Step 2:
Now I am using the IF logical function with the above ISTEXT array formula to return text values wherever the ISTEXT formula returns TRUE.
Similarly, the same formula will return blank wherever the ISTEXT formula returns FALSE.
=ArrayFormula(if(istext(A2:D6),A2:D6,))
Here I think I should show you the output. So I’ve captured a screenshot.
Step 3:
This is our final step to filter rows if all the Columns have text content in Google Sheets.
You can use a Query formula on the above result. In plain English, the filter conditions in Query are like this.
Filter columns A, B, C, and D if the cell contents in that rows are not blank. See the formula.
=query(ArrayFormula(if(istext(A2:D),A2:D,)),"Select * where Col1<>'' and Col2<>''and Col3<>''and Col4<>''")
And the result:
I’ve applied the above formula in Cell F2 and it filtered the rows that only having text values.
Additional Tips:
You have already learned how to filter rows if all the columns have text content. So similarly you can filter rows that only having number values. Here there are minor changes to the above formula.
=query(ArrayFormula(if(isnumber(A2:D),A2:D,0)),"Select * where Col1>0 and Col2>0 and Col3>0 and Col4>0")
What are those changes?
Here the ISTEXT formula is replaced by the ISNUMBER formula. Also in Query, the logical clause has changed from<> to>0.
Then there is one more minor change but which is very important. Earlier I’ve left the FALSE part in the IF logical test as blank. Now I’ve put it as 0. It’s important in Query to filter.
Conclusion:
Hope you could learn how to filter rows having text / numbers only in Google Sheets. I have made the use of Google Sheets info functions like ISTEXT and ISNUMBER for this purpose. There may be alternative solutions. But it works. Enjoy!
