 # Calculate simple equation which is included in one cell..

All cells in the Column contain more than one value in each cell , that they were entered by using Alt+enter ..
I want a formula to sum the values in each Cell ..

Exmp.

the Cell A1 contains 100 200 300 , Cell A2 contains 500 300 ,Cell A3 contains 500 , Cell A4 contains 100 150 200 50 ..etc.

I need to add Column showing the total in each Cell , i.e. B1 shows 600 , B2 800 , B3 500 , B4 500 ..etc.

what is the formula I have to use in Column B .I reached to get it 100+200+300 ..etc i.e. replace the space or Alt +Enter by "+" , but I can not calculate it to get the total .

9 Replies

# Re: Calculate simple equation which is included in one cell..

Abdullah, perhaps this http://stackoverflow.com/questions/25316094/split-a-string-cell-in-excel-without-vba-e-g-for-array-f... idea with array will help, but i dind't try that myself

# Re: Calculate simple equation which is included in one cell..

Played a bit taken the approach from here https://www.mrexcel.com/forum/excel-questions/505622-text-numbers-same-cell-sum-numbers.html

For simplicity let assume your numbers are split by spaces, not by ALT+ENTER.

In A2 you have "200 3 4 6 2"

When

=SUMPRODUCT(
IF(
SUBSTITUTE(TRIM(MID(SUBSTITUTE(\$A2," ",REPT(" ",10)),{1,2,3,4,5,6,7,8}*10-9,10)),",","")="",
0,
SUBSTITUTE(TRIM(MID(SUBSTITUTE(\$A2," ",REPT(" ",10)),{1,2,3,4,5,6,7,8}*10-9,10)),",","")*1)
)

gives 215.

Here 10 in REPT is any number which is more than length of your biggets number

Array {1,2,..} is more than biggest number of numbers in your cells

First SUBSTITUTES compares with IF set extra members of array to 0 (othervise you'll have an error)

*1 close to end converts string array with numbers into the numeric array

You may use Evaluate Formula in Ribbon to check how it works.

And i guess you may use directly ALT+ENTER symbol in final formula

# Re: Calculate simple equation which is included in one cell..

Forgot to say that's not an array formula, i.e. without ctrl+shift+enter

# Re: Calculate simple equation which is included in one cell..

thank you very much , could you please send me it in Excel ..

Attached

# Re: Calculate simple equation which is included in one cell..

It is not working fine , I tried different values like 100 1600 2000 in the cell it gave me value 1900 ..

# Re: Calculate simple equation which is included in one cell..

Please expand your range in formula, e.g. use 100 instead of 10, like

...,REPT(" ",100)),{1,2,3,4,5,6,7,8}*100-99,100))

instead of

...,REPT(" ",10)),{1,2,3,4,5,6,7,8}*10-9,10))

When you'll have 3700 for "100 1600 2000". This magic 100 (or whatever) shall be more than maximum number of characters in any of your cells.

# Re: Calculate simple equation which is included in one cell..

Hi Abdullah,

If you have access to Power Query then that can also provide a possible solution

Power Query can be used to split cells containing Alt+Enter  and then a few extra Power Query steps allows you to sum those figures.  I've attached a demo file

# Re: Calculate simple equation which is included in one cell..

I agree with Wyn, with Power Query it's easier - if you use it