Need Formula for Adding Numbers with Words from the Same Cell

[TheAverageMan]

Hi there. I've been googling and haven't been able to find a working formula for this, and was hoping someone on here could help me with a formula that would combine all those numbers within cell H5, and place them into J5.

The numbers above J5 are all manually computed with a calculator and I just write it in there, but a formula would sure make it easier if they just grabbed all the numbers from the food cell and combined them.

I'm using Microsoft Excel 2010 also.

Thanks.

 

[hiker95]

TheAverageMan,

Welcome to the MrExcel forum.

How about a macro solution?

Sample raw data:

Excel 2007
	H	I
1	Food eaten	Estimated Calories
2	120 cereal, 240 milk, black coffee, 200 protein bar, 120 jerkey, 360 chicken, 120 bbq
3
Sheet1

After the macro:

Excel 2007
	H	I
1	Food eaten	Estimated Calories
2	120 cereal, 240 milk, black coffee, 200 protein bar, 120 jerkey, 360 chicken, 120 bbq	1160
3
Sheet1

Cell Formulas
Range	Formula
I2	=120+240+200+120+360+120

Please TEST this FIRST in a COPY of your workbook (always make a backup copy before trying new code, you never know what you might lose).

1. Copy the below macro code, and, function
2. Open your NEW workbook
3. Press the keys ALT + F11 to open the Visual Basic Editor
4. Press the keys ALT + I to activate the Insert menu
5. Press M to insert a Standard Module
6. Where the cursor is flashing, paste the code
7. Press the keys ALT + Q to exit the Editor, and return to Excel
8. To run the macro from Excel press ALT + F8 to display the Run Macro Dialog. Double Click the macro's name to Run it.

Sub SumStrings()
' hiker95, 09/04/2014, ME803416
Dim c As Range, MySum As String, s, i As Long
Application.ScreenUpdating = False
For Each c In Range("H2", Range("H" & Rows.Count).End(xlUp))
  s = Split(c, " ")
  MySum = "="
  For i = LBound(s) To UBound(s)
    If IsNumeric(s(i)) Then
      MySum = MySum & TextNum(s(i), 0) & "+"
    End If
  Next i
  If Right(MySum, 1) = "+" Then MySum = Left(MySum, Len(MySum) - 1)
  c.Offset(, 1).Formula = MySum
Next c
Application.ScreenUpdating = True
End Sub
Function TextNum(ByVal txt As String, ByVal ref As Boolean) As String
' jindon
' http://www.mrexcel.com/forum/showthread.php?t=362461
' =TextNum(A1,1)
' 1 for Text only, 0 for Numbers only
With CreateObject("VBScript.RegExp")
  .Pattern = IIf(ref = True, "\d+", "\D+")
  .Global = True
  TextNum = .Replace(txt, "")
End With
End Function

Before you use the macro with Excel 2007 or newer, save your workbook, Save As, a macro enabled workbook with the file extension .xlsm

Then run the SumStrings macro.

 

[TheAverageMan]

Wow, thanks for the super detailed response. I'll try that after dinner and update if it worked. thanks.

 

[hiker95]

TheAverageMan,

Thanks for the feedback.

You are very welcome.

Hope to hear back from you later.

 

[TheAverageMan]

It works. Thanks a lot man.

 

[hiker95]

TheAverageMan,

Thanks for the feedback.

You are very welcome. Glad I could help.

And, come back anytime.

 

[István Hirsch]

Put this formula in I2 with Ctrl-Shift-Enter and copy down (for H2 the SUM will be 1):

=SUM(IF(ISNUMBER(1*TRIM(MID(SUBSTITUTE(" "&H2," ",REPT(" ",999)),ROW(INDIRECT("1:"&LEN(H2)))*999,999))),1*TRIM(MID(SUBSTITUTE(" "&H2," ",REPT(" ",999)),ROW(INDIRECT("1:"&LEN(H2)))*999,999)),0))

 

[hiker95]

István Hirsch,

Nicely done - one for my archives - thanks.

 

[TheAverageMan]

Thanks Hirsch, I already changed my I2 to have calories in it, and the H2 also.

One thing to follow up on, I have this entire H column go for a couple hundred rows, does the macro work all the way till the end of the H column, or do I have to modify something if I reach a couple hundred days worth of calorie counting.

Thanks again.

 

[hiker95]

TheAverageMan,

One thing to follow up on, I have this entire H column go for a couple hundred rows, does the macro work all the way till the end of the H column

The macro will go from H2, all the way down to the last used cell in column H.