Hello, I am trying to add a column of numbers that may also contain text in the same cells, ie; C537 1oz, C538 100.25, C539 10 lb (total 111.25). I want to ignore the text yet add the numbers. The formula I am using is =SUM(IFERROR(0+LEFT(C537:C640,FIND("",C537:C640&"")),0)) this formula appears to ignore the text in cells C537 to C640 but the total is wrong. I think the syntax for decimal places is wrong but I can't figure out what syntax to use. Thank you

# Sum a column of numbers that also contains text.

### Discussion

## Answers

Okay, after playing around with tihe formula, I figured out my mistake - I needed to put a space between the " "s. This works as long as there is a space between the number and the text to the right of the number (and the text is on the right). I really don't have an answer if there is no space between text and number except to use the data validation tool to idiot proof the input .

### Discussion

As I said earlier today, your question as worded remains confusing for other users.

*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.*

### Discussion

Nevertheless the numerical total will make no sense in the case of mixed units e.g. C537 (1oz) plus C539 (10lb) should be 161oz since there are 16oz to each pound,

**not**(10+1=) 11 mixed units; your answer would be over 14 times too small!

What would such a sum be used for?

*.to add a column of numbers that may also contain text*..." certainly fooled both @Variatus and me!

A modified version of the UDF provided in my Answer above could separate decimal numbers from text (and also e.g. put them in a separate column) - do you have the units for those mixed cells in a separate column and use SUMIF additions to add the various units perhaps?

Give us a better idea of what you're doing (perhaps add a workbook to your Question) and we will have a better chance of helping you.