Missing zeros (including all trailing and inbetween zeros)

**mecham** · 04-05-2012, 06:18 PM

Data below on the left is what I want, but Data on the right is what I'm getting.

Any ideas why this is happening?

0
0
0
12976 12976
16791 16791
12362 12362
15580 1558
14255 14255
17002 172
10728 1728
15477 15477
19931 19931
19684 19684
15668 15668
11089 1189
8005 85
19372 19372
19370 1937
18694 18694
15982 15982
17989 17989
19643 19643

**Paul** · 04-05-2012, 06:23 PM

Hi Mecham, welcome to the forum.

Can you post a copy of the workbook in which you're seeing this occur? Perhaps you have an event macro that is adjusting your values, or an incorrect function somewhere.

**FDibbins** · 04-05-2012, 06:24 PM

what are you doing when you get this result?

**mecham** · 04-09-2012, 01:20 PM

I was not involved with handling of the two xls filese, but have been given them to figure out what happened.

The info in these two files is sensitive, but if you like I could share the original files stripped of everything but the column of data that has the zeros that are being stripped out. Is that helpful?

I have verified that there are no macros listed under View>Macros for either file (both xls files). I don't know if there was some intermediary file.

I should also add that the individual who handled these files has performed this same process dozens of times every year for the last 10 years, and has never encountered this issue. The process is simple - to take a client file, usually an xls, and convert it to an xls file that has the correct data headers for uploading to our database. This process usually requires little more than a lot of copying/pasting.

The fascinating thing is that there are other columns with dates, phone numbers, IDs, Zips, and other numbers that retained all their zeros. So there was only a single column affected!?!?!?

**mecham** · 04-09-2012, 01:52 PM

I believe I just recreated the issue.

If you use the"Replace All" function to replace cells that have a zero, with a blank, it will simultaneously strip out all zeros in all other numbers. I was surprised when this happened because I thought that "replace all" would look for those cells with the value of "0" and replace onlythose cells with a blank value. It turns out that it does do that, but it also strips out all zeros in all other numbers in the column, whether they are leading, trailing, or in the middle.

**Paul** · 04-09-2012, 02:28 PM

In the Find/Replace dialog window, if you type 0 into the "Find What" box, then click the Options button and select "Match entire cell contents" it will only replace the cells whose entire content is 0. It won't replace the 0's in 102, 2200, etc.