Add multiple numbers within a cell

Microsoft Excel 2003 (full product)
January 28, 2010 at 22:01:01
Specs: Windows Vista
I am trying to add multiple numbers within a cell but show the work

40+20 This column I need to add the 2
in Column A together to show the sum or total of them in
Column B
Thanks for any help

See More: Add multiple numbers within a cell

Report •

January 29, 2010 at 03:48:55
These formulae will concatenate the text sting "40 + 20 = " with the formula SUM(40+20)

The first uses the Concatenate operator "&", the other uses the function itself.

="40 + 20 = " & SUM(40+20)

=CONCATENATE("40 + 20 = ",SUM(40+20))

This formula will use the values in A1 (40) and A2 (20) to produce the same results:

=A1&" + "&A2&" = "&SUM(A1:A2)

Report •

January 29, 2010 at 04:41:57
In addition, if you don't want to hard code the arithmetic operator into the formula, you could put that in a cell also.

A1 = 40
A2 = 20
A3 = + or * or - or /

=A1&A3&A2&" = "&IF(A3="+",SUM(A1,A2),IF(A3="*",A1*A2,IF(A3="-",A1-A2,IF(A3="/",A1/A2,""))))

You could take this farther with more IF's for more operators or a VLOOKUP table for even more functions.

Report •

January 29, 2010 at 05:10:00

Here is another approach:

If your numbers in column A are always in the same position in the text string, e.g., always pad numbers with spaces so that they are say three characters long, use the mid function to retrieve each number.
SUM() can take a series of values instead of a range:

400 +  20 +  1


B2 value

Each MID() function pulls three characters from the text string and SUM() treats the three characters as a number and in this example adds three, 3-character text strings.


Report •

Related Solutions

January 29, 2010 at 05:13:25

This also works:

400 +  20 -  1

=MID(A2,1,3) +MID(A2,7,3)-MID(A2,12,3)

B2 value


Report •

January 29, 2010 at 07:39:16
Thank you for all of the responses several of the formulas work but not every time is the numbers the exact format.


Column 1 Heat Race Points

I need for the next Column to total these no matter if it is 1 number or 2 numbers or 3 numbers etc.

It can even do the following as long as I can total the last number that is the sum of the strings


Column A

Is there a formula that would total just the 90 in the next column B
90 (formula to read the sum of column A)


Report •

January 29, 2010 at 08:32:40
Does the data have to remain as 40+20+30+10 in a single cell?

If the operator is always a + sign, why not separate the data into columns via:

Data...Text to columns....Delimited...Other: +

If your max number of values is 4, you could then put your SUM formulae in column E and see all of your answers there.

Report •

January 29, 2010 at 09:06:16
I need the data all in 1 cell to show the points they receive for each heat and then a total of them in another cell.

I dont have to use a + sign if there is another way I am not that familat with text to columns.

The most important part is that all of the numbers showing their heats from left to right are in 1 cell but I need to total them in another. I hope that makes sense

Report •

January 29, 2010 at 09:37:46

Another solution is a custom function.

This is a function, like ordinary Excel functions, but it does something specific.

This custom function will return the value (sum) for a series of numbers as text in a cell. It will cope with different numbers of numbers in the cell, such as 2+20 or 10+20+30+40+50

If cell E2 contains 40+20+9+2
This goes in another cell =TextMath(E2)
and shows 71

In your Workbook, click Alt+f11 (Alt key plus function key 11 at the same time).
This opens the visual basic window.
Look on the left in the Project Explorer window, look for the name of your Workbook. If your workbook is Scores.xls it will be VBAProject(Scores.xls).
Right-click on the name and select Insert and select Module (not class module).
A new item named Module1 will appear under Modules.
Double click on Module1
In the large window on the right, paste the following into the window.

Option Explicit
Function TextMath(Cell As Range) As Variant
'function to perform addition or subtraction on a cell containing text
Dim strCell As String
Dim strChar As String
Dim intOps As Integer
Dim varMathArry() As Variant
Dim n As Integer

'get the text
strCell = Cell.Value

'set math operator count to zero
intOps = 0

'find how many operators
For n = 1 To Len(strCell)
    strChar = Mid(strCell, n, 1)
    If strChar = "+" Or strChar = "-" Then
    intOps = intOps + 1
    End If
Next n

'resize array to hold operators and values
ReDim varMathArry(intOps * 2, 1)

'reset intOps
intOps = 0

'get values and text
For n = 1 To Len(strCell)
    strChar = Mid(strCell, n, 1)
    If strChar = "+" Or strChar = "-" Then
        intOps = intOps + 1
        varMathArry(intOps, 0) = strChar
        varMathArry(intOps, 1) = True
        intOps = intOps + 1
        varMathArry(intOps, 0) = varMathArry(intOps, 0) + strChar
        varMathArry(intOps, 1) = False
    End If
Next n

'get first 'value' - convert text to a double value
TextMath = CDbl(varMathArry(0, 0))

'add or subtract values
For intOps = 1 To UBound(varMathArry, 1)
    If varMathArry(intOps, 1) = True Then
        'do a math operation
        If varMathArry(intOps, 0) = "+" Then
            TextMath = TextMath + CDbl(varMathArry(intOps + 1, 0))
            TextMath = TextMath - CDbl(varMathArry(intOps + 1, 0))
        End If
        'jump over next array content
        intOps = intOps + 1
    End If
Next intOps
End Function

From the Visual Basic window select File - Save.

Use Alt + f11 to go back to the main Excel window

Enter the name of the custom function in a cell and enter the appropriate cell address =TextMath(E2).

PS I haven't added any error checks into this - so if the text isn't in the format expected it just returns an error.
The function also handles subtraction - but not (yet) multiplication or division!

Report •

January 29, 2010 at 09:59:56
re: I need the data all in 1 cell to show the points they receive for each heat and then a total of them in another cell.

I know what you are asking for, but I have to ask: Why?

Why do all of the points have to be in one cell?

Why can't you have a column for each heat with a single value in each cell?

By limiting yourself to one cell, you are making some very simple calculations troublesome.

By using one cell for each heat, you can get your answer very easily as well as do all sorts of data manipulation and analysis.

What was the average points awarded in Heat 1?

Where did Player X rank in Heat 2?

What does the data look like when sorted heat by heat?

Does Player X consistantly score higher in the early heats or the later heats? (The answer could be used to modify the player's training regimen, etc.)

It just seem rather limiting (and burdensome) to layout your data as you are.

If that is how the data is given to you, then look up Text To Columns in the Excel Help files or follow the steps I offered in Response 6 to separate the data into columns.

Report •

January 30, 2010 at 04:12:50

If your requirement is to have all results in one cell, could you instead have what looks like one cell, but is actually several cells.

Have scores and + signs in a series of cells.
Resize the columns making alternate columns just wide enough for the + signs and the columns with numbers, just wide enough for the largest number.
Highlight all the cells and using Format Cells... Borders, Replace the vertical lines between cells with white lines so that they are invisible.
Retain the horizontal lines and an outside border.

The SUM function in the next column will work as normal, and the scores will appear to be in single cells.

To enter + in a cell on its own precede it by a single quote '

As DerbyDad03 said, you will be able to use the numbers for other functions such as averages, maximums etc.


Report •

January 30, 2010 at 04:25:05

One other idea:

Place scores on one worksheet using standard numbers in single cells.
On a second 'Display' worksheet used for displaying results create the single cell displays.

If Sheet2 has this

	A	B	C	D
row 2	20	10	5	18

In cell A2 on the 'Display' worksheet enter this:

which looks like this

In cell B2 on the 'Display' worksheet the total is shown with this formula:

Now you have the best of both worlds - results displayed in one cell and real numbers that you can use for the total and any other calculations.


Report •

Ask Question