CODEWITHSUNDEEP
excelexcel-formula
Teco
Teco

Reputation: 5

Excel formula to SUM all numbers in a cell with text

I have cells in my spreadsheet who are like:

RF(4);MN(4);LL(2);TE(4);HA(2)

There is a formula where I can sum all the numbers in that String/Cell?

I can try another way by Office Script, but since he has low speed, and I have to sum a lot of lines of cells with those type of string, i would try to accelerate the process with Excel Formula as much as possible.

best regards

Upvotes: 0

Views: 2598

Answers (2)

Scott Craner
Scott Craner

Reputation: 152450

With Excel 2013 and later you can use FILTERXML to pull out the numbers and wrap that in SUMPRODUCT to return the sum:

=SUMPRODUCT(FILTERXML("<t><s>"&SUBSTITUTE(SUBSTITUTE(A1,")","</s><s>"),"(","</s><s>")&"</s></t>","//s[number()=.]"))

One note, this does not work in Excel online or the phone/tablet apps. And some versions of mac also do not have FILTERXML.

enter image description here

If they are always single digit we can use:

=SUMPRODUCT(IFERROR(--MID(A1,SEQUENCE(LEN(A1)),1),0))

or older version:

=SUMPRODUCT(IFERROR(--MID(A1,ROW($ZZ$1:INDEX($ZZ:$ZZ,LEN(A1))),1),0))

For some versions this will require the use of Ctrl-Shift-Enter instead of Enter when exiting edit mode to make it an array formula.

enter image description here

Upvotes: 4

Mark S.
Mark S.

Reputation: 2584

  1. What you want to do is delimit the column based on ";"
  2. Data --> Text to Columns --> Delimit / Next --> Semicolon --> Finish
  3. This will separate the column into 5 columns based on the data you gave, now you'll add 5 additional helper columns that will extract the number.
  4. Assuming your new Data goes from A2:E2 and downward, placing this formula in F2:J2 =SUMPRODUCT(MID(0&A2, LARGE(INDEX(ISNUMBER(--MID(A2, ROW(INDIRECT("1:"&LEN(A2))), 1)) * ROW(INDIRECT("1:"&LEN(A2))), 0), ROW(INDIRECT("1:"&LEN(A2))))+1, 1) * 10^ROW(INDIRECT("1:"&LEN(A2)))/10)
  5. After dragging this formula across the 5 columns (separating the numbers from text); Add 1 last column summing these 5 new columns.

This should be able to handle multiple digits in a single group of parenthesis. You can avoid having all the additional columns, by adding the formula for each column together. I just break this out so if there's issues you have visibility in what's happening and how it works.

Upvotes: 0

Related Questions