how to paste values created by custom format?

[oli merge]

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

 

[Jan Karel Pieterse]

Hi Oli,

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?

If you don't mind running a macro that changes all entries permanently to text:

Sub Convert2Text()
Dim oCell As Range
For Each oCell In Selection
oCell.Value = "'" & oCell.Text
oCell.NumberFormat = "@"
Next
End Sub

Paste this in a normal module, select your column and run the macro.

Regards,

Jan Karel Pieterse
Excel MVP
http://www.jkp-ads.com
Member of:
Professional Office Developer Association
www.proofficedev.com

 

[Dave Peterson]

I think you should be able to use that #3 in your list.

Debra Dalgleish posted this:

There's an article on the Microsoft web site that might help you:

Answer Box: Numbers don't merge right in Word
http://office.microsoft.com/en-ca/assistance/HA011164951033.aspx

And if you prefer the old Mail Merge helper, Word MVP Suzanne Barnhill
has instructions here:

http://sbarnhill.mvps.org/WordFAQs/CustomizingWord2002.htm

about half way down the page.

 

[oli merge]

Hi,

This suprised me by actually keeping the leading zeros when just manually
changing the column to text didnt, however, now the mail merge has decided
that all the values in the column are zero. Hmm not good, any suggestions?

 

[oli merge]

sorry mate, no good, these are all the things i tried first before posting
here.

as I said, you cant format it in word (well i couldnt anyway) because Word
will put blank spaces at the front instead of zeros unlike excel in this
instance.

 

[Jan Karel Pieterse]

Hi Oli,

now the mail merge has decided
that all the values in the column are zero. Hmm not good, any suggestions?

Can you change the data type in the mail merge to text? (sorry, no Word
expert here<g>)

Regards,

Jan Karel Pieterse
Excel MVP
http://www.jkp-ads.com
Member of:
Professional Office Developer Association
www.proofficedev.com

 

[oli merge]

Can you change the data type in the mail merge to text? (sorry, no Word
expert here<g>)

Hi again, after playing around it seems you can change the formatting type
in word kind of (using "\#" in the mergefield to make it into a number) but
then it seems to want me to specify the maximum number of digits. This is a
problem since they are either 7 or 8 digits, and the 7 digit ones will end up
with a blank space preceding it to make it up to the 8 digits.

Im also assuming that the 8 digit ones would be cutoff if I specified 7
digits maximum. I cant test it out because now word has lost its link to the
excel data file that i converted to text and wont reconnect.

Aaaaah Microsoft, please sort out your Mail Merge problems, it has silly
amount of bugs in it. I will personally buy Office 2007 or whatever for
work if you will just write a program that works properly!

 

[David Biddulph]

=TEXT(A1,"0000000")

 

[Dave Peterson]

Maybe it's time to share your MSWord formatting strings in an MSWord newsgroup.

And include your rules--how to know if a number should be treated as 7 or 8
digits.

 

[oli merge]

THATS IT! Well I think so anyway, I couldnt find the file again to test it so
I made a mock up and it seemd to work on that one.