Excel number in Word Mail Merge

sykes

Well-known Member

Joined

May 1, 2002

Messages

1,885

Office Version

1. 365

Platform

1. Windows

• Aug 27, 2007

• #2

Hi Ginny
If you just wanted it to 0 decimal places, and didn't mind it rounding up or down, then just right-click the Excel cell(s) in question use "Format Cells" then use "Number" select 0 decimal places, and finally check the box to use the comma as a thousands seperator.

 

Upvote 0

Peter_SSs

MrExcel MVP, Moderator

Joined

May 28, 2005

Messages

63,880

Office Version

1. 365

Platform

1. Windows

• Aug 27, 2007

• #3

Hi,

I am using excel figures in a word mail merge and they keep showing up in the word document as the wrong format.

I would like the figure to be displayed in the mail merge as 14,316 however it is coming up as 14316.709999999999.

I don't seem to be able to format the figure in word, does anyone have any suggestions on how I would format it in excel?

Thanks

Ginny

Click to expand...

I'm not up to speed on using Excel data in a mail merge, but if the 14316.709.... is the result of a formula in Excel, what about changing your formula to something like:
=INT(your_existing_formula)
or maybe
=ROUND(your_existing_formula,0)

 

Upvote 0

Norie

Well-known Member

Joined

Apr 28, 2004

Messages

76,377

Office Version

1. 365

Platform

1. Windows

• Aug 27, 2007

• #4

Ginny

You can format data in a mail merge.

You use what are called switches.

Check out Help in Word, it's not too bad on the topic.

 

Upvote 0

G

gossv

Board Regular

Joined

Jul 8, 2005

Messages

141

• Aug 27, 2007

• #5

Thank you everyone,

I'll give all options a go

 

Upvote 0

NateO

Legend

Joined

Feb 17, 2002

Messages

9,700

• Aug 27, 2007

• #6

I don't think rounding the number, to another number, is going to be of much help to you, here, because this is floating-point error. This just tends to happen during the Merge...

Convert your data to a text String before sending it off to Word. See the Fixed Function, e.g.,

Code:

=FIXED(A1,1,FALSE)

I suspect it was implemented in Excel for this very case... Since you're going to Word and it's all Strings/Text anyway, you should be fine.

Edit: Norie's suggestion may work, too. Does that flip the Data Type?

 

Upvote 0

Smitty

Legend

Joined

May 15, 2003

Messages

29,536

• Aug 27, 2007

• #7

Nate,

Norie's on the mark regarding switches for merges. You can set the data type to any custom number format by writing it in the fields.

gossv, check the Word helpfile for "Format (\*) field switch ".

HTH,

Smitty

 

Upvote 0

NateO

Legend

Joined

Feb 17, 2002

Messages

9,700

• Aug 27, 2007

• #8

Okay, one of the two is the ticket, then.

The trick is to get the data out of a Double data type, which is prone to floating-point rounding error in this process, or in general, and get this puppy into a Text/String data type, which knows nothing about floating-point.

 

Upvote 0

Macropod

Retired Moderator

Joined

Aug 27, 2007

Messages

3,677

• Aug 27, 2007

• #9

Hi gossv,

The simple solution is to:
. select your mergefield in Word
. press Shift-F9 to expose the field code, which should look something like '{MERGEFIELD ExcelVal}'
. add a '\# 0' numeric switch to the field, thus '{MERGEFIELD ExcelVal \# 0}'
. press F9 to update the field
. run your mailmerge.

There is no need to modify anything in Excel.

Cheers,
macropod

 

Upvote 0

Peter_SSs

MrExcel MVP, Moderator

Joined

May 28, 2005

Messages

63,880

Office Version

1. 365

Platform

1. Windows

• Aug 28, 2007

• #10

Nothing to do with Excel, I know but...

I have found this link has some good info on Word fields and switches.
http://office.microsoft.com/en-us/word/CH061047231033.aspx

 

Upvote 0