# Need Formula for Adding Numbers with Words from the Same Cell

#### TheAverageMan

##### New Member

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.

### Excel Facts

Can you sort left to right?
To sort left-to-right, use the Sort dialog box. Click Options. Choose "Sort left to right"
TheAverageMan,

Welcome to the MrExcel forum.

Sample raw data:

Excel 2007
HI
1Food eatenEstimated Calories
2120 cereal, 240 milk, black coffee, 200 protein bar, 120 jerkey, 360 chicken, 120 bbq
3
Sheet1

After the macro:

Excel 2007
HI
1Food eatenEstimated Calories
2120 cereal, 240 milk, black coffee, 200 protein bar, 120 jerkey, 360 chicken, 120 bbq1160
3
Sheet1
Cell Formulas
RangeFormula
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
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.

Code:
``````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
' =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.

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

TheAverageMan,

Thanks for the feedback.

You are very welcome.

Hope to hear back from you later.

It works. Thanks a lot man.

TheAverageMan,

Thanks for the feedback.

You are very welcome. Glad I could help.

And, come back anytime.

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))

István Hirsch,

Nicely done - one for my archives - thanks.

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.

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.

Replies
1
Views
110
Replies
7
Views
203
Replies
3
Views
134
Replies
2
Views
116
Replies
10
Views
323

1,219,915
Messages
6,150,939
Members
450,996
Latest member
darko1515s

### We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.

### Which adblocker are you using?

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

### Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

### Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back