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.
0
Discussion
I can't figure out why you would want to add ounces to pounds. The idea seems to contradict the purpose of having a sum. However, as John already pointed out, the accepted solution is to place quantity and unit in separate columns and then use SUMIF to separately add up ounces and pounds. Excel has many solutions to deal with related data in separate columns but when you mix data types there are immediate drawbacks and the one you discovered isn't the worst of them.
Variatus (rep: 4258) Nov 25, '20 at 7:01 pm
Answers
0
Selected Answer
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
Firstly, you omitted to say in your question that you were using an array formula but I should have guessed and spotted that you were finding "" (=nothing) rather than a space. Wildcards aren't allowed in FIND so I don't see a way to use in your array formula with strings without a space or a non-numeric separator
As I said earlier today, your question as worded remains confusing for other users.
As I said earlier today, your question as worded remains confusing for other users.
John_Ru (rep: 502) Nov 30, '20 at 12:37 pm
0
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 FunctionORIGINAL 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
Unfortunately I do have mixed units, but I do not need to have any units carried down to the total, just the numerical total. C*** is the cell deisgnation. The number in that cell might have up to 6 characters counting the decimal places.
dschneid Nov 25, '20 at 3:05 pm
Ah, now I see (partially)... C537 is the cell reference containing "1oz" (not part of a string like "C537 1oz"). As I said, it would help if you incldued a file in your Question.
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?
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?
John_Ru (rep: 502) Nov 25, '20 at 5:09 pm
Sorry, I should have been more clear. The oz and lb are just examples of text that might be encountered in any of my columns, they would not be mixed. I convert the units in another site on the spreadsheet, all I am trying to do here is separate the text from the number so the column adds up correctly. This seems like a pretty simple and common thing to ask, I don't know why excel has such a problem with it that you would have to make a big complicated formula or macro to solve it. Understand I am not faulting anyone on this site, just the microsoft developers. People on this site have been a HUGE help to this novice, I have been very obtuse on describing my problems and appreciate your patience :). I am obviously not at all good with macros.
dschneid Nov 30, '20 at 9:50 am
Your original stated requirement to "...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.
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.
John_Ru (rep: 502) Nov 30, '20 at 10:39 am
I have tweaked the code I provided earlier but won't add to my Answer until you clarify you needs (by a revision to your Question). It would be helpful to others too if you modified the title of your Question to reflect the fact that you now want to extract numbers from mixed text (not add those numbers)
John_Ru (rep: 502) Nov 30, '20 at 11:33 am