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.
Best Office Productivity Tools
Supercharge Your Excel Skills with Kutools for Excel, and Experience Efficiency Like Never Before. Kutools for Excel Offers Over 300 Advanced Features to Boost Productivity and Save Time. Click Here to Get The Feature You Need The Most...
Office Tab Brings Tabbed interface to Office, and Make Your Work Much Easier
- Enable tabbed editing and reading in Word, Excel, PowerPoint, Publisher, Access, Visio and Project.
- Open and create multiple documents in new tabs of the same window, rather than in new windows.
- Increases your productivity by 50%, and reduces hundreds of mouse clicks for you every day!