REPLACE command drops leading zeros
 

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

Answer: REPLACE command drops leading zeros
 

Hi Guy,

I understand your frustration with losing the leading zeros when using the FIND/REPLACE command in Excel. This is a common issue that many users face when working with part numbers or other alphanumeric data.

One solution to this problem is to use a formula instead of the FIND/REPLACE command. Here's how you can do it:

1. Insert a new column next to the column with the part numbers.
2. In the first cell of the new column, enter the following formula:
   Formula:

   ---

   =TEXT(A1,"000000000000000") 


   ---

   This formula will convert the part number to text format and add leading zeros to ensure that all part numbers have the same number of digits.
3. Copy the formula down to all the cells in the new column.
4. Select the entire new column and copy it.
5. Right-click on the original column with the part numbers and select "Paste Special".
6. In the "Paste Special" dialog box, select "Values" and click "OK".
   This will replace the original part numbers with the new formatted part numbers, including the leading zeros.

I hope this solution works for you. Let me know if you have any other questions or concerns.

Best regards,
[Your Name]

REPLACE command drops leading zeros
 

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

REPLACE command drops leading zeros
 

Guy one solution copy this formula into a helper column and drag down.
Then Copy column Paste Special Values
Hope this helps


"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

REPLACE command drops leading zeros
 

Check your other post.

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

--

Dave Peterson

REPLACE command drops leading zeros
 

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.

"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