Part II: Working with Formulas and Functions
390
To make the formula work with a horizontal range, you need to transpose the array of integers
generated by the ROW function. The TRANPOSE function is just the ticket. The modified array for-
mula that follows works only with a horizontal Data range:{=IF(n=0,0,SUM(IF(MOD(TRANSPOSE(ROW(INDIRECT
(“1:”&COUNT(Data))))-1,n)=0,Data,””)))}Removing non-numeric characters from a string .....................................................
The following array formula extracts a number from a string that contains text. For example, con-
sider the string ABC145Z. The formula returns the numeric part, 145.{=MID(A1,MATCH(0,(ISERROR(MID(A1,ROW(INDIRECT
(“1:”&LEN(A1))),1)*1)*1),0),LEN(A1)-SUM((ISERROR
(MID(A1,ROW(INDIRECT(“1:”&LEN(A1))),1)*1)*1)))}This formula works only with a single embedded number. For example, it fails with a string like
X45Z99 because the string contains two embedded numbers.If you would like to better understand how some of these complex array formulas work, consider using
a handy tool: the Formula Evaluator. Select the cell that contains the formula and then choose
Formulas ➪ Formula Auditing ➪ Evaluate Formula. The Evaluate Formula dialog box shown in the fig-
ure here.Click the Evaluate button repeatedly to see the intermediate results as the formula is being calculated.
It’s like watching a formula calculate in slow motion.Using the Excel Formula Evaluator