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

Point taken

--
Regards,

Ashish Mathur
Microsoft Excel MVP
www.ashishmathur.com

"Lars-Åke Aspelin" wrote in message
...
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