Selected Answer
Note- the original text (at the bottom of this Answer) should have been a discussion on your question, not an answer.
In the attached spreadsheet, I've used a User Defined Function (UDF) to calculate a correct weight in ounces from cells which may contain text like oz, ounce lb or pound (if it sees lb or pound, it gets the value and mulitplies that by 16). Look in the Tutoirial section to find out about UDFs.
You can type a formula "=AddWeights(<<range>>)" anywhere in the workbook, where <<range>> could point to C537:C640 as you say.
In the sample file, there are two yellow cells with the formula. In A7, it points to A1:A5 to see that 1 of each type add to the correct number of ounces (any number is assumed to be in ounces). In D6 it adds the string values you gave in your question.
Note that the UDF produces a number OzWt (e.g. 35) REVISION: In the revised file attached, the last line of the UDF code is changed to return OzWt converted to a value in pounds and ounces e.g. 35 (oz) as "2 lb 3 oz" (and avoids the need for cutom formatting of the number).
The UDF code is below and (if you want to use it on your workbook, add a Module in VB Explorer then copy the code below (and then write the formula in the cells you need to add the values.
Hope this helps.
Function AddWeights(ByVal SlctRange As Range)
OzWt = 0
For Each Cell In SlctRange 'loop through range
If IsNumeric(Cell) Then 'if a cell is a number, assume in ounces and just add it
OzWt = OzWt + Cell
Else
'Extract the number by trimming non-numbers from the right
WtStr = Cell.Text
For n = 1 To Len(Cell.Text)
If Not IsNumeric(Right(WtStr, 1)) Or Right(WtStr, 1) = "." Then WtStr = Left(WtStr, Len(WtStr) - 1)
If Len(WtStr) = 1 Then Exit For
Next n
'text if the number was oz or lb...
If Cell Like "*oz" Or Cell Like "*ounce*" Then OzWt = OzWt + Val(WtStr) ' convert string to value with Val
If Cell Like "*lb" Or Cell Like "*pound*" Then OzWt = OzWt + Val(WtStr) * 16 'mulitply by 16 if pounds
End If
Next Cell
AddWeights = CInt(OzWt / 16) & " lb " & (OzWt Mod 16) & " oz" ' Convert to pounds and ounces then return
End Function
ORIGINAL ANSWER (Discussion point) WAS:
Hi
Do you really need to have mixed (or undefined) weight units in those strings? I.e. oz, none (assumed ounces) and lb? (It's not good practice!)
If that's really necessary, how do you want to express the result (in which units)?
Please clarify in your question, preferably also attaching an Excel file so we can try any solutions.