CODEWITHSUNDEEP
excel-formula
Chris
Chris

Reputation: 769

How to sum several bracket-surrounded numbers of a single cell with Excel formula?

I have one cell containing several lines, including numbers inside brackets, which I want to sum-up with a single Excel formula (no VBA).

The following approach already works for single bracket: https://exceljet.net/formula/extract-text-between-parentheses

But I need extended approach... here an example for the content of one single Excel cell to which I search for an formula, which should result in sum of "8":

The task requires following effort (incl. documentation)
- create plan (2h)
- execute test (14h)
- write report (draft) (2h)

Possible approach: The formula should search for all numbers inside the mask <"(" x "h)">, where x must be summed-up.

UPDATE: The formula should also work with numbers >=10.

UPDATE2: It should also work in case there are other comments in brackets, also after presence of first (xh) number. See "(draft)" in example, last row.

Upvotes: 0

Views: 3696

Answers (2)

ian0411
ian0411

Reputation: 4265

Borrow the formula from this post #6 (https://www.mrexcel.com/forum/excel-questions/362184-extracting-multiple-numbers-string.html) and modified to fit your need (single cell formula). Assuming you are going to enter the formula in cell B1:

{=SUM(VALUE(MID(0&A1,LARGE(ISNUMBER(--MID(A1,ROW(INDIRECT("1:"&LEN(A1))),1))*ROW(INDIRECT("1:"&LEN(A1))),ROW(INDIRECT("1:"&LEN(A1))))+1,1)))}

Basically this is to assign each character with its index and then get the numeric value to sum up. Please note this is an array formula. Please click Ctrl + Shift + Enter together.

REVISED:

Here is the array formula (click Ctrl + Shift + Enter together) to extract two-digit numbers:

{=SUMPRODUCT(IFERROR(0+("0"&TRIM(MID(SUBSTITUTE(SUBSTITUTE(S‌​UBSTITUTE(LOWER(MID(‌​‌​A1,SEARCH("h)",A1)‌​-4,LEN(A1))),"h","")‌​,")","("),"(",REPT(" ",1000)),ROW(INDIRECT("1:20"))*2*1000-999,1000))),0))}

What this does is to massage the text first by removing unnecessary content, remove h wording and convert ) to 999 blanks. Then you can extract numbers and add up. IFERROR will made the anything not numbers to 0. Hope this can solve your problem.

Upvotes: 1

Glitch_Doctor
Glitch_Doctor

Reputation: 3034

I appreciate that this is not in any way elegant, however it is working. I do not have time to run through the logic here right now (might edit it in later though) but essentially it is a load of search index logic.

I have Used 5 columns for each formula which assumes a maximum of 5 values but you can adjust this as needed by dragging the formula over more columns as it will begin looking for the next "(?h)" after the previous column's found value.

Red cell formula: =SEARCH("(?h)",$A1)&" - "&SEARCH("h)",$A1)

Orange cell formula: =SEARCH("(?h)",$A1,MID(B1,SEARCH("- ",B1)+2,LEN(B1)-(SEARCH("- ",B1)+1))*1)&" - "&SEARCH("h)",$A1,MID(B1,SEARCH("- ",B1)+2,LEN(B1)-(SEARCH("- ",B1)+1))+1)

Yellow cell formula: =MID($A1,LEFT(B1,SEARCH(" - ",B1))+1,((MID(B1,SEARCH("- ",B1)+2,LEN(B1)-(SEARCH("- ",B1)))*1)-(LEFT(B1,SEARCH(" - ",B1))*1))-1)*1

Green cell formula: =SUMIF(G1:K1,">="&0)

enter image description here

Upvotes: 1

Related Questions