How to sum or adding numbers in a single cell in Excel?
In many cases, you may need to sum all numbers in a single cell. For example, there is a text string Class 1 Grade 2 in cell A1, and the result of adding these numbers is 1+2=3. This article will show you methods of adding numbers in a single cell with details.
You can sum numbers in a single cell with the below User-defined function.
1. Press Alt + F11 keys simultaneously to open the Microsoft Visual Basic Application window.
2. In the Microsoft Visual Basic Application window, click Insert > Module. And then copy the below VBA into the Module window.
VBA: sum numbers in a single cell
Function SumNums(pWorkRng As Range, Optional xDelim As String = " ") As Double Dim arr As Variant Dim xIndex As Long arr = Split(pWorkRng, xDelim) For xIndex = LBound(arr) To UBound(arr) Step 1 SumNums = SumNums + VBA.Val(arr(xIndex)) Next End Function
3.Press the Alt + Q keys to close the Microsoft Visual Basic Application window.
4. Select a blank cell to output the result. Enter the below formula into it and press the Enter key (A2 is the cell in which you will sum single numbers).
5. Then drag the Fill Handle of the result cell to apply the formula to other needed cells.
Note: This user-defined function does not apply to cells in number format.
Kutools for Excel’s Sum numbers in a cell formula can help to easily sum numbers in a single cell (including text format cell and number format cell) with several click only.
Before applying Kutools for Excel, please download and install it firstly.
1. Select a blank cell to output the resut. And then click Kutools > Formula Helper > Formula Helper.
2. In the Formula Helper dialog box, please configure as follows.
Tips: You can check the Filter box, type in a keyword to filter the formula you need.
3. Then the result is populated in selected cell. Select the result cell, and then drag its Fill Handle down to get the other results.
You are guest
or post as a guest, but your post won't be published automatically.
- To post as a guest, your comment is unpublished.· 1 months agoThe VBA function code works really well. Thank you.
- To post as a guest, your comment is unpublished.· 2 years agoHow do I add +1 digit to numbers in one cell (word or excell or notepad, doesn't matter). But at My work I often have: 14,17,28,31,35,38,50,53,70,73,80,83,90,93,120,123,135,138,150,153,165,168,180,183,210,213,250,253,275,278,300,303 something like that.
And I need to make them like: 15,18,29,32,36,39,51,54,71,74,81,84,91,94,121,124,136,139,151,154,166,169,181,184,211,214,251,254,276,279,301,304. I do it manually, but maybe there is a fast way to do it?
- To post as a guest, your comment is unpublished.· 2 years agoI have a list of numbers that I want to automatically add 30 to each number in each single cell. Is that possible?
- To post as a guest, your comment is unpublished.· 4 years agoYou can also very easily just type "=" in the cell. So if you had a cell that was 3 but wanted to add 1+2 it would be "=1+2" in the cell
- To post as a guest, your comment is unpublished.· 4 years agoThe problem with this tool is that it adds all the digits. The problem is 125 + 2 is not 127. The tool would add it as 1 + 2 + 5 + 2. Not sure how this is useful to someone that needs to add all the numbers. Even in the example it does not make sense or seem practical. Did I miss something?
- To post as a guest, your comment is unpublished.· 5 years agoTHANKSSSSSS SOOO MUCH THE MODULE IDEA IS AMAZING ! I NEVER KNEW IT EXISTED