Excel: Unique with Filter formula for Blank and " " cells still returns an empty value from list

Excel: Unique with Filter formula for Blank and " " cells still returns an empty value from list

If you are an Excel user, you may have come across a situation where you need to extract unique values from a list. Excel provides a handy formula called UNIQUE, which can help you achieve this. However, there is a common issue that users face when using the UNIQUE formula with a filter to exclude blank cells or cells containing only spaces. Even after applying the filter, the formula still returns an empty value. In this article, we will explore this problem and provide a solution to overcome it.

The UNIQUE formula

The UNIQUE formula in Excel allows you to extract unique values from a range or an array. It returns a dynamic array of unique values, which can be very useful for data analysis, reporting, or any other task that requires working with unique values.

To use the UNIQUE formula, you simply need to enter it in a cell and provide the range or array as an argument. For example, if you have a list of values in cells A1 to A10, you can enter the formula =UNIQUE(A1:A10) in a different cell to get the unique values.

The issue with blank and " " cells

Now, let's say you have a list of values where some cells are blank or contain only spaces. You want to extract the unique values from this list but exclude the blank and " " cells. To achieve this, you may try using the UNIQUE formula along with the FILTER formula.

The FILTER formula allows you to apply a filter to a range or an array based on certain conditions. In this case, you want to filter out the blank and " " cells. You can use the following formula: =UNIQUE(FILTER(A1:A10, A1:A10<>"", A1:A10<>" ")).

However, even after applying this formula, you may notice that it still returns an empty value. This can be quite frustrating, especially if you are expecting a list of unique values.

The reason behind the issue

The reason behind this issue is that Excel treats blank cells and cells containing only spaces as text values. When you use the FILTER formula to exclude these cells, it actually checks for non-blank text values. Since Excel considers the " " (space) as a non-blank text value, it fails to exclude the cells containing only spaces. As a result, the UNIQUE formula still considers these cells as unique values and includes them in the output.

A workaround solution

To overcome this issue, you can use a combination of the TRIM and SUBSTITUTE formulas along with the UNIQUE and FILTER formulas. The TRIM formula removes leading and trailing spaces from a text string, while the SUBSTITUTE formula replaces a specific text within a string.

Here's the modified formula: =UNIQUE(FILTER(A1:A10, TRIM(SUBSTITUTE(A1:A10, " ", ""))<>"")). Let's break it down:

• The SUBSTITUTE formula replaces all spaces with an empty string, effectively removing the spaces from the cells.
• The TRIM formula then removes any leading or trailing spaces from the resulting text.
• The FILTER formula filters out the non-blank values after removing the spaces.
• Finally, the UNIQUE formula extracts the unique values from the filtered list.

By using this modified formula, you should be able to extract the desired unique values from your list, excluding the blank cells and cells containing only spaces.

Conclusion

The UNIQUE formula in Excel is a powerful tool for extracting unique values from a list. However, when you need to exclude blank cells or cells containing only spaces, it may not work as expected. By using a combination of the TRIM and SUBSTITUTE formulas along with the UNIQUE and FILTER formulas, you can overcome this issue and obtain the desired results. Remember to modify the formula according to your specific range or array.