Schielrn,

The formula from Domenic at post 6:

Extracting Multiple Numbers from String
works great (I changed my string to cell A2):

=SUM(MID(0&A2,LARGE(ISNUMBER(--MID(A2,ROW(INDIRECT("1:"&LEN(A2))),1))*ROW(INDIRECT("1:"&LEN(A2))),ROW(INDIRECT("1:"&LEN(A2))))+1,1)*10^ROW(INDIRECT("1:"&LEN(A2)))/10)

Ron Coderre,

This formula is beautiful (I changed my string to cell A2):

=SUMPRODUCT(MID(0&A2,LARGE(INDEX(ISNUMBER(--MID(A2,ROW($1:$25),1))*

ROW($1:$25),0),ROW($1:$25))+1,1)*10^ROW($1:$25)/10)

Ron, I like your formula because of its compactness and non-array non-Ctrl-Shift-Enter.

I like Domenic’s because of the robustness of the ROW(INDIRECT("1:"&LEN(A2))).

Mashing your two formulas together (in Excel 2007 only – exceeds 7 nesting rule in Excel 2003) I got:

=SUMPRODUCT(MID(0&A2,LARGE(INDEX(ISNUMBER(--MID(A2,ROW(INDIRECT("1:"&LEN($A$2))),1))*

ROW(INDIRECT("1:"&LEN($A$2))),0),ROW(INDIRECT("1:"&LEN($A$2))))+1,1)*10^ROW(INDIRECT("1:"&LEN($A$2)))/10)

I learn a lot from both formulas. In essence, are these the concepts of how the formula is working:

1) Extract characters with MID

2) Convert extracted characters to numbers and errors with --

3) Ask array if elements are numbers with ISNUMBER

4) Multiply array of TRUE/FALSE by position -- *ROW(INDIRECT("1:"&LEN($A$2)))

5) Organize by position with LARGE (largest to smallest including zeroes)

6) Add 1 to avoid error with MID caused by zeros from LARGE

7) Concatenate a zero to beginning of string so the 1 added to the zeros will not cause error when MID extracts numbers

8) Multiply the extracted numbers by 10^ROW(INDIRECT("1:"&LEN($A$2))) to get the correct number of zeros for each extracted number

9) Divide by 10 to deal with the fact the we had a zero concatenated to the front of the string

10) Added

Am I getting the concepts, right?

## Like this thread? Share it with others