Microsoft Office Tutorials and References

In Depth Information

{=ROW(INDIRECT(“1:”&LEN(A1)))}

This function returns an array of consecutive integers beginning with 1 and ending with the number of digits in

the value in cell A1. For example, if cell A1 contains the value 409, the LEN function returns
3
, and the array

generated by the ROW functions is

{1,2,3}

For more information about using the INDIRECT function to return this array, see

Chapter 14.

This array is then used as the second argument for the MID function. The MID part of the formula, simplified a

bit and expressed as values, is the following:

{=MID(409,{1,2,3},1)*1}

This function generates an array with three elements:

{4,0,9}

By simplifying again and adding the SUM function, the formula looks like this:

{=SUM({4,0,9})}

This formula produces the result of
13
.

The values in the array created by the MID function are multiplied by 1 because the MID

function returns a string. Multiplying by 1 forces a numeric value result. Alternatively,

you can use the VALUE function to force a numeric string to become a numeric value.

Notice that the formula doesn't work with a negative value because the negative sign is not a numeric value.

Also, the formula fails if the cell contains non-numeric values (such as 123A6). The following formula solves

this problem by checking for errors in the array and replacing them with zero:

{=SUM(IFERROR(MID(A1,ROW(INDIRECT(“1:”&LEN(A1))),1)*1,0))}