Using Excel as a database and transferring a zero

B

Bob Reynolds

I have excel 2003 and am using it as a database for a WORD document as the
datasource. I have several fields in the excel database that are blank and
while I have show zeros turned off, they show up as a 0 in the merge field
in the word document.

I've seen a formula to make this 0 a non space so it formats properly in
WORD but can't locate it.
Any ideas?
Right now it goes as this Robert 0 Jones instead of Robert Jones
Thanks Bob Reynolds
 
J

Jennifer

Perhaps you could try this simple Find & Replace:
Find: ~0~
Replace: leave blank
Replace all
That should replace all your zeros with nothing.

Hope that helps!
Jennifer
 
J

Jennifer

My mistake - that would replace ALL zeros... I don't think you want that.
I'll leave the answering to the experts... :)
 
R

rjr

Unfortunately this is a complicated worksheet that is used to complete
merge's in WORD. It's used by several "non-computer" people and that idea
would be an issue for them.
 
D

Debra Dalgleish

In the Mail Merge, after you select your Excel file as a data source,
you should see a 'Confirm Data Source' dialog box.
(If you don't see the dialog box, change the setting in Word --
under Tools>Options, General -- add a check mark to
'Confirm Conversion at Open')

From that list, choose 'MS Excel Worksheets via DDE (*.xls)', and your
blanks should be retained.

If you have to connect through a different source, you can format the
fields in the Word document. For example:

1. In Word, in the Main Document, press Alt+F9 to view the field codes.
2. Find the field code for the PO Box Zip. It will look something like:
{ MERGEFIELD "POZip" }
3. Click to the left of this field, and choose Insert>Field
4. Select IF, click OK
5. The result should be: {IF \* MERGEFORMAT }
6. Delete the switch ( \* MERGEFORMAT)
7. Select the { MERGEFIELD "POZip"} and cut it.
8. Click inside the IF field, just in front of the closing bracket.
9. Create the following, pasting in the cut text, instead of typing it:
{IF "{MERGEFIELD "POZip"}" = "0" "" "{MERGEFIELD "POZip"}"}
10. Press Alt+F9 to hide the field codes.
11. Save the Main Document, and view the records

There's more information in the following article on the Microsoft web
site:

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