Solved: Deleting blank spaces in front and after a number in a cell

[buhay]

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:

[VBA]Public Sub RemoveSpaces()

Application.ActiveCell = Trim(Application.ActiveCell)

End Sub[/VBA]

I have also tried this procedure below which removes also the dots which is not my intention:

[VBA]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[/VBA]

Somebody help please

[buhay]

I have figured it out myself just add "." to strgood

[VBA]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 [/VBA]

[austenr]

You might try in an empty column =SUBSTITUTE(B1,"",""). However looking at your workbook and trying that didn't produce the expected results. So I am thinking you have hidden characters not spaces in front and or after your string.

[p45cal]

They're not spaces but characters with ascii 160, so
[VBA]ActiveCell.Value = Replace(ActiveCell.Value, Chr(160), "")[/VBA]
or if there are spaces too then:
[VBA]ActiveCell.Value = Trim(Replace(ActiveCell.Value, Chr(160), ""))[/VBA]

[buhay]

Thank you for the quick reply. As mentioned above this code here[VBA]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 [/VBA]

works. All I had to do is to include the dot "." in
[VBA]strgood = "0123456789abcdefghijklmnopqrstuvwxyz." [/VBA]