Summing up figures in a SINGLE cell divided by alt-enter line-breaks (aka Char(10))

**VECT** · 03-09-2015, 07:06 PM

Hi guys,

I'm in need of a way to sum/adding up numbers in a SINGLE cell that are divided by line breaks. Example:

0
6
7
0

Those 4 numbers would be in a single cell lined up vertically. How do I write a formula that will return back to me a value of "13"? I have be wrecking my brain over this trying to find a solution to no avail so far.

And before anyone asks why are my numbers line-broke in a single cell instead of occupying separate cells, these numbers are the result of an output Excel-file from an ERP, not manually entered.

Any advice would be appreciated!

**daffodil11** · 03-09-2015, 07:48 PM

Assuming always 4 lines per cell:

Please Login or Register  to view this content.

**VECT** · 03-09-2015, 08:13 PM

Hi daffodil,

Unfortunately it's not always 4 lines. Also, your formula seem to not work if a figure has more than 1 digit; some of these figures goes up to 5 or 6 digits.

**FlameRetired** · 03-09-2015, 08:26 PM

some of these figures goes up to 5 or 6 digits.

Do you mean 5 or 6 digits between the line breaks as in

0
12
5
2
0
15848
0

Something like that?

**shg** · 03-09-2015, 08:39 PM

You could do text to columns using line feed as the delimiter, and then use a normal SUM function.

**FlameRetired** · 03-09-2015, 08:44 PM

Something like this?

Formula:

Please Login or Register  to view this content.

Array entered. Array enter means the formula must be committed by simultaneously pressing and holding down Ctrl and Shift while hitting Enter.

**Debraj Roy** · 03-09-2015, 08:51 PM

Assuming data is in A1,
in B1 use formula as

Formula:

Please Login or Register  to view this content.

Create a Name like..
Select b1, create Name EVAL, and refers to as .. =EVALUATE(Sheet1!a1)

Now in cell C1 use formula as

Formula:

Please Login or Register  to view this content.

EVALUATE.xlsm

keep in mind that its a MACRO.. not a normal Formula..

**Pete_UK** · 03-09-2015, 08:52 PM

You could set up a small UDF like this:

Please Login or Register  to view this content.

and then you would use this in a formula like this:

=eval(SUBSTITUTE(A1,CHAR(10),"+"))

assuming your string of numbers is in A1.

Hope this helps.

Pete

**FDibbins** · 03-10-2015, 01:05 AM

I was thinking along the same nines that shg was - Text2Columns, then sum

Summing up figures in a SINGLE cell divided by alt-enter line-breaks (aka Char(10))

LinkBack

Thread Tools

Rate This Thread

Display

Summing up figures in a SINGLE cell divided by alt-enter line-breaks (aka Char(10))

Re: Summing up figures in a SINGLE cell divided by alt-enter line-breaks (aka Char(10))

Re: Summing up figures in a SINGLE cell divided by alt-enter line-breaks (aka Char(10))

Re: Summing up figures in a SINGLE cell divided by alt-enter line-breaks (aka Char(10))

Re: Summing up figures in a SINGLE cell divided by alt-enter line-breaks (aka Char(10))

Re: Summing up figures in a SINGLE cell divided by alt-enter line-breaks (aka Char(10))

Re: Summing up figures in a SINGLE cell divided by alt-enter line-breaks (aka Char(10))

Re: Summing up figures in a SINGLE cell divided by alt-enter line-breaks (aka Char(10))

Re: Summing up figures in a SINGLE cell divided by alt-enter line-breaks (aka Char(10))

Thread Information

Users Browsing this Thread

Similar Threads

[SOLVED] Unable to get cell count when using line breaks char(10)

[SOLVED] Remove the "line breaks" within the cell to retain values in a single line

Formula to enter line breaks in cells when a specific charachter is entered

[SOLVED] How to enter single char data into Excel worksheet without hitting Enter

Remove Line Breaks ALT + ENTER

Bookmarks

Bookmarks

Posting Permissions