Card Tricks

Posts Tagged ‘array’

Excel Magic Trick 489: Extract Only Numbers From Text String Array Formula

January 31st, 2010

See an amazing Array Formula from Domenic and Ron Coderre at the Mr Excel Message Board that extracts only the numbers from a text string. See the functions LEN, INDIRECT, ROW, MID, ISNUMBER, INDEX, LARGE, SUMPRODUCT, and SUM.

Tags: array, college, Community, Excel, excelisfun, Extract, formula, FROM, Gel, Girvin, Highline, INDIRECT, ISNUMBER, LARGE, LEN, mid, mike, Numbers, only, ROW, STRING, Sum, SUMPRODUCT, Text

Excel Magic Trick 185 Dynamic Formula Extract Data 1criteria

August 4th, 2009

Create a formula to extract part of a table from a larger table. Similar to a query in Access, but with a array formula in Excel that uses the functions: INDIRECT, IF, ROWS, COUNTIF, INDEX, SMALL, and ROW. This formula will extract records that meet one condition (criteria criterion) and list them in order with no extra rows between records. We will also use the INDIRECT function to save on formula creation time. Related Videos: YTLE#84: A Few Data Validation Tricks Excel Magic Trick #180 …

Tags: array, college, Community, COUNTIF, Excel, formula, functions, Gel, Girvin, Highline, IF, INDEX, mike, ROWS, SMALLROW, with