Solved: deleting the space

[Sathisc]

Hi,

I have full of data with numbers in Column A with space added in the last

1234535628136543213932180

for example in the first number after last digit 5 there is a single space added, same for all

I need a formula to the delete the space as it will be very time consuming if i delete it manually.

Thanks in advance,
Sathish

[macropod]

Hi Sathish,

Try:

Sub CleanRange()
Dim SBar As Boolean, Cell As Range, CellCount As Long, I As Long
With Application
  SBar = .DisplayStatusBar
  .DisplayStatusBar = True
  .ScreenUpdating = False
  .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 = .Trim(Replace(Replace(Cell.Value, Chr(160), ""), " ", ""))
    I = I + 1
    .StatusBar = Int(I / CellCount * 100 + 0.5) & "% Cleaned"
  Next Cell
  .Calculation = xlAutomatic
  .StatusBar = False
  .DisplayStatusBar = SBar
  .ScreenUpdating = True
End With
MsgBox "Finished cleaning " & CellCount & " cells.", 64
End Sub

If you select a range, the macro acts only on that range; other wise it acts on the whole workbook. A progress report is given on the status bar, plus a message box at the end.

[omnibuster]

Private Sub TryThis()
Dim c As Range
For Each c In Range("A1:A10")
If Len(c.Value) > 0 Then
c.Value = Val(c.Value)
End If
Next
End Sub

[Sathisc]

You guys are really great

Thank you so much