Solved: deleting an asterik

[vzachin]

hi
i'm having problem with deleting an asterik in a field:
cl.Replace what:="~*", Replacement:=""

for the most part it works however if i have a field with all zeros (0), it removes the asterik and retains one zero. i need to retain all the zeros.
the following code removes spaces & asteriks. how can i modify the coding to retain the zeros?


Sub RemoveSpaces()
Dim cl As Variant
LastRow = Cells(Rows.Count, "A").End(xlUp).Row
Set rng = Range("A5:Z5").Resize(LastRow - 4).SpecialCells(xlCellTypeVisible)
If Not rng Is Nothing Then
For Each cl In rng
If Len(cl) > Len(WorksheetFunction.Trim(cl)) Then
cl.Replace what:="~*", Replacement:=""
cl.Value = WorksheetFunction.Trim(cl)
End If
Next cl
End If
End Sub


thanks
zach

[Bob Phillips]

That is presumably because once you remove the *, Excel sees it as a number, and it shows 0 as 0, that is a single digit.

[vzachin]

hi xld,
i tried formatting the fields as text but i got the same results. is there any way around this?

thanks
zach

[Bob Phillips]

You could format the field as say 000000, but other than that, the only thing I can think of is to add a ' at the start of the field before you do the replace, but that will affect other stuff

[vzachin]

see http://www.vbaexpress.com/forum/showthread.php?p=148727#post148727
for solution