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>

 

[FormR]

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?

It's essentially the same as I posted way back in post# 17 - that apparently you were not having much luck with.

 

[Willy Excel]

It's essentially the same as I posted way back in post# 17 - that apparently you were not having much luck with.

Gotcha. If it works on a PC as well (will try at work), then it's officially working on both Mac and PC and is the perfect solution.

 

[inactiveuserps07]

Yes, Willy, the formula I adapted from FormR's excellent approach should work without alteration on Mac or PC, so that you can send it to other users who may be on one or the other.

 

[Willy Excel]

Yes, Willy, the formula I adapted from FormR's excellent approach should work without alteration on Mac or PC, so that you can send it to other users who may be on one or the other.

Fantastic! I've just tested it on both a Mac and PC and it did work for me.

Officially solved!

Much appreciated, everybody.

 

[inactiveuserps07]

Thanks for letting us know, Willy. Glad it worked out.

 

[Sontauzo]

I have the same situation as the original poster, but i was wondering if it is possible to do this with a macro? I would like to run the macro and have the sums replace the original content of the cell. Is that even possible?

 

[Rick Rothstein]

I have the same situation as the original poster, but i was wondering if it is possible to do this with a macro? I would like to run the macro and have the sums replace the original content of the cell. Is that even possible?

Give this macro a try...

[table="width: 500"]
[tr]
	[td]Sub TotalNumbersInCellDelineatedByLineFeeds()
  Dim Cell As Range
  For Each Cell In Range("A1", Cells(Rows.Count, "A").End(xlUp))
    Cell.Offset(, 1).Value = Evaluate(Replace(Replace(Replace(Cell.Value, vbCr, vbLf), vbLf & vbLf, vbLf), vbLf, "+") & "+0")
  Next
End Sub[/td]
[/tr]
[/table]

 

[Sontauzo]

Thanks so much!!! That was exactly what i was looking for and it works perfectly.