Excel - adding numbers within a text string

Question

I would like to add numbers that are within a text string but I need to only add the number to the left of matching criteria.  My data looks like this  

Column H:  

1 Meal

1 Meal, 1 Extra

1 Meal, 1 Piz

1 Piz, 2 Piz

1 Extra

1 Extra, 1 Meal

I want to sum each type of menu choice

Extras: 3

Meals: 4

Piz: 4

Thank you in advance.

Answer 1

A solution using Excel formulas, though a long formula, and valid for upto 2 occurrences of a type in one cell. Refer below image:

Enter below formula as an array formula (CTRL+SHIFT+ENTER) in cell D2 and copy down:

=SUM(IF(ISNUMBER(VALUE(IFERROR(TRIM(RIGHT(SUBSTITUTE(TRIM(LEFT(SUBSTITUTE($A$2:$A$10,C2,REPT(" ",255)),255)),",",REPT(" ",255)),255)),0))),VALUE(IFERROR(TRIM(RIGHT(SUBSTITUTE(TRIM(LEFT(SUBSTITUTE($A$2:$A$10,C2,REPT(" ",255)),255)),",",REPT(" ",255)),255)),0))))+SUM(IF(ISNUMBER(VALUE(IFERROR(TRIM(RIGHT(SUBSTITUTE(TRIM(LEFT(SUBSTITUTE(SUBSTITUTE($A$2:$A$10,C2,"?",1),C2,REPT(" ",255)),255)),",",REPT(" ",255)),255)),0))),VALUE(IFERROR(TRIM(RIGHT(SUBSTITUTE(TRIM(LEFT(SUBSTITUTE(SUBSTITUTE($A$2:$A$10,C2,"?",1),C2,REPT(" ",255)),255)),",",REPT(" ",255)),255)),0))))

Regards,

Amit Tandon

Answer 2

Amit, it works perfectly!  That is exactly what we needed.  Thank you!!

 I really appreciate the help from such brilliant people.

Answer 3

How many items may end up in the same cell? Your sample shows 2. Could it be more?

Is there any way you could get the data from the web in a different format?

In any case, you need to perform some data cleansing before you can work with the numbers buried in the text. There are many possible approaches. It depends what you are comfortable with. Would you be fine with a VBA solution?

Answer 4

Thank you for your response.  I am getting the data from a website form where multiple selections can be chosen so that is the reason for the difficult format.  If I modify the website form so that quantity and item have to be filled in, it will be more cumbersome for parents.  Is that the only way I can get a sum of each item?  

Thanks much!

Answer 5

Hello,

with a data entry format like that, it will be either immensely complex or even impossible to produce the result you describe. There are a few ground rules for spreadsheet design that, when followed, will make things a lot easier.

If you enter the data into a simple table, one column for the text, one column for the number, and put each text/number combination into its own row, then creating the summary you describe is really easy: Select the data, click Insert > Pivot table. Drag the "Food Type" field to the Rows and the "amount" field to the Values area and you're done.