How can I SUM only numbers in cells that contains numbers and text ? I am using Excel 2010 For example :
I need to SUM 2 cells that contains data like distances or salary with suffix like 20 pounds or 20 kms ?
need to exclude kms or pounds and just sum the numbers. Any idea? Thanks..
CodePudding user response：
Personally I think there are better ways to do this to make it more generic but if you're only dealing with a few suffixes then it's just easier to put it all in one formula.
Try this ... =NUMBERVALUE(TRIM(SUBSTITUTE(SUBSTITUTE(LOWER(A1),"kgs",""), "pounds", "")))
An example is shown below ...
... using SUBSTITUTE makes it easy to remove everything nicely. No need to search for specific positions within the string itself.
CodePudding user response：
Sample Table
| Value 1 | Value 2 | Sum | Code |
|---|---|---|---|
| £20 | £1.79 | £21.79 | ="£"&RIGHT(B1,2) RIGHT(B2,4) |
| 17kg | 11kg | 28kg | =LEFT(C1,2) LEFT(C2,2)&"kg" |
How it works:
="£"&RIGHT(B1,2) RIGHT(B2,4)
="£" - Puts a pound (£) sign at the beginning
&RIGHT(B1,2) - Puts in the right 2 values of B1 (20)
RIGHT(B2,4) - Adds the right 4 values of B2 (1.79) to the right 2 values of B1 (20)
= £(20 1.79)
= £21.79
How it works:
=LEFT(C1,2) LEFT(C2,2)&"kg"
=LEFT(C1,2) - Puts in the left 2 values of C1 (17)
LEFT(C2,2) - Adds the left 2 values of C2 (11) to the left 2 values of C1 (17)
&"kg" - Puts "kg" at the end
= (17 11)kg
= 28kg
Find out more here