REPLACE command drops leading zeros

  • Thread starter Thread starter Guy Kerr
  • Start date Start date
G

Guy Kerr

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
 
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.
 
Guy one solution copy this formula into a helper column and drag down.
Then Copy column > Paste Special > Values
Hope this helps
 
Check your other post.

Guy said:
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
Click to expand...
 
Thank you both. The Substitute command worked beautifully. I don't know why
I didn't think of this. I got so caught up with trying to figure out why the
FIND/REPLACE wasn't working that I didn't look for a better solution.

Thanks again.
 
Back
Top