locked
switch to change numbers into text containing dollars & cents in text RRS feed

  • Question

  • I need to convert a currency number into text that includes dollars & cents. ie. one thousand two hundred fourteen dollars and sixty-two cents. 

    I have used Graham Mayor's site a lot and it has been extremely helpful (http://www.gmayor.com/formatting_word_fields.htm#cash_amounts_in_words) but perhaps I am not understanding how to implement his solution.

    I need this result: Where Text1 contains 5.75 the fields produce five dollars and seventy-five cents 

    I am only used to adding switches such as /*Firstcap or /dollartext. 

    Could you help me understand what the solution is or lead me to what I need to research to understand a new process other than just simple switches.

    Is this a conditional switch? If so I may just need to read up on them so I can wrap my head around this.

    My use for mail merge is atypical. I am creating forms or documents specific to one account through mail merge pulling information from one software program system.  Excel is not used.


    Friday, June 10, 2016 1:25 AM

Answers

  • You could use a field coded as:

    {QUOTE {SET Val {MERGEFIELD Amount}}"{=INT(Val) \* CardText} dollar{=INT(Val)-1 \# "s;s;"}{IF{=MOD(Val,1) \# 0.00}> 0 " and {={=MOD(Val,1)*100 \# 0} \* CardText } cent{=INT(MOD(Val,1)*100-1) \# "s;s;"}"}"}
    or:
    {QUOTE {SET Val «Amount»}"{=INT(Val) \* CardText} dollar{=INT(Val)-1 \# "s;s;"}{IF{=MOD(Val,1) \# 0.00}> 0 " and {={=MOD(Val,1)*100 \# 0} \* CardText } cent{=INT(MOD(Val,1)*100-1) \# "s;s;"}"}"}

    where 'Amount' is the name of your mergefield. This will give outputs such as:

    one dollar
    twelve dollars
    one hundred twenty-three dollars
    one dollar and one cent
    one dollar and twelve cents
    one thousand twenty-four dollars and fifty-five cents

    more complex field coding would be required to insert the 'and' used outside the US, as in:

    one hundred and twenty-three dollars
    one thousand and twenty-four dollars and fifty-five cents

    For outputs that don't suppress the cents when zero, you could instead use:

    {QUOTE {SET Val {MERGEFIELD Amount}}"{=INT(Val) \* CardText} dollar{=INT(Val)-1 \# "s;s;"} and {={=MOD(Val,1)*100 \# 0} \* CardText } cent{=INT(MOD(Val,1)*100-1) \# "s;s;"}"}
    or:
    {QUOTE {SET Val «Amount»}"{=INT(Val) \* CardText} dollar{=INT(Val)-1 \# "s;s;"} and {={=MOD(Val,1)*100 \# 0} \* CardText } cent{=INT(MOD(Val,1)*100-1) \# "s;s;"}"}

    Note: The field brace pairs (i.e. '{ }') for the above examples are all created in the document itself, via Ctrl-F9 (Cmd-F9 on a Mac); you can't simply type them or copy & paste them from this message. Nor is it practical to add them via any of the standard Word dialogues. Likewise, you can't type or copy & paste the chevrons (i.e. '« »') - they're part of the actual mergefields, which you can insert from the 'Insert Merge Field' dropdown. The spaces represented in the field construction are all required.

    For a macro to convert the above into working field codes, see Convert Text Representations of Fields to Working Fields in the Mailmerge Tips and Tricks threads at:
    http://www.msofficeforums.com/mail-merge/21803-mailmerge-tips-tricks.html
    and:
    http://windowssecrets.com/forums/showthread.php/163017-Word-Mailmerge-Tips-amp-Tricks


    Cheers
    Paul Edstein
    [MS MVP - Word]

    • Proposed as answer by Charles Kenyon Sunday, June 12, 2016 8:30 PM
    • Marked as answer by Steve Fan Thursday, June 16, 2016 9:04 AM
    Friday, June 10, 2016 2:59 AM