Newbie Question

  • Thread starter Thread starter Thorrrr
  • Start date Start date
T

Thorrrr

Hi

Sorry for this simple question I keep forgetting how to do it. When you
enter data in a table and it is prefixed by letters E.g. : 103 kg or 210 lbs
how do you enter it so you can perform calculation on the numbers?

If I enter theses prefixes I cannot get Excel to recognise the numbers??

How do I get the result of a sum to enter the prefix in the result?

Cheers Dale
 
Dale,

You need to extract the numbers to sum them:

=SUMPRODUCT(VALUE(LEFT(A1:A10,LEN(A1:A10)-LEN(" lbs"))))

or

=SUMPRODUCT(VALUE(LEFT(A1:A10,LEN(A1:A10)-LEN(" kg"))))

A1:A10 must be filled for this to work.

If you need to do more calcs, use another column of formulas (one per filled
cell), or enter your values as numbers, and use formatting for display of
the ' lbs' part: format the cells using custom, 0 "lbs", for example.

PS: prefixes that follow numbers are called suffixes ;-)

HTH,
Bernie
MS Excel MVP
 
Hi

You or simply enter numbers without prefixes/units. You can have them in
column header, when there is a need for them,
or Format range/column as custom, p.e.
#" kg" or #" lbs"
Now when you enter p.e. 103 into formatted cell, 103 kg is displayed. But
you still can make calculations with it as with an usual numeric entry.
 
Here is a macro that will sum the numbers if all are to the left of the
first space.

Sub SumNumbersFromText()
For Each c In Range("b1:b22")
mt = Val(Left(c, Application.Find(" ", c)) + mt)
Next c
MsgBox mt
End Sub
 
Back
Top