How to find errors in documents

[Pnoahjones]

when you have a series of data and you have a space or error in your numbers
when you are using functions and formulas. how do you find where there error
is in the data if you cant see the space or cant see where the error might be
that is causing the formula answer to be wrong.

 

[macropod]

Hi Pnoahjones,

Numbers with spaces are treated by Excel as text. Provided you're using the default cell format, rather than left-aligned, the text
strings will be right-aligned.

You can clean up a selected range, or a whole worksheet using the macro below:
Sub TrimRange()
Dim SBar As Boolean, Cell As Range, CellCount As Long, I As Long
SBar = Application.DisplayStatusBar
Application.DisplayStatusBar = True
Application.ScreenUpdating = False
Application.Calculation = xlManual
On Error Resume Next
I = 0
If Selection.Rows.Count * Selection.Columns.Count > 1 Then
CellCount = Selection.Rows.Count * Selection.Columns.Count
Else
CellCount = ActiveSheet.UsedRange.Rows.Count * ActiveSheet.UsedRange.Columns.Count
End If
For Each Cell In Selection.SpecialCells(xlConstants)
Cell.Value = Application.Trim(Replace(Cell.Value, Chr(160), " "))
I = I + 1
Application.StatusBar = Int(I / CellCount * 100 + 0.5) & "% Trimmed"
Next Cell
Application.Calculation = xlAutomatic
Application.StatusBar = False
Application.DisplayStatusBar = SBar
Application.ScreenUpdating = True
MsgBox "Finished trimming " & CellCount & " cells.", 64
End Sub

The macro gives a progress report on the Excel status bar and a message box when it's finished.