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}
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))}
Search JabSto ::

Custom Search