Mail Merge using Excel the numbers are not as formatted

[jairiana]

Setting up a mail merge document based upon an Excel worksheet that has some cells which are computations which are set up in the cells to be a 2 digit number. When appear in the Mail Merge they don't stay as a 2 digit number.

Any suggestions on how to get the mail merge # to appear as 2 digit?

 

[SkipVought]

jairiana,

Check out this thread that I posted in regarding leading zeros. The same can be said for your question. This will explain why.

thread68-763114

You need to store the numbers as text. FORMATTING with leading zeroes does NOT change the data.

For instance if you want 2 digit numbers, then 1 thru 9 need to be stored

'01
'02
... etc

Skip,

http://www.TheOfficeExperts.com

Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884

 

[jairiana]

Guess I didn't quite explain myself correctly. The numbers are coming out as 5.1234586 when I want the mail merge to have 5.12

Thanks

 

[SkipVought]

...and that's because the numbers in Excel are like 5.12345678. When you FORMAT to LOOK like 5.12, it does not CHANGE the data -- just what you see in Excel.

So...

here's what you need to do.

Make a new column using this formula

=INT(A1*100)/100

assuming that your numbers are in column A



Skip,

http://www.TheOfficeExperts.com

Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884

 

[jairiana]

Doesn't work quite right for all. Some it does fine while others still come out with long decimal numbers.

In relooking at mail merge info it might be that I need to add a switch in the mail merge. If that is the case I am not exactly certain as where to place the switch.

Thanks for your help.

 

[SkipVought]

...for instance???

Skip,

http://www.TheOfficeExperts.com

Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884

 

[jairiana]

Maybe this \# "#,##0.00" \* some where in {Mergefield "Hrs_vac_Acc"}

 

[SkipVought]

{Mergefield "Hrs_vac_Acc" \ # "#,##0.00"}

Skip,

http://www.TheOfficeExperts.com

Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884

 

[jfhewitt]

I just did a dummy mailmerge with an Excel database using plain number format 1.1 etc. Worked fine. I've always had data appear in the merge same as in the worksheet

 

[jairiana]

Jf

Think mine doesn't work as cells I am entering are based upon formulas and the answers all have about 15 digits in the decimals. Was your experience with that are just numbers you entered?

 

[jfhewitt]

Yes...just numbers. But it seems you could copy and paste the values of formulae to a new column and use that for your data source. Use Paste Special of course.