Filter Rows with Text in All Columns in Google Sheets

In many scenarios, you may want to filter rows in a range where all columns contain text in Google Sheets. For example, in a customer feedback table, you might expect all the columns to be filled with relevant information. Leaving empty cells in any column or inserting random numbers may not be sufficient to qualify the feedback.

To filter rows where all columns have text, we will use a combination of functions, with FILTER playing a key role.

Generic Formula

Here is the formula to filter rows where all columns contain text content in Google Sheets:

=FILTER(range, MMULT(--ISTEXT(range), SEQUENCE(COLUMNS(range), 1, 1, 0)) = COLUMNS(range))

In this generic formula, replace range with the specific range you want to filter.

Example: Filter Rows Where All Columns Contain User Feedback in Text

In the following example, a hotel feedback table collects user experiences. Some users may fail to enter names and may instead input random numbers to hide their identities. Additionally, they might skip providing important feedback.

Sample Dataset for Filtering Rows with Text in All Columns in Google Sheets

We want to filter the rows where all columns are filled with text content.

The range in our example is A1:G. Here’s the formula to filter rows in the range A1:G where all columns contain text content:

=FILTER(A1:G, MMULT(--ISTEXT(A1:G), SEQUENCE(COLUMNS(A1:G), 1, 1, 0)) = COLUMNS(A1:G))
Filter Formula to Extract Rows Where All Columns Contain Text-Based User Feedback

Formula Breakdown

To understand this formula, it’s important to break down and explain the condition that filters the rows.

The formula syntax is:

FILTER(range, condition1, [condition2, ...])

Where:

  • range = A1:G (the range to filter)
  • condition1 = MMULT(--ISTEXT(A1:G), SEQUENCE(COLUMNS(A1:G), 1, 1, 0)) = COLUMNS(A1:G)

Let me explain the condition1 in detail so you can understand how the formula filters rows where all columns contain text.

  1. --ISTEXT(A1:G): This part converts the result of the ISTEXT function (which checks if a cell contains text) into a numeric array. It returns 1 for text values and 0 for non-text (or empty) cells. The -- (double negation) converts the TRUE/FALSE result into 1/0.
    Using ISTEXT to Check for Text Values in a Range
  2. SEQUENCE(COLUMNS(A1:G), 1, 1, 0): This generates a vertical array of numbers starting from 1, with a length matching the number of columns in the specified range (in this case, A1:G). The SEQUENCE function creates a sequential array that helps in multiplying with the results from ISTEXT to check each column.
    {1; 1; 1; 1; 1; 1; 1}
  3. MMULT(--ISTEXT(A1:G), SEQUENCE(COLUMNS(A1:G), 1, 1, 0)): The MMULT function performs matrix multiplication between the array of text (converted to 1/0) and the sequence of numbers. This gives the sum of the 1s (representing text values) across each row. If all columns in a row contain text, the sum will equal the total number of columns in that row.
  4. = COLUMNS(A1:G): This checks if the sum from the MMULT function matches the total number of columns in the range (i.e. if the sum of 1s equals the total number of columns). If it does, it returns TRUE, meaning all columns have text content. If not, it returns FALSE.
  5. FILTER(range, condition): The FILTER function returns the rows where the condition evaluates to TRUE, i.e., where all columns contain text.

The formula will return all rows where every column contains text content. Rows with empty or non-text values in any of their columns will be excluded.

Resources

Prashanth KV
Prashanth KV
Your Trusted Google Sheets and Excel Guide Prashanth KV brings a wealth of experience in Google Sheets and Excel, cultivated through years of work with multinational corporations in Mumbai and Dubai. As a recognized Google Product Expert in Docs Editors, Prashanth shares his expertise through insightful blogging since 2012. Explore his blog for practical tips and guidance on maximizing your spreadsheet skills.

Lookup Values Under Categories in Excel and Google Sheets

We can use a combination of XLOOKUP and VLOOKUP in both Excel and Google...

Extract Items Under a Category from the Same Column in Excel

In Excel, you can use a combination of the XLOOKUP and DROP functions to...

How to Create a Searchable Table in Excel Using the FILTER Function

Finding specific records, or rows containing the required information, is straightforward in Excel using...

Time Sequences in Excel by Minute, Hour, or Second Increments

Creating time sequences, whether by hour, minute, or second increments in Excel, can be...

More like this

XMATCH Row by Row: Finding Values Across a Range in Google Sheets

Using the BYROW function with XMATCH in Google Sheets allows us to match values...

Limit Formula Expansion to a Specific Row in Google Sheets

In this tutorial, I’ll explain how to limit the expansion of an array formula...

3-D Referencing Structured Data Tables in Google Sheets

When you have several tables within a single sheet—not across multiple sheets in a...

LEAVE A REPLY

Please enter your comment!
Please enter your name here

This site uses Akismet to reduce spam. Learn how your comment data is processed.