Sum numbers in a single cell

  • HI


    I've been breaking my head trying to figure out that one out.




    I have multiple entries in the same cell


    A1 -> 1(PO#7777), 2(PO#8888), 3(PO#9999)


    or


    A1 -> PO#7777(1), PO#8888(2), PO#9999(3)



    The result/formula i am looking for in cell B1 should be 6 (IE. 1+2+3=6)

  • Re: Sum numbers in a single cell


    So, to clarify, the only two possibilities for the placement of the numbers to be summed are:


    1) Immediately prior to the opening parenthesis in each comma-separated string
    2) In parentheses at the very end of each comma-separated string


    Correct?


    Are the numbers to be summed always single digits, as in your examples?


    Is the other part of every comma-separated string of the precise form PO#XXXX, i.e. PO# followed by 4 digits?


    Regards

  • Re: Sum numbers in a single cell




    the numbers to sum are up to 3digit (i can use a default 3digits(005) to facilitate the formula)

    the PO# on the otherhand are sometime 4 sometime 5 digit and i cannot add zeros as they are purchase orders so they must stay as is.



    i also cant use VB or MACRO.



    thanks for ur help

  • Re: Sum numbers in a single cell


    Quote from XOR LX;780505

    Thanks, but could you also confirm my first two statements?


    1) Immediately prior to the opening parenthesis in each comma-separated string
    2) In parentheses at the very end of each comma-separated string


    Regards


    the answer is Yes on both statements (but it doesn't have to be limited to that... i just used comma because of the formula i was working on used comma. if you have a better way, i am all hears.)


    thanks again for your time

  • Re: Sum numbers in a single cell


    Assuming:


    1) There are never more than 10 comma-separated strings in a given entry
    2) A given entry never contains a mixture of the two types you give, i.e.:


    1(PO#7777), 24(PO#8888), PO#77776(1)


    for example, is not possible.


    then, array formula**:


    =ABS(SUM(IFERROR(0+SUBSTITUTE(MID(SUBSTITUTE(","&A1,",",REPT(" ",20)),FIND("ζ",SUBSTITUTE(SUBSTITUTE(","&A1,",",REPT(" ",20)),"(","ζ",{1,2,3,4,5,6,7,8,9,10}))-{0;6},{5;6}),",",""),"")))


    Regards



    **Array formulas are not entered in the same way as 'standard' formulas. Instead of pressing just ENTER, you first hold down CTRL and SHIFT, and only then press ENTER. If you've done it correctly, you'll notice Excel puts curly brackets {} around the formula (though do not attempt to manually insert these yourself).

Participate now!

Don’t have an account yet? Register yourself now and be a part of our community!