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

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

Regards

• 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.)

• 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).

• Re: Sum numbers in a single cell

Thanks a lot. it works with both approach.

PO#000(123) --> even works without comma which is nice!

Appreciated

• Re: Sum numbers in a single cell

You're welcome!

Cheers

Participate now!

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