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

ExcelTips247

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.    FILTER() to extract text and numbers from dataset,How do I extract numbers from text and numbers in Excel?,How do I separate data in Excel based on criteria?,How do I extract text from a filter in Excel?,  excel extract number from mixed text,excel nested filter function,excel filter function multiple criteria,excel filter function multiple values,excel extract number from text in cell,excel filter function multiple columns,how to filter cells containing specific text in excel,  excel extract number from mixed text,excel nested filter function,excel filter function multiple criteria,excel filter function multiple values,excel extract number from text in cell,excel filter function multiple columns,

Transcript

00:00If you have a data set and it's a mixture of both text and numeric value, and if you have to split text and numeric value into two separate columns, A and B, this is how you do it. Let's start with numeric. This is the formula to split text and numeric from this data set.

00:15Essentially, I'm using a filter function to do it. The first condition outlines the data set that I want to work on. The second one is your conditional for that filter. So basically, I'm checking if the data set is number. And this double minus minus means is a double unary operator, basically trying to convert this into numeric value and checking to see if that particular cell value is a number. If it's true, then I'll include that in the list.

00:45And this is what the list looks like. Now for the text, this is the formula. Okay, let's look at the text conversion formula here. Again, using the filter function. I'm using or defining the data set that I'm looking at. And the second condition, or the second argument is your condition itself. Let's look at it further here. So basically, we're looking and seeing if that particular cell is a text. If it's a text, like for example, battery, it will return a true value.

01:13And I'm taking that true value and multiplying with this is error function here. And inside, if you look at it, again, it's a double unary operator here, basically trying to convert this, say a particular cell into a number.

01:28And if you try to convert a string into a number, you know it's going to cause an error. So this is error will return to. So for example, if you take battery into consideration, the is text is true.

01:39And this trying to convert into a number will produce an error is true. That's why this is true. And then it will be included as part of your text.

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

Category

Transcript

Comments

Recommended