From: Pnoahjones on 25 Mar 2010 00:13
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.
From: macropod on 25 Mar 2010 00:29
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:
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
CellCount = ActiveSheet.UsedRange.Rows.Count * ActiveSheet.UsedRange.Columns.Count
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"
Application.Calculation = xlAutomatic
Application.StatusBar = False
Application.DisplayStatusBar = SBar
Application.ScreenUpdating = True
MsgBox "Finished trimming " & CellCount & " cells.", 64
The macro gives a progress report on the Excel status bar and a message box when it's finished.
[Microsoft MVP - Word]
"Pnoahjones" <Pnoahjones(a)discussions.microsoft.com> wrote in message news:A2862477-CA2B-4BBE-A85B-6C967DE75D37(a)microsoft.com...
> 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.