You can use the FILTER function combined with the LEN function to filter a range of cells in Excel based on the number of characters in a specific cell:
=FILTER(A2:C11, LEN(A2:A11)<5)
This particular formula filters the cells in the range A2:C11 to only return the rows where cells in the range A2:A11 have less than 5 characters.
The following example shows how to use this formula in practice.
Example: Filter by Number of Characters in Excel
First, let’s enter the following dataset in Excel that contains information about various basketball players:
Suppose we would like to filter the dataset to only show the rows where the team name contains less than 5 characters.
We can type the following formula into cell E2 to do so:
=FILTER(A2:C11, LEN(A2:A11)<5)
The following screenshot shows how to use this formula in practice:
The formula returns only the rows where the number of characters in the team column is less than 5.
Note that you can also use multiple conditions within the FILTER function.
For example, you could type the following formula into cell E2 to only show the rows where the number of characters in the team column is between 5 and 7:
=FILTER(A2:C11,(LEN(A2:A11)>=5)*(LEN(A2:A11)<=7))
The following screenshot shows how to use this formula in practice:
The formula returns only the rows where the number of characters in the team column is between 5 and 7.
Additional Resources
The following tutorials explain how to perform other common operations in Excel:
Excel: How to Use Wildcard in FILTER Function
Excel: How to Filter Cells that Contain Multiple Words
Excel: How to Count Filtered Rows
