Can I add a cell with text in it

[Djblois]

If I have the numbers

15 lbs.
30 lbs.

Can I add them to get 45 lbs.

With a Macro?? I want to keep the lbs. in the cell. because some people don't know what the units column is. (sometimes the number in the units column would be cases and sometimes pounds)

[lenze]

You can if you use a Custom format for your cells

Format>Cells>Number Tab>Custom.
For type enter ## "lbs"

HTH

lenze

[fixo]

If I have the numbers

15 lbs.
30 lbs.

Can I add them to get 45 lbs.

With a Macro?? I want to keep the lbs. in the cell. because some people don't know what the units column is. (sometimes the number in the units column would be cases and sometimes pounds)

Not sure about..
Select cells you need to summ before run
[vba]
Sub SumCellsWithUnits()
Dim myVar As Variant
Dim i, sm, r, c As Long
Dim s, un As String
sm = 0
un = "lbs"
With Selection
r = .Rows.Count
c = .Columns.Count
myVar = .Value
For i = LBound(myVar) To UBound(myVar)
s = myVar(i, 1)
s = Left(s, Len(s) - InStr(1, s, un, 1))
sm = sm + CInt(s)
Next
End With
ActiveSheet.Cells(r + 1, c).Value = CStr(sm) & Chr(32) & un
End Sub
[/vba]

~'J'~

[mdmackillop]

Lenze has it right, but if you do have text value, this code will work as long as there is a space after the number.
[vba]
Function SumNum(Data As Range)
Dim cel as Range, tot as Double
For Each cel In Data
tot = tot + Split(cel, " ")(0)
Next
SumNum = tot
End Function

[/vba]

[matthewspatrick]

This is a tweak on Malcolm's approach. No space is required between the numeric part and the text part, but be advised that Val() stops looking when it finds a non-numeric character (except for periods that could be decimal points, of course!).


[VBA]
Option Explicit
Function SumNum(Data As Range)
Dim cel As Range, tot As Double
For Each cel In Data
tot = tot + Val(cel)
Next
SumNum = tot
End Function
[/VBA]