ft., and the size of the lot in acres.
Forcol_delimiter,we provide a single space (" “).
Removing the non-numeric values
The next step in the process is to remove the non-numeric values.
At first glance, this is a puzzle, becauseall the valuesin the array are text,includingthe numbers.
The result is an array like this:
Notice #VALUE!
Next, we need to discard the errors.
Pretty cool, huh?
We can do this with theDROP function, which is designed to remove rows or columns from an array.
We can do that by providing the number 1 for thecolumnsargument:
Notice therowsargument is left empty.
As the formula is copied down, the formula extracts the same information from each property as shown.
TEXTSPLIT creates an array of values, and adding zero forces the text values to errors, leaving numbers.
The FILTER function then removes the errors by testing for numbers with theISNUMBER function.
However, because of the structure of FILTER, we need to repeat the TEXTSPLIT operation twice.
The TOCOL solution avoids this redundancy, resulting in a compact, elegant formula.
TEXTSPLIT can split text into rows or columns.
TOROW Function
The Excel TOROW function transforms an array into a single row.
By default, TOROW will scan values by row, but TOROW can also scan values by column.
The number of rows and columns to remove is provided by separaterowsandcolumnsarguments.
