buhay
11-30-2010, 12:06 PM
Deleting blank spaces in front and after a number in a cell
I've been trying to delete blank spaces(the number of spaces vary in front and after the numbers) in a cell without any success.
For example (see also sample file):
----ColB ----------------- ColC
288,121.051494---------337,543,908.54
1,716.789957---------- 2,011,063.39
The number of spaces vary in front and after the numbers.
I've tried this procedure below but it doesn't work:
Public Sub RemoveSpaces()
Application.ActiveCell = Trim(Application.ActiveCell)
End Sub
I have also tried this procedure below which removes also the dots which is not my intention:
Sub getRid()
Application.ScreenUpdating = False
strgood = "0123456789abcdefghijklmnopqrstuvwxyz"
For y = 1 To 6
For x = 1 To Cells(Rows.Count, y).End(xlUp).Row
r = Cells(x, y).Value
o = ""
For z = 1 To Len(r)
m = Mid(r, z, 1)
If InStr(strgood, LCase(m)) > 0 Then o = o & m
Next z
Cells(x, y).Value = o
Next x
Next y
Application.ScreenUpdating = True
End Sub
Somebody help please
I've been trying to delete blank spaces(the number of spaces vary in front and after the numbers) in a cell without any success.
For example (see also sample file):
----ColB ----------------- ColC
288,121.051494---------337,543,908.54
1,716.789957---------- 2,011,063.39
The number of spaces vary in front and after the numbers.
I've tried this procedure below but it doesn't work:
Public Sub RemoveSpaces()
Application.ActiveCell = Trim(Application.ActiveCell)
End Sub
I have also tried this procedure below which removes also the dots which is not my intention:
Sub getRid()
Application.ScreenUpdating = False
strgood = "0123456789abcdefghijklmnopqrstuvwxyz"
For y = 1 To 6
For x = 1 To Cells(Rows.Count, y).End(xlUp).Row
r = Cells(x, y).Value
o = ""
For z = 1 To Len(r)
m = Mid(r, z, 1)
If InStr(strgood, LCase(m)) > 0 Then o = o & m
Next z
Cells(x, y).Value = o
Next x
Next y
Application.ScreenUpdating = True
End Sub
Somebody help please