yogi-Compute Total Carbs In A Table Of Diabetic Diet With Mixed text And Numbers

                                          Google Spreadsheet   Post  #1022

Yogi Anand, D.Eng, P.E.      ANAND Enterprises LLC -- Rochester Hills MI     www.energyefficientbuild.com.    Feb 10, 2013
user Nigel Hackney said:(http://productforums.google.com/forum/?zx=kjnxa5fsz2px#!category-topic/docs/spreadsheets/NVuoecmCIuY)
Using SPLIT with ARRAYFORMULA, or How to sum all the numbers in a cell containing both text and numbers

I have had help on a couple of topics this week, but am nearly finished with my project so I hope this might be my last request!

The problem is almost exactly as set out in this question: http://productforums.google.com/forum/#!topic/docs/vYC1XUWWgck

Briefly, I have a column containing cells with text such as "sandwich 20, biscuit 10, milk 15", for recording and counting carb intake for diabetes. The text comes in that format from a Google Form, if that makes any difference. The question is how to sum the number values from each cell.

For a single cell, Ahab presented a formula in the above post, which works perfectly (of course): =SUM( SPLIT( A1; CONCATENATE( SPLIT( A1; "0123456789" ))))

However, I've been trying to then apply that formula to the whole column, using ARRAYFORMULA language, without success. I have tried various versions, such as =ARRAYFORMULA(SUM( SPLIT( A1:A; CONCATENATE( SPLIT( A1:A; "0123456789" ))))

So, maybe I just don't understand how to use ARRAYFORMULA properly, and if so if someone could set me straight on that I would appreciate it. [Just to be clear, what I want is the sum of the numbers per cell, not the total number sum from all cells.]

However, there are other forum questions which seem to indicate that there are know issues when trying to use ARRAYFORMULA with SPLIT, such as: http://productforums.google.com/forum/#!topic/docs/qPvvanNuU0g

The above post suggests using a function called RegExReplace, but I am afraid that is a bit beyond my skill level, and its not immediately apparent to me whether or how to use that function in relation to this particular extract-and-sum-numbers-from-text problem.

So, what I think this boils down to is:

1. Can anyone suggest how to modify Ahab's original formula into an ARRAYFORMULA which works, please?

2. If not, can anyone point me towards a different solution to the actual problem of extracting and summing the numbers from a single text cell, which solution can then use ARRAYFORMULA (or indeed anything else which will make it work automatically with an ever-expanding array).

Many thanks.

[If it matters, I think I understand the operation of Ahab's original formula, in that the first SPLIT (at the right hand end) isolates all the non-number strings, those strings are then CONCATENATEd, that non-number list is then used to re-SPLIT the same cell, which this time leaves only the numbers, and then the SUM does the sum. Beautifully neat!]

---

Hi

Thanks for the suggestion. Here's a dummy spreadsheet 

https://docs.google.com/a/sylvandale.co.uk/spreadsheet/ccc?key=0AqeNLWJtPxMJdEpyS1hxOEtfWGJVVEZxMDNzZVJDSHc&usp=sharing

The spreadsheet is simplified - the real thing is a lot bigger, and ever-growing.

Basically the end result required is (for each row) to sum the numbers (if any) in col D and col E and put the result in col F. [I have included Col H manually just to make sure it is clear what the sum should be for each row.]

The core of this problem, at least for me, is how to extract the numbers from the text cell of col D, with an ARRAYFORMULA or similar expression. The rest should be easy enough. But I need the sheet to do it automatically as soon as each new row is entered from a form submission, as I want use that calculation result immediately elsewhere (to work out an insulin dose). 

You can see that sometimes there is nothing in col D, sometimes nothing in col E, sometimes nothing in either, and sometimes data in both. I am assuming I will be able to deal with omitting invalid results by using some IF statements, once I have got the calculation to work.

I haven't had chance to look in detail at KylerH's suggestion yet, but will do soon. Any and all other suggestions are much appreciated.

Thanks again. 'Tis a wonderful forum.

------------------------------------------------------------------------------------------------------

following is a solution to the problem using some helper columns that are automatically and dynamically populated as new data is logged in