Results 1 to 7 of 7
  1. #1
    New Lounger
    Join Date
    May 2011
    Posts
    4
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Mail Merge Currency, cents only without dot

    I have a mail merge that i want to get the cents only. but i cant use /.00 because then i get a dot. How can we get it without the dot?

  2. #2
    Super Moderator RetiredGeek's Avatar
    Join Date
    Mar 2004
    Location
    Manning, South Carolina
    Posts
    6,176
    Thanks
    200
    Thanked 781 Times in 715 Posts
    I don't know what your source is but you can multiply the number by 100 then take the modulo of the answer divided by 100 should get you an integer from 0 to 99.

    On second thought I guess you could just take the modulo of the number divided by 1!
    May the Forces of good computing be with you!

    RG

    VBA Rules!

    My Systems: Desktop Specs
    Laptop Specs


  3. #3
    New Lounger
    Join Date
    May 2011
    Posts
    4
    Thanks
    0
    Thanked 0 Times in 0 Posts
    I am talking about a mail merge field. I dont control the actual data. So lets assume the field name is MyAmount. and the row is 2.45. What i want is to right only 45.

    How do i get only the 45 to display.

  4. #4
    5 Star Lounger
    Join Date
    Jan 2001
    Location
    La Jolla,CA
    Posts
    1,074
    Thanks
    12
    Thanked 36 Times in 35 Posts
    If the merge field name is Value1, try field codes: { mod(Value1,1) * 100 }

  5. #5
    New Lounger
    Join Date
    May 2011
    Posts
    4
    Thanks
    0
    Thanked 0 Times in 0 Posts
    Thanks. This is what i used in the end for anyone interested.

    {MERGEFIELD KP \* CardText \* FirstCap} Dollars and = {={MERGEFIELD KP \# ".00" } *100 \* MERGEFORMAT \* CardText \* FirstCap } Cents

    And this is the result.

    Fifty-two Dollars and twenty Cents

  6. #6
    Super Moderator
    Join Date
    May 2002
    Location
    Canberra, Australian Capital Territory, Australia
    Posts
    3,894
    Thanks
    0
    Thanked 188 Times in 172 Posts
    Hi Shragel,

    You don't need the '\* MERGEFORMAT' switch. And, in case, you're interested, here's a more sophisticated version of the code, that adds the 's' on 'dollar' and 'cent' only where warranted:
    {QUOTE {SET Val {MERGEFIELD KP}"{=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;"}"}" \* FirstCap}

    One thing you'll note is that the \* CardText switch doesn't insert 'and' where it's used in such strings outside the US (eg "One hundred and one dollars"). If you need that, I can provide an even more sophisticated version.
    Cheers,

    Paul Edstein
    [MS MVP - Word]

  7. The Following User Says Thank You to macropod For This Useful Post:

    RetiredGeek (2011-05-27)

  8. #7
    New Lounger
    Join Date
    May 2011
    Posts
    4
    Thanks
    0
    Thanked 0 Times in 0 Posts
    Thanks. You are great.

Tags for this Thread

Posting Permissions

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