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 may interest in:
Excel Productivity Tools
Office Tab: Bring powerful tabs to Office (include Excel), just like Chrome, Safari, Firefox and Internet Explorer. Save you half the time, and reduce thousands of mouse clicks for you. 30-day Unlimited Free Trial
Kutools for Excel: Save 70% of your time and solve 80% Excel problems for you. 300+ advanced features designed for 1500+ work scenario, make Excel much easy and increase productivity immediately. 60-day Unlimited Free Trial
You can sum numbers in a single cell with 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 and paste below code 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. After pasting the code, press Alt + Q keys to close the Microsoft Visual Basic Application window.
4. Select a blank cell adjacent to the cell you are going to sum numbers inside, type formula =SUMNUMS(A1) into the Formula Bar, and then press the Enter key.
5. After getting the result of the first cell, if your cells are continuous, please drag the Fill Handle to the cell for covering this formula.
Note: This user-defined function cannot take effect for number format cells.
Kutools for Excel’s Commonly-used Formulas function collects the formula of adding numbers in a single cell. You can easily sum numbers in a single cell (including text format cell and number format cell) without remembering any formula.
1. Select a blank cell for locating the summing result. And then click Kutools > Formula Helper > Sum numbers in a cell. See screenshot:
2. In the Formula Helper dialog box, enter the cell address which contains the numbers you want to sum in the Cell box, or click the button to select the cell directly, and then click the OK button, see screenshot:
3. Then you get the result of the specified cell. Drag the Fill Handle down to the cell for getting all results.
Kutools for Excel includes more than 300 handy Excel tools. Free to try with no limitation in 60 days. Download the free trial now!
Excel Productivity Tools
You are guest ( Sign Up? )
or post as a guest, but your post won't be published automatically.
To post as a guest, your comment is unpublished.· 1 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.· 1 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.· 2 years agoTHANKSSSSSS SOOO MUCH THE MODULE IDEA IS AMAZING ! I NEVER KNEW IT EXISTED