Hi - Dave here.
Happy Friday!
Recently, I was playing with a new function in Excel called TOROW. This humble little function has just one job: transform an array into a single row.
While this can be useful in certain problems, TOROW has another handy feature I had never really used - it can ignore errors or empty values.
You can see a nice example of this feature in action below, where the goal is to extract property information from the text in column B. The core of this formula in C5 is:
=TOROW(TEXTSPLIT(B5," ")+0,2)
[
Download the workbook and read the full explanation]
In a nutshell: (1) TEXTSPLIT splits the text into words, (2) adding zero forces non-numeric values to errors, and (3) TOROW removes the errors. Very slick, and a huge improvement over the complicated formulas required to perform this task in an older version of Excel. The DROP function is used to discard the first number. Read the complete explanation and download the workbook at the link above.
Note: this formula requires Excel 365. Excel is changing very quickly right now!
Excel formulas
We maintain a list of over 500 working formulas
here.
If you need more structure, we also offer
video training.
Have a great weekend!
Dave
