Formula to sum the digits
On Tue, 6 Jan 2009 15:13:14 +0530, "Ashish Mathur"
wrote:
Hi,
You can use the following array formula (Ctrl+Shift+Enter)
=SUM(1*MID(MID(B5,SEARCH("[",B5,1)+1,SEARCH("]",B5,1)-2)*1,ROW(INDIRECT("1:"&(LEN(B5)-2))),1))
Your formula is either unneccessarily complicated or not complete.
The latter part, with the LEN function, assumes that there are nothing
before the "[" or after the "]".
If that is true you can simply write 2 instead of SEARCH("[",B5,1)+1
and LEN(B5)-2 instead of SEARCH("]",B5,1)-2
If you really want to allow for text before the "[" an after the "]",
like
sometext[2220]somemoretext
the formula has to be more complex. Something like
=SUM(1*MID(MID(A10,SEARCH("[",B5)+1,SEARCH("]",B5)-SEARCH("[",B5)-1),ROW(INDIRECT("1:"&(SEARCH("]",B5)-SEARCH("[",B5)-1))),1))
Lars-Åke
|