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.

Sum numbers in a single cell with User-defined function
Easily sum numbers in a single cell with only several clicks


Sum numbers in a single cell with User-defined function

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

=SUMNUMS(A2)

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.


Easily sum numbers in a single cell with only several clicks

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.

2.1) Find and select Sum numbers in a cell in the Choose a formula box;
Tips: You can check the Filter box, type in a keyword to filter the formula you need.
2.2) In the Cell box, specify a cell which contains the numbers you will sum;
2.3) Click the OK button. See screenshot:

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.

  If you want to have a free trial ( 30-day) of this utility, please click to download it, and then go to apply the operation according above steps.


Easily sum numbers in a single cell with Kutools for Excel


The Best Office Productivity Tools

Kutools for Excel Solves Most of Your Problems, and Increases Your Productivity by 80%

  • Reuse: Quickly insert complex formulas, charts and anything that you have used before; Encrypt Cells with password; Create Mailing List and send emails...
  • Super Formula Bar (easily edit multiple lines of text and formula); Reading Layout (easily read and edit large numbers of cells); Paste to Filtered Range...
  • Merge Cells/Rows/Columns without losing Data; Split Cells Content; Combine Duplicate Rows/Columns... Prevent Duplicate Cells; Compare Ranges...
  • Select Duplicate or Unique Rows; Select Blank Rows (all cells are empty); Super Find and Fuzzy Find in Many Workbooks; Random Select...
  • Exact Copy Multiple Cells without changing formula reference; Auto Create References to Multiple Sheets; Insert Bullets, Check Boxes and more...
  • Extract Text, Add Text, Remove by Position, Remove Space; Create and Print Paging Subtotals; Convert Between Cells Content and Comments...
  • Super Filter (save and apply filter schemes to other sheets); Advanced Sort by month/week/day, frequency and more; Special Filter by bold, italic...
  • Combine Workbooks and WorkSheets; Merge Tables based on key columns; Split Data into Multiple Sheets; Batch Convert xls, xlsx and PDF...
  • More than 300 powerful features. Supports Office/Excel 2007-2019 and 365. Supports all languages. Easy deploying in your enterprise or organization. Full features 30-day free trial. 60-day money back guarantee.
kte tab 201905

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!
officetab bottom
Say something here...
symbols left.
You are guest ( Sign Up? )
or post as a guest, but your post won't be published automatically.
Loading comment... The comment will be refreshed after 00:00.
  • To post as a guest, your comment is unpublished.
    Migle · 11 months ago
    How 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.
    Louanne Zettler · 1 years ago
    I 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.
    Em · 2 years ago
    You 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.
    Dan · 3 years ago
    The 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.
    Omar · 3 years ago
    THANKSSSSSS SOOO MUCH THE MODULE IDEA IS AMAZING ! I NEVER KNEW IT EXISTED