Add up all Numbers in a Cell with a Line Break?

[Willy Excel]

Sum These	Place Here:
2 1	3
	0
1 2	3
2	2

<tbody>
</tbody>

Greetings! Is there a simple method to SUM all digits within a cell with line breaks?

Some cells in the column have no numbers, some have one number, and others have a line break with a number on top and number on bottom.

Or: is there a way to highlight the cells and show "Sum:" down bottom?

Thanks!<attachment></attachment>

 

[Willy Excel]

Everybody, I have just discovered a major wrinkle. There can actually be multiple line breaks. So, a cell can have: no data, two rows, three rows, or four rows. How could we update the formulas for this more complex scenario?

Data			SUM
1 2 3			6
			0
9			9
			0
3 1			4
			0
4 2 3 7			16

<tbody>
</tbody>

 

[TonyUK72]

Where is your raw data coming from, I'm thinking it might be more appropriate to CLEAN the data first?

Another alternative is to use the inbuilt feature "TEXT TO COLUMNS" which can be found on the DATA TAB

Is you 1st data entry is in cell A2, then the output for "TEXT TO COLUMNS" would be B2, C2, D2 and E2 based on the fact some of your cells contain UPTO 4 numbers.

F2 would now become a simple SUM() of B2, C2, D2 and E2 and contain your answer.

 

[Willy Excel]

It is coming from a website related to my job that exports to excel and that is how it exports it. I could manually do it in a new column, but a formula would be terrific.

 

[TonyUK72]

Where is your raw data coming from, I'm thinking it might be more appropriate to CLEAN the data first?

Another alternative is to use the inbuilt feature "TEXT TO COLUMNS" which can be found on the DATA TAB

Is you 1st data entry is in cell A2, then the output for "TEXT TO COLUMNS" would be B2, C2, D2 and E2 based on the fact some of your cells contain UPTO 4 numbers.

F2 would now become a simple SUM() of B2, C2, D2 and E2 and contain your answer.

 

[FormR]

No luck with that latest formula.

Hi, "No Luck" doesn't give us much to work on to help you trouble shoot.

Your previous one works perfectly, however.

Everybody, I have just discovered a major wrinkle. There can actually be multiple line breaks.

If you know what the maximum number is then you can extend my suggestion as below, for say a maximum of 5 line breaks. If the maximum is truly unknown then we can adapt the formula to be dynamic.

=SUMPRODUCT(0+(0&TRIM(MID(SUBSTITUTE(A2,CHAR(13),REPT(" ",99)),{1,2,3,4,5}*99-98,99))))

 

[inactiveuserps07]

I say go with FormR's formula, adapted for either CHAR(13) or CHAR(10) and accounting for up to 10 line breaks. Why not?


=SUMPRODUCT(--(0&TRIM(MID(SUBSTITUTE(SUBSTITUTE(A1,CHAR(13),CHAR(10)),CHAR(10),REPT(" ",99)),{1,2,3,4,5,6,7,8,9,10}*99-98,99))))

 

[Willy Excel]

Where is your raw data coming from, I'm thinking it might be more appropriate to CLEAN the data first?

Another alternative is to use the inbuilt feature "TEXT TO COLUMNS" which can be found on the DATA TAB

Is you 1st data entry is in cell A2, then the output for "TEXT TO COLUMNS" would be B2, C2, D2 and E2 based on the fact some of your cells contain UPTO 4 numbers.

F2 would now become a simple SUM() of B2, C2, D2 and E2 and contain your answer.

Thanks, Tony. I wouldn't have thought of that approach. A formula would be better, however, since spreadsheet needs one cell in a nearby column.

 

[Willy Excel]

Hi, "No Luck" doesn't give us much to work on to help you trouble shoot.

Thanks for your help again, FormR. From your:
=SUMPRODUCT(0+(0&TRIM(MID(SUBSTITUTE(SUBSTITUTE(A2,CHAR(10),CHAR(13)),CHAR(13),REPT(" ",99)),{1,2}*99-98,99))))

Data			SUM
1 2 3			3
			0
9			9
			0
3 1			4
			0
4 2 3 7			6

<tbody>
</tbody>

 

[Willy Excel]

If you know what the maximum number is then you can extend my suggestion as below, for say a maximum of 5 line breaks. If the maximum is truly unknown then we can adapt the formula to be dynamic.

=SUMPRODUCT(0+(0&TRIM(MID(SUBSTITUTE(A2,CHAR(13),REPT(" ",99)),{1,2,3,4,5}*99-98,99))))

Bingo! Works great.

 

[Willy Excel]

I say go with FormR's formula, adapted for either CHAR(13) or CHAR(10) and accounting for up to 10 line breaks. Why not?


=SUMPRODUCT(--(0&TRIM(MID(SUBSTITUTE(SUBSTITUTE(A1,CHAR(13),CHAR(10)),CHAR(10),REPT(" ",99)),{1,2,3,4,5,6,7,8,9,10}*99-98,99))))

Thanks, Erik. This one correctly returned:

Data			SUM
1 2 3			6
			0
9			9
			0
3 1			4
			0
4 2 3 7			16

<tbody>
</tbody>

I see it has a mix of both char 10 and 13. It's working on my Mac. Is that designed to work on PC as well with that mix of both 10 and 13?