When I pull data from my ADP system it puts several dollar amounts into one cell. Example: 20.00 5.00 10.00 All in one cell, in a stack like this. I need a way to add each cell of numbers into one number. When I try to view the numbers in the formula bar it only shows one number at a time and I have to scroll down in the formula bar to see the next number. Somehow I need to add all the numbers as a total single number. Usually I will have upwards of 250 rows of numbers to add together as stated above. Any help would be good. I can send an example of spreadsheet if needed.
To total the values in the cell with line feeds, a modified version of the VBA macro can be used which runs on all selected cells.
VBA Code:
Sub LinesToTotal() Dim cr, ac As Variant Dim i As Integer Dim t As Single On Error Resume Next For Each cr In Selection t = 0 ac = Split(cr.Text, Chr(10)) For i = LBound(ac) To UBound(ac) t = t + Trim(ac(i)) Next cr.Formula = t Next End Sub
This is caused by line feeds within the source data that are imported as text. To avoid this, try to use alternative import options or if using a paste function then Paste Special > Unicode Text. If this is not possible then a VBA macro can split the lines feeds into separate cells. The following VBA sub routine will split values for a selected range of cells in a column to continuous rows in the adjacent column to the right.
VBA Code:
Sub LinesToCells() Dim cr, ac As Variant Dim r, c, i As Integer r = ActiveCell.Row c = ActiveCell.Column + 1 For Each cr In Selection ac = Split(cr.Text, Chr(10)) For i = LBound(ac) To UBound(ac) Cells(r, c).Value = ac(i) r = r + 1 Next Next End Sub
