Calculate simple equation which is included in one cell..

Copper Contributor

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

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

 

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

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

Attached

 

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

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.

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

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