*** Google Sheet ArrayFormula Usage ***
Enables the display of values returned from an array formula into multiple rows and/or columns and the use of non-array functions with arrays.
Many array formulas will be automatically expanded into neighboring cells, obviating the explicit use of ARRAYFORMULA.
To skip blank lines, I use an FILTER() check to skip rows w/o value in required cell.
** EXAMPLES **
* Remove ' at ' from date in format '2012/04/06 at 4:07 pm' located in N2 cell *
=REPLACE(N2,11,4," ")
=ARRAYFORMULA(REPLACE(FILTER(N2:N, NOT(ISBLANK(N2:N))), 11, 4, " "))
* Convert date/time from M2 cell to specific format (date format for WordPress) *
=TEXT(M2, "YYYY-MM-ddTHH:mm:ss")
=ARRAYFORMULA(TEXT(FILTER(M2:M, NOT(ISBLANK(M2:M))), "YYYY-MM-ddTHH:mm:ss"))
* Simply multiply two numbers from 'D2' and 'E2' cells *
=D2*E2
=ARRAYFORMULA(FILTER(D2:D*E2:E, NOT(ISBLANK(D212:D))))
Comments