XL does an implicit conversion when you Find/Replace. There is no setting or
option to avoid it. Since they are part numbers there is no value in
converting them to numbers. I would recommend using the Substitute formula
something like this...

=SUBSTITUTE(A2, "-", "")

Where your part number is in A2.
--
HTH...

Jim Thomlinson


"Guy Kerr" wrote:

I have some part numbers (see examples below) that contain hypens that I need
to remove for approx. 800 records. If I edit the cells and remove the hypens
manually the leading zero is dropped because Excel assumes it's a number. If
i format the cell as TEXT first then repeat the process the leading zero is
retained.

Since I have so many records to remove the hypens from I wanted to use the
FIND/REPLACE menu option. When I do this I lose the leading zeros regarless
of whether or not I format the cells as TEXT beforehand.

Does anyone have any idea why this is happening? I thought it may be a bug
so I tried in both Excel 2003 and 2007 with the same results. I also thought
that maybe there is some hidden formatting in the spreadsheet that may be
tripping me up (I've seen this before in Excel) so I copied and PASTE AS
VALUES the data to a new Excel file with the same results.

Any help would be much appreciated.

Guy

022491806-0101-030
033547786-0101-019
014186194-0101-079
010217379-0101-150
028456385-0101-027
014259858-0101-063
009833612-0101-085