Hi,

I have a spreadsheet that has a column of ID codes on it that all appear to
be 7 or 8 digits long. Looking at the formatting, it seems that the
formatting of all of this column is a custom format of "0000000". This means
that some of the entries which are really only 6 digits, are being preceded
with a 0 automatically, which is correct and just seems to be the way that
the person working on the spreadsheet used to get the leading zeros to
display and not be autocorrected. Also, as I mentioned, there are some 8
digit entries which dont begin with a zero, and these seem to be left alone
completely by the custom format and just display as normal despite being
overlength.

I need to use this column in a mail merge, but am obviously only getting the
'true' auto-corrected value of the cell rather than the displayed value,
which often misses the leading zero where there is one.

After looking around at the usual solutions to this on the web, i cant find
anyway of getting the displayed values with the leading zeros that were
created by the custom formatting on there. The following things didnt work:

1) copy, paste special,values - took out the leading zeros on the shorter
entries until I formatted the cells as the same custom format again, thus
giving me exactly what I started with

2) putting ' at the start of cells - i DO NOT want the cell to remain
untouched by Excel, I want the formatting to carry through to Word

3) formatting the merge field in Word - this doesnt add leading zeros, it
just adds a space when I did it

4) changing the cell format to text etc - this removes my leading zeros
which i want to remain there.

So I was wondering if there was any other way of copy pasting the actual
displayed values and retaining my leading zeros created by the custom format
WITHOUT having to use the custom format on that column too?


Very long winded, hopefully someone will understand me here, cheers, Oli