Keeping leading zero when using Find and Replace in a macro

Question

Hello,

I have data in a column as listed below;

002 (xxxx......

2.0.03 (xxx.....

I am using the following to remove all data one character to the left of the parentheses and all data to the right.

Range("F1").Select

    Range(Selection, Selection.End(xlDown)).Select

    Selection.Replace What:=" (*", Replacement:="", LookAt:=xlPart, _

        SearchOrder:=xlByColumns, MatchCase:=False, SearchFormat:=False, _

        ReplaceFormat:=False

How ever I loose the leading zeros in 002 and would prefer to keep them.  Any suggestion on what I can do?

Thanks in advance.

Answer 1

This will force the values to be treated as strings:

Sub TestMacro1()

    Dim r As Range

    Dim c As Range

    Set r = Range(Range("F1"), Range("F1").End(xlDown))

    r.NumberFormat = "@"

    For Each c In r

        c.Value = "'" & c.Text

    Next c

    r.Replace What:=" (*", Replacement:="", LookAt:=xlPart, _

    SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _

    ReplaceFormat:=False

End Sub

Answer 2

This worked great, thanks