• Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User

    Hui...

  • When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

summing all numbers in a single cell that also contains non-numeric characters

I

indesignkat

Member
Hello fine Chandoovians. I'm working on an excel spreadsheet to inventory my Magic:The Gathering cards and I've got what is (for me) a stumper...

Say a cell contains this: "{3}{G}//{4}{R}{R}". (Magic players will recognize that as the casting cost of a fuse card). My spreadsheet has a column for each letter than can appear in there (W,U,B,R,or G) and the count of that letter's instances in there (so for the value above it would return 1 in the G column and 2 in the R column) but I can't figure out how to sum any and all numbers that appear. For the example above, I would want this formula to return a 7 (3+4).

Any ideas?
 
I'd been working on this for a while, but after another 20 minutes of scouring the interwebs I found a solution. It's not elegant, but it works:

using the same example: {3}{G} // {4}{R}{R}
add a helper column to pull all the numbers from the cell using this formula:

=SUMPRODUCT(MID(0&A1,LARGE(INDEX(ISNUMBER(--MID(A1,ROW($1:$25),1))*ROW($1:$25),0),ROW($1:$25))+1,1)*10^ROW($1:$25)/10)

which returns 34

then use this formula:

=SUMPRODUCT(1*MID(A1,ROW(INDIRECT("1:"&LEN(A1))),1))

to turn the 34 into a 7
 
@indesignkat

If the numbers are single and non-repetive than we can use below array formula to get the sum. Assuming value string in F13.
=SUM(IF(ISNUMBER(VALUE(MID(F13,SEARCH({1,2,3,4,5,6,7,8,9,0},F13),1))),VALUE(MID(F13,SEARCH({1,2,3,4,5,6,7,8,9,0},F13),1)),0))

Enter with Ctrl+Shift+Enter.

Regards!
 
=SUM(IF(ISNUMBER(VALUE(MID(F13,ROW(INDIRECT(1&":"&LEN(F13))),1))),VALUE(MID(F13,ROW(INDIRECT(1&":"&LEN(F13))),1)),0))
array formula giving same result as @Lori formula.

Regards!
 
Technically it is possible for a 2 digit number to be in there, but it's so rare that I'm not concerned with it. Any value over 8 will stand out very easily to me for other, unrelated reasons.

The format does not always follow the style of the example. More examples:

{0}
{8}{G}{G}{G}
{3}{U}{R} // {2}{R}
{1}{G} // {4}{W}{W}{U}

Lori: that works perfectly, thank you!!
 
You must log in or register to reply here.
Back
Top