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.
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
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
