Format when "0" is the first letter

C

cpliu

I have a Excel document that has many cells with "0" as the first character
of a string of numbers. When I change the format to number, the "0" would
disappear. If I use Custom and type multiple 0s, the 0 stays but they would
not stay when I do mail merge in Word.

What can I do to retain the "0" in Excel and be able to mail merge without
losing the "0"?

Thanks,


cpliu
 
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
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. For example:
{ MERGEFIELD FieldName \# "000000" }
4. Press Alt+F9 to hide the field codes.
5. Save the Main Document
 
T

tjtjjtjt

If all the numbers are the same length, I wouldn't use the apostrophe option unless you do it before all the numbers...even if some of them don't start with zero.
--some features, such as sorting, may not work as expected if some of the numbers are text and some actual numbers. You will also have to adjust cell alignment.
Instead, create a Custom Number Format.

Excel has a great Help Topic for Custom Formats.

If all of the numbers are the same length:
Format|Cells, on the Number tab click Custom. Then, in the line in which you can type, press 0 enough times to have the same number length as your numbers in the Spreadsheet.
For instance, if you have 6 digit numbers, type 000000 as the Custom Number Format.
Note that there are "Special" formats for zip codes in the Special category of Number Format.

If the numbers are not the same length but always start with 0, then use this number format:
0#

You will need to specify if decimals are necessary.
0#.00, for instance.

If some of the numbers have a zero in front and some don't and the numbers are not all the same length, I'd recommend you make use of the Format Painter, or Record a Macro that applies the Formatting you need and place a button for it on a Toolbar.

tj
 
Top