G
Guest
Once cells are formatted for zip codes, how do you get the zero as a first digit to be copied into a Word document mail merge?
You are using an out of date browser. It may not display this or other websites correctly.
You should upgrade or use an alternative browser.
You should upgrade or use an alternative browser.
G
Guest
Seems like there should be a simpler way to do this, but this works
Directly before the Zip Field, Insert a Word Field--the Field you want is If...Then...Else
Set it so that if the Zip Field is less than or equal to 9999, it will insert a 0. Leave the Otherwise part blank
Note: I'm doing this in Office XP
tj
Directly before the Zip Field, Insert a Word Field--the Field you want is If...Then...Else
Set it so that if the Zip Field is less than or equal to 9999, it will insert a 0. Leave the Otherwise part blank
Note: I'm doing this in Office XP
tj
D
Dave Peterson
Debra Dalgleish posted this:
After you select your Excel file as a data source, you should see a
'Confirm Data Source' dialog box. From that list, choose 'MS Excel
Worksheets via DDE (*.xls)', and your formatting will be retained.
If you have to connect through a different source, you can format the
fields in the Word document. For example, to specify a number of decimals:
1. In Word, in the Main Document, press Alt+F9 to view the field codes.
2. Find the field code for the number. It will look something like:
{ MERGEFIELD FieldName }
3. Add a switch, to format the number with two decimal places.
For example:
{ MERGEFIELD FieldName \# "#,##0.00" }
4. Press Alt+F9 to hide the field codes.
5. Save the Main Document
(modify that format for zipcodes! "#,##0.00" will become "00000")
After you select your Excel file as a data source, you should see a
'Confirm Data Source' dialog box. From that list, choose 'MS Excel
Worksheets via DDE (*.xls)', and your formatting will be retained.
If you have to connect through a different source, you can format the
fields in the Word document. For example, to specify a number of decimals:
1. In Word, in the Main Document, press Alt+F9 to view the field codes.
2. Find the field code for the number. It will look something like:
{ MERGEFIELD FieldName }
3. Add a switch, to format the number with two decimal places.
For example:
{ MERGEFIELD FieldName \# "#,##0.00" }
4. Press Alt+F9 to hide the field codes.
5. Save the Main Document
(modify that format for zipcodes! "#,##0.00" will become "00000")
G
Guest
Dave
I don't seem to be getting that particular Dialogue Box--or, at least I don't see the Option you mention. Where is it exactly? Is it version specific--I'm in XP
tj
I don't seem to be getting that particular Dialogue Box--or, at least I don't see the Option you mention. Where is it exactly? Is it version specific--I'm in XP
tj
D
David McRitchie
Without looking for the previous posting, I expect he was talking
about Format (menu), Cells, Special, zip codes -- of course
you might not have that in International versions of Excel.
But I would suggest making the column text and using FixUSzip5 in
.http://www.mvps.org/dmcritchie/excel/join.htm#fixuszip5
about Format (menu), Cells, Special, zip codes -- of course
you might not have that in International versions of Excel.
But I would suggest making the column text and using FixUSzip5 in
.http://www.mvps.org/dmcritchie/excel/join.htm#fixuszip5
it version specific--I'm in XP.tjtjjtjt said:Dave,
I don't seem to be getting that particular Dialogue Box--or, at least I don't see the Option you mention. Where is it exactly? Is
D
Dave Peterson
I'm gonna have to have a talk with that Debra character!
Inside MSWord, turn this setting on:
Tools|Options|General Tab|
Check "Confirm Conversion at Open"
And you'll get that dialog.
This setting also means you'll get another dialog when you open non-MSWord files
(.txt for example) within MSWord.
Inside MSWord, turn this setting on:
Tools|Options|General Tab|
Check "Confirm Conversion at Open"
And you'll get that dialog.
This setting also means you'll get another dialog when you open non-MSWord files
(.txt for example) within MSWord.
D
Debra Dalgleish
AFAIK, that's the default setting in Word 2002. (but I've been wrong
before!)
And in Word 2000, in the Open Data Source dialog box, make sure the
'Select Method' option is checked.
before!)
And in Word 2000, in the Open Data Source dialog box, make sure the
'Select Method' option is checked.
D
Dave Peterson
Yeah, but....
that confirmation dialog is a pain when you import a lot of text files. I
turned it off (like all right thinking humans!).
oh, oh.
Was that outloud?
that confirmation dialog is a pain when you import a lot of text files. I
turned it off (like all right thinking humans!).
oh, oh.
Was that outloud?
D
Debra Dalgleish
Yeah, but no one was listening.
Dave said:Yeah, but....
that confirmation dialog is a pain when you import a lot of text files. I
turned it off (like all right thinking humans!).
oh, oh.
Was that outloud?
P
Peo Sjoblom
Does that ever happen?
--
Regards,
Peo Sjoblom
specific--I'm in XP.
--
Regards,
Peo Sjoblom
I don't see the Option you mention. Where is it exactly? Is it versionDebra Dalgleish said:Yeah, but no one was listening.
specific--I'm in XP.
D
Debra Dalgleish
G
Guest
Thanks to everyone for all your suggestions
I do remember in Excel 97 there were no conversions that had to be made - the zip 5 digit format which allowed the 0 as the first digit in Excel carried the 0 over to the address without making any other changes. Does anyone know why Microsoft made a simple task more difficult?
I do remember in Excel 97 there were no conversions that had to be made - the zip 5 digit format which allowed the 0 as the first digit in Excel carried the 0 over to the address without making any other changes. Does anyone know why Microsoft made a simple task more difficult?
G
Guest
Thanks for pointing this out. Thank you TWT, for asking a question that helped me out, too. Much better answer than wht I came up with
Out of curiosity, is there anywhere that lists the differences between the four converters that show up for Excel sheets? I'd like to figure out under what circumstances the different ones might be appropriate. I've been poking around in the Word Help and haven't found an answer yet
tj
Out of curiosity, is there anywhere that lists the differences between the four converters that show up for Excel sheets? I'd like to figure out under what circumstances the different ones might be appropriate. I've been poking around in the Word Help and haven't found an answer yet
tj
D
Debra Dalgleish
It's not more difficult -- it's "enhanced" <g>
There's information here, that you may find useful:
http://www.gmayor.com/mail_merge_labels_with_word_xp.htm#Excel_data
There's information here, that you may find useful:
http://www.gmayor.com/mail_merge_labels_with_word_xp.htm#Excel_data
D
Debra Dalgleish
The converters are discussed here:
http://www.gmayor.com/mail_merge_labels_with_word_xp.htm#Excel_data
http://www.gmayor.com/mail_merge_labels_with_word_xp.htm#Excel_data
P
Peo Sjoblom
With Dave I would say both..
G
Guest
Thanks again!
tj
tj
D
Dave Peterson
It makes life more interesting?????
I have no idea, really. But you can send a message to
(e-mail address removed)
or
(e-mail address removed)
(with excel in the subject)
to ask for it back.
I have no idea, really. But you can send a message to
(e-mail address removed)
or
(e-mail address removed)
(with excel in the subject)
to ask for it back.
D
Dave Peterson
And I'd answer "sometimes" to both!
Peo said:With Dave I would say both..
--
Regards,
Peo Sjoblom
G
Guest
Debra,
The link provided was very useful. Thanks
I have been able to work my way through the other "enhancements" but losing the 0 as a placeholder has driven me crazy. Why the 97 version had to be enhanced in this manner makes me wonder what
Starbucks puts in the coffee in Microsoft land.
The link provided was very useful. Thanks
I have been able to work my way through the other "enhancements" but losing the 0 as a placeholder has driven me crazy. Why the 97 version had to be enhanced in this manner makes me wonder what
Starbucks puts in the coffee in Microsoft land.