Microsoft Excel 2003 (full product)

I am trying to add multiple numbers within a cell but show the work example

A

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

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)

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.

Hi, 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:

A2400 + 20 + 1

B2=SUM(MID(A2,1,3),MID(A2,7,3),MID(A2,12,3))

B2 value421

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

Hi, This also works:

A2400 + 20 - 1

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

B2 value419Regards

Thank you for all of the responses several of the formulas work but not every time is the numbers the exact format. example

Column 1 Heat Race Points

40+20

40+20+30+10

20

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

example:

Column A

40+20+30=90Is there a formula that would total just the 90 in the next column B

90 (formula to read the sum of column A)Thanks,

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.

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

Hi, 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 71In 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 Else 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)) Else 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).

Regards

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!

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.

Hi, 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.

Regards

Hi, 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:=Sheet2!A2&"+"&Sheet2!B2&"+"&Sheet2!C2&"+"&Sheet2!D2

which looks like this20+10+5+18

In cell B2 on the 'Display' worksheet the total is shown with this formula:=SUM(Sheet2!A2:D2)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.

Regards

Ask Your Question

Weekly Poll