Loading
Ozgrid Excel Help & Best Practices Forums

Excel Video Tutorials / Excel Dashboards Reports



Results 1 to 8 of 8

Thread: Formating Number Values In Word Mail Merge

  1. #1
    Join Date
    20th September 2005
    Location
    Sydney AUSTRALIA
    Posts
    52

    Formating Number Values In Word Mail Merge

    Greetings.

    I've created a mail merge document to report on things like % and total times (excel format [HH]:mm:ss).

    However, the merged fields in Word show the raw value, rather then the value I would like to see.

    Help files state I have to format the merged data, and not the source data (thus I'm posting in the word forum). And I've searched here twice and cannot find an answer.

    Example: Data displayed in Excel column is 88%...data in merged field in Word is 0.88235294117647056. How would I get this to display without decimal points?

    Example: Data displayed in Excel column for time is 82:02:51...data in merged field in word displays as 1/3/1900 10:02:51 AM. How would I format this in Word to reflect what is seen in Excel?

    Many thanks,

    Brendon.
    Last edited by nodnerb; April 13th, 2007 at 13:36.

    Excel Video Tutorials / Excel Dashboards Reports


  2. #2
    Join Date
    7th March 2003
    Location
    Essex, England
    Posts
    11,310

    Re: Formating Number Values In Word Mail Merge

    Last edited by Andy Pope; April 14th, 2007 at 01:43.

    Cheers
    Andy


  3. #3
    Join Date
    20th September 2005
    Location
    Sydney AUSTRALIA
    Posts
    52

    Re: Formating Number Values In Word Mail Merge

    Thanks Andy

    Well, I can't say it won't work, coz maybe I'm not doing it right?

    The site you suggest says I should do this...

    VB:
    { Mergefield Name \* CharFormat } 
    
    
    So, I'm assuming this includes the spaces within the code.

    One of my fields would then look like this (I'm guessing)

    VB:
    { MERGEFIELD "M_Answered" \* CharFormat } 
    
    
    Does that look right?

    If so, is there anything else I'm supposed to do, coz I can't get it to work.

    Cheers

    Excel Video Tutorials / Excel Dashboards Reports


  4. #4
    Join Date
    7th March 2003
    Location
    Essex, England
    Posts
    11,310

    Re: Formating Number Values In Word Mail Merge

    The percentage field requires a calculation as the % in the number format is treated as a character not an operation as it is when formatting in excel.

    This is the field code for a excel field column named Percent.

    { ={MERGEFIELD Percent} *100 \# "0.0%" }

    Here is another site with information
    http://www.gmayor.com/formatting_word_fields.htm

    Cheers
    Andy


  5. #5
    Join Date
    20th September 2005
    Location
    Sydney AUSTRALIA
    Posts
    52

    Re: Formating Number Values In Word Mail Merge

    Thanks Andy

    That second site explained it a bit better for me. I've had success with the % field. Still having trouble with the time field, in that I'm trying to display a total number of hours:mins:secs ie... 38:24:21

    I'll keep searching. Couldn't find anything in the documents provided so far, unless I'm mising the point.

    If anyone finds anything in the meantime, please let me know?

    Many thanks...progress has been made.

    Cheers

    Excel Video Tutorials / Excel Dashboards Reports


  6. #6
    Join Date
    20th September 2005
    Location
    Sydney AUSTRALIA
    Posts
    52

    Re: Formating Number Values In Word Mail Merge

    Can I safely assume we're all at at dead end over the time format switch and unbookmark this thread?

    I've been searching for an answer feverantly since the last post without any joy.

    Many thanks to Andy for the assistance.

    Cheers
    Last edited by nodnerb; April 18th, 2007 at 19:06.

    Excel Video Tutorials / Excel Dashboards Reports


  7. #7
    Join Date
    7th March 2003
    Location
    Essex, England
    Posts
    11,310

    Re: Formating Number Values In Word Mail Merge

    This simplest way to solve the problem is to convert the values in the data source file.
    Create extra fields for Hours, Minutes and seconds.

    Assuming field B2 contains the time value.

    Total hours is =INT((B2*86400)/3600)
    Total minutes is =INT(((B2*86400)-(INT((B2*86400)/(3600))*3600))/60)
    Total seconds is =(B2*86400)-((INT((B2*86400)/3600)*3600)+(INT(((B2*86400)-(INT((B2*86400)/(3600))*3600))/60)*60))

    If you were brave you change the formatting of the time cell to general number and then do the above in the form fields.

    Cheers
    Andy


  8. #8
    Join Date
    20th September 2005
    Location
    Sydney AUSTRALIA
    Posts
    52

    Re: Formating Number Values In Word Mail Merge

    I'll give that a shot Andy.

    Cheers

    Excel Video Tutorials / Excel Dashboards Reports


Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Possible Answers

  1. Vb Mail Merge To Word
    By ben3001 in forum Excel and/or Word Help
    Replies: 2
    Last Post: May 8th, 2007, 03:15
  2. mail merge with xls - word
    By black knight in forum EXCEL HELP
    Replies: 1
    Last Post: April 12th, 2005, 04:14
  3. Mail merge from Excel to Word - Crashes Word on closing let
    By SleeplessInSomerset in forum Excel and/or Word Help
    Replies: 2
    Last Post: April 27th, 2004, 23:07

Bookmarks

Posting Permissions

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