m2 behinde a number and I want to sum

5 m²
3 m²
7 m²
2 m²
2m²
12m²
How can I get the sum without clearing the m2 in the field?

Let's say the first on in in A1
Insert a new column B
In B1 enter =--LEFT(A1,LEN(A1)-3); this will retunr the number 5 (if you
have 5 m² in A1)
Copy down the column and add
Now you have real numbers that can be added

For a permanent solution: after you have done the above
Select all the new B values and give them the custom format: 0 "m²"
Select all the B values and Copy, with them still selected use Edit | Paste
Special - Values to turn the formulas to numbers
Now you have real numbers that can be added
Now you can delete column A
best wishes
--
Bernard Liengme
http://people.stfx.ca/bliengme
Microsoft Excel MVP

"sas" wrote in message
...
5 m²
3 m²
7 m²
2 m²
2m²
12m²
How can I get the sum without clearing the m2 in the field?

You need to first create a helper column to strip out the number. Something
like:
=VALUE(LEFT(A1,FIND(" ",A1)-1))

And then perform a summation of the helper column.
--
Best Regards,

Luke M
*Remember to click "yes" if this post helped you!*


"sas" wrote:

5 m²
3 m²
7 m²
2 m²
2m²
12m²
How can I get the sum without clearing the m2 in the field?
.