CODEWITHSUNDEEP
excelexcel-formula
Darius
Darius

Reputation: 612

Excel - extracting numeric value from cell

All,

I have a workbook of values laid out like so:

 |    A   |     B    |    C    |
1| VAR - 2| VAR - 18 | VAR - 7 |

I am trying to extract the values from A1:C1 and total them up in D1. So D1 should be 27. Anyone know how to do this? I started with:

=SUMPRODUCT(- -ISNUMBER(MID(A1,ROW($A$1:$A$200),1)+0))

But this seems to only be calculating the number of digits, not the total value. Any ideas?

Darius

Upvotes: 1

Views: 5382

Answers (4)

user3616725
user3616725

Reputation: 3655

or you could get the MORFUNC addon* and simply do:

{=SUM(VALUE(REGEX.MID(A1:C1,"[0-9]")))}


MOREFUNC ADDON

Upvotes: 0

Andrew
Andrew

Reputation: 7768

Here is your formula... USE CONTROL+SHIFT+ENTER to calculate!

it will work with any mix of characters and numbers. It will extract the number and dispose of the characters

=(1*MID(A1,MATCH(TRUE,ISNUMBER(1*MID(A1,ROW($1:$9),1)),0),COUNT(1*MID(A1,ROW($1:$9),1))))+(1*MID(B1,MATCH(TRUE,ISNUMBER(1*MID(B1,ROW($1:$9),1)),0),COUNT(1*MID(B1,ROW($1:$9),1))))+(1*MID(C1,MATCH(TRUE,ISNUMBER(1*MID(C1,ROW($1:$9),1)),0),COUNT(1*MID(C1,ROW($1:$9),1))))

Upvotes: 1

Ravi Yenugu
Ravi Yenugu

Reputation: 3898

In D1 Enter

=SUMPRODUCT(IF(ISNUMBER(--MID(A1:C1,--FIND("-",A1:C1,1)+1,255)),--MID(A1:C1,--FIND("-",A1:C1,1)+1,255),0))

Then click anywhere inside D1 and hit Ctrl+Shift+Enter

255 here is the number of characters after - in the cell

Upvotes: 1

Matt Cremeens
Matt Cremeens

Reputation: 5151

I believe this should work

{=SUM(VALUE(TRIM(RIGHT(A1:C1,2))))}

Note that it is an array formula, so be sure to hit ctrl-shift-enter when inside the cell.

If your number could be longer than 2 digits, then this may be more appropriate (general)

{=SUM(VALUE(TRIM(RIGHT(A1:C1,LEN(A1:C1)-6))))}

Upvotes: 1

Related Questions