Results 1 to 7 of 7
  1. #1
    3 Star Lounger
    Join Date
    Jan 2001
    Posts
    313
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Word/Excel (2002)

    I have a professor who merged data from Excel to Word. The problem is that in Word the merged data is display with too many decimal points, 78.46777777887 - something like this. How to I turn this off? Do I make the change in Excel or Word?

  2. #2
    2 Star Lounger
    Join Date
    Feb 2001
    Location
    Brussels, Brussel, Belgium
    Posts
    159
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Word/Excel (2002)

    hi Melanie,

    that's the problem with professors, they always want to be too precise! <img src=/S/grin.gif border=0 alt=grin width=15 height=15>

    seriously though, you have two options.
    the simplest is to format the numbers directly in excel. word will show them exactly as they are displayed in excel.

    or edit the mergefields fields in word. you'll need to add a formatting switch:
    { MERGEFIELD numbercolumn # #.##0,00 }

    the formatting switch # #.##0,00 will tell word to round numbers to the second decimal. beware of internation settings, you may need to switch comma and the decimal point.

    greetings,

  3. #3
    3 Star Lounger
    Join Date
    Jan 2001
    Posts
    313
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Word/Excel (2002)

    yes - professors can be that way, unfortunately without them I wouldn't have a job, or actually without students, the professor and I and the entire campus would be out of jobs!!

    I did try reformatting in Excel. Everything from number with no decimal to custom but still comes across with 15 decimal places. I will try the field switch. Thanks.

  4. #4
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Word/Excel (2002)

    Melanie,

    A number format applied in Excel is only just that - formatting. The spreadsheet stores the numbers with all those ugly decimals.

    In a mail merge, the actual data are transferred, not their format. Pieter has given you the best way to handle it - with a format switch in the merge field in Word. It is also possible to do it in Excel, but it's more work:

    - Add an extra column in Excel, with a column heading like FormattedNum
    - If the numbers are in column A, starting in cell A2 (for example), enter the formula =TEXT(A2, "0.00") in the second row of the new column
    - Fill down as far as needed.
    - Since these values are text, not numeric, they will be transferred to Word as displayed.
    - Use the new (text) field as merge field instead of the numeric field.

    As you see, Pieter's solution is easier.

  5. #5
    3 Star Lounger
    Join Date
    Jan 2001
    Posts
    313
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Word/Excel (2002)

    Thank you - I have been playing around with the field switches and I have gotten them to work.

    The one thing I forgot to mention is that the entire merge process worked fine in Word/Excel 2000 last week. His department was upgraded to Office XP and now his numbers are formatted incorrectly.

    Can you figure this one out??

  6. #6
    2 Star Lounger
    Join Date
    Feb 2001
    Location
    Brussels, Brussel, Belgium
    Posts
    159
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Word/Excel (2002)

    hi Hans, Melanie.

    formatting the data in excel that will be transferred to word, works for me in office XP (using the DDE merge option), no need to create extra columns.
    btw, wether the transferred number is the result of an excel formula or hard coded, wether you've applied the number format directly or used a custom number format in excel, it still is tranferred as displayed in excel when i test it. (maybe this indeed won't work if one would try the odbc or other merge options that are available)

    greetings,

  7. #7
    3 Star Lounger
    Join Date
    Jan 2001
    Posts
    313
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Word/Excel (2002)

    so what could have changed in the upgrade? All he did was open Excel, update the grades, quizzes, etc and then merged as he always did with Word 2000. I am not sure why it doesn't display correctly?

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •