Auto-suggest helps you quickly narrow down your search results by suggesting possible matches as you type.

- Home
- :
- Microsoft Excel
- :
- Excel
- :
- Calculate simple equation which is included in one cell..

- Subscribe to RSS Feed
- Mark Discussion as New
- Mark Discussion as Read
- Pin this Discussion for Current User
- Bookmark
- Subscribe
- Printer Friendly Page

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Email to a Friend
- Report Inappropriate Content

Jan 25 2017 06:27 AM

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 .

Labels:

9 Replies

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Email to a Friend
- Report Inappropriate Content

Jan 25 2017 07:11 AM

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

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Email to a Friend
- Report Inappropriate Content

Jan 25 2017 11:02 AM - edited Jan 25 2017 11:03 AM

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

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Email to a Friend
- Report Inappropriate Content

Jan 25 2017 11:06 AM

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

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Email to a Friend
- Report Inappropriate Content

Jan 29 2017 10:32 PM

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

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Email to a Friend
- Report Inappropriate Content

Jan 30 2017 12:19 AM

Attached

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Email to a Friend
- Report Inappropriate Content

Jan 30 2017 03:41 AM

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

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Email to a Friend
- Report Inappropriate Content

Jan 30 2017 04:00 AM

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.

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Email to a Friend
- Report Inappropriate Content

Jan 30 2017 04:39 AM

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

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Email to a Friend
- Report Inappropriate Content

Jan 30 2017 04:46 AM

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