VBA Replace "-"

[JMC57]

I need to find a way to find and repalce using VBA so that 00904-0201-61 converts to 904020161 or 12345-6789-11 converts to to 12345678911 by removing the - between then numbers.


ActiveSheet.Columns("C").NumberFormat = "@" or ActiveSheet.Columns("C").NumberFormat = "00000-0000-00"</SPAN> do not allow ther esult to be viewed as a number when doing VLOOKUP. When I use find and replace teh VLOOKUP works.

Having VBA do the find and erpalce will help expedite the formating.

Thanks
</SPAN>

 

[Biz]

Try code below

Sub Test()
With Columns("C:C")
    .Cells.Replace What:="-", Replacement:="", LookAt:=xlPart, SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, ReplaceFormat:=False
    .EntireColumn.AutoFit
End With
End Sub

 

[JMC57]

Thanks! I can sleep tonight.

 

[Biz]

No problems m8.
I know becuz I have hard time sleeping with tough vba and SQL questions too.

Biz