Share via

Mail Merge field format with various data types as possible input

Anonymous
2011-05-06T13:51:38+00:00

Hello all!

I am putting together a template using mail merge. This is the first time that I have made extensive use of the mail merge feature, so I am still learning how to format the fields.

The source is an Excel spreadsheet. In the sheet, there are a number of fields where the input data type can vary.  Example: in the field "DoctorOfficeUS" the user can input the following values:

Standard Coinsurance (after deductible)

Standard Coinsurance (deductible waived)

100%

90%

80%

If I insert the field into the Word document with no formatting, it shows the text values correctly but displays the percentages as decimals, such as 0.9000000000000000

If I use switches to format the percentages, the percentages display correctly, but a syntax error is generated if the field contains one of the text options:

{ ={MERGEFIELD "DoctorOfficeUS" } *100 # "##0%" }

Is there a way to determine the data type and then format it appropriately? I am thinking that some sort of "If" statement might work. How would I determine if the input is numeric or text? Changing the input Excel file is not really an option since this file is in use by numerous salespeople in the field, and we have lots of existing data that needs to be imported into the template that I am creating.

Help?

Microsoft 365 and Office | Word | For home | Windows

Locked Question. This question was migrated from the Microsoft Support Community. You can vote on whether it's helpful, but you can't add comments or replies or follow the question.

0 comments

Answer accepted by question author

Anonymous
2011-05-06T14:42:23+00:00

You can try 

{ IF { MERGEFIELD "DoctorOfficeUS" } < 2 "{ ={ Mergefield DoctorOfficeUS } * 100 #0% }" "{ Mergefield DoctorOfficeUS }  }

All the pairs of { } have to be the special field code character that you can insert using ctrl-F9

If the first 8 data rows of your sheet all happen to have have numeric (1.e. 100%, 90%, 80%) values the texts will come through as "0" and you will either have to fix the sheet anyway, connect using DDE instead of OLE DB, or get the data using another method.

Was this answer helpful?

1 person found this answer helpful.
0 comments

13 additional answers

Sort by: Most helpful
Most helpful Newest Oldest
  1. Paul Edstein 82,861 Reputation points Volunteer Moderator
    2011-05-06T22:24:22+00:00

    Another approach:

    {IF{MERGEFIELD "DoctorOfficeUS"}= {={ Mergefield DoctorOfficeUS }} {={Mergefield DoctorOfficeUS}*100 # 0%} {Mergefield DoctorOfficeUS}}

    Basically, this tests whether the mergefield's contents is a number. If it is, the number is exressed as a percentage; otherwise it's output as text. As such, it's independent of both the value of the number and the content of the text.

    Was this answer helpful?

    0 comments
  2. Anonymous
    2011-05-06T16:43:59+00:00

    This works. Thanks to both of you for your help.

    Was this answer helpful?

    0 comments
  3. Anonymous
    2011-05-06T15:02:56+00:00

    Thanks, I think I see what you are trying to do, but I am still getting a syntax error. Is there a " missing in the code you posted? I see a total of seven - shouldn't there be 8?

    I corrected it as such:

    { IF { MERGEFIELD "DoctorOfficeUS" } <> "Standard*" "{ ={MERGEFIELD "DoctorOfficeUS" } * 100 # 0% }"{ " MERGEFIELD "DoctorOfficeUS" }" }

    This is still giving me a Syntax error when I select one of the text options as the input. The percentages display correctly.

    Was this answer helpful?

    0 comments
  4. Anonymous
    2011-05-06T14:12:29+00:00

    If those are the full range of values then

    { IF { MERGEFIELD "DoctorOfficeUS" } <> "Standard*" "{ ={ Mergefield DoctorOfficeUS } * 100 # 0%}" "{ Mergefield DoctorOfficeUS } }

    should do the trick

    See also http://www.gmayor.com/formatting_word_fields.htm

    Was this answer helpful?

    0 comments