PDA

View Full Version : Trim bug?



paolo2504
04-28-2010, 02:26 AM
hi all,
i recently had to process feeds produced in a unix system. Data records were plenty of leading and trialing spaces so i used a simple 'TrimAll' function to clean all sheets after import. Something like this:

Private Sub TrimAll()
Dim cell As Range
UltimaRiga = xlLastRow
UltimaColonna = xlLastCol
LetteraColonna = ColumnLetter(UltimaColonna)
Range("A1:" & LetteraColonna & UltimaRiga).Select
For Each cell In Selection
cell = Application.WorksheetFunction.Trim(cell)
cell = Application.WorksheetFunction.Clean(cell)
Next
End Sub
ok, it's working well. The problem i discovered only yesterday was the routine also change ',' to '.' (depending on regional settings) in presence of numbers with decimal fraction, i.e.: 123.345,5643 becomes 123.345.5643 !!
I noticed if integer part is 0 or decimals are less than 3 no change is made (i.e. 0,12 / 1,12 are not changed).

I discovered using xlCellTypeConstants is preventing this issue:
......
Set CleanTrimRg = Selection.SpecialCells(xlCellTypeConstants, 2)
For Each oCell In CleanTrimRg
' oCell = Func.Clean(Func.Trim(oCell)) ' use this to trim leading, trailing and internal spaces
oCell = Trim(oCell) ' use this to trim only leading and trailing spaces
Next

Anybody saw this issue ? It looks to me like a big bug...