PDA

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



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

buhay
11-30-2010, 01:01 PM
I have figured it out myself just add "." to strgood

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

austenr
11-30-2010, 01:06 PM
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
11-30-2010, 01:10 PM
They're not spaces but characters with ascii 160, so
ActiveCell.Value = Replace(ActiveCell.Value, Chr(160), "")
or if there are spaces too then:
ActiveCell.Value = Trim(Replace(ActiveCell.Value, Chr(160), ""))

buhay
11-30-2010, 01:14 PM
Thank you for the quick reply. As mentioned above this code hereSub 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

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