FILTER() to extract text and numbers from dataset - Excel Tips and Tricks

Discover use FILTER() to extract text and numbers frn dataset. Essentially, how do I extract numbers from text and numbers in Excel? Or how do I separate data in Excel based on criteria? And how do I extract text from a filter in Excel?

Extract numeric values from dataset
=FILTER(D2:D18,ISNUMBER(--D2:D18))

The "--" operator is known as the double unary operator. It is used to convert the values in the range D2:D18 to numbers, which can then be evaluated by the ISNUMBER function.


Extract text from dataset
=FILTER(D2:D18,ISTEXT(D2:D18)*ISERR(--D2:D18))

Lets look at the second argument of the FILTER() function.
The formula ISTEXT(D2:D18)*ISERR(--D2:D18) is an array formula that checks a range of cells D2:D18 for cells that contain text values and are not numbers.

The ISTEXT function checks whether each cell in the range contains text and returns an array of TRUE or FALSE values.
The ISERR function checks whether each cell in the range, converted to a number by the double unary operator (--), results in an error value (such as #VALUE!, #REF!, etc.), and returns an array of TRUE or FALSE values.
The multiplication operator (*) performs an element-wise multiplication of the two arrays of TRUE or FALSE values, resulting in an array of TRUE or FALSE values. The resulting array will contain TRUE values only for cells that meet both of the conditions.

The cell contains text (i.e., ISTEXT returns TRUE)
The cell is not a numeric value (i.e., ISERR returns TRUE)
In other words, the formula is checking for cells in the range D2:D18 that are text values and not numbers, and returns an array of TRUE or FALSE values corresponding to each cell in the range. This type of formula can be used to filter or count cells that meet specific criteria.

#shorts #short #shortvideo #fyp #excel #microsoft #tiktok #fypシ