Microsoft Office Forums

Go Back   Microsoft Office Forums > >

Reply
 
Thread Tools Display Modes
  #1  
Old 02-19-2020, 01:18 AM
Naveen Dhanaraj Naveen Dhanaraj is offline Express Mergefield Number as Text Windows 10 Express Mergefield Number as Text Office 2019
Novice
Express Mergefield Number as Text
 
Join Date: Jan 2020
Posts: 20
Naveen Dhanaraj is on a distinguished road
Default Express Mergefield Number as Text

How could I convert Currency in to words without VBA.... I want achieve this with a formula....




Amount Field Name: PAYMENTVALUETOTAL



Like this,
"$1,000.23", I need it like "One thousand dollars and twenty three cents"


how could I achieve this via formula...


Thanks,
Naveen
Reply With Quote
  #2  
Old 02-19-2020, 05:19 AM
macropod's Avatar
macropod macropod is offline Express Mergefield Number as Text Windows 7 64bit Express Mergefield Number as Text Office 2010 32bit
Administrator
 
Join Date: Dec 2010
Location: Canberra, Australia
Posts: 20,063
macropod has a brilliant futuremacropod has a brilliant futuremacropod has a brilliant futuremacropod has a brilliant futuremacropod has a brilliant futuremacropod has a brilliant futuremacropod has a brilliant futuremacropod has a brilliant futuremacropod has a brilliant futuremacropod has a brilliant futuremacropod has a brilliant future
Default

For that you could use a field constructed as:
Code:
{QUOTE{SET Val «PAYMENTVALUETOTAL»}↵
{IF{=INT(Val/100000000)}<> 0 "{=INT(Val/100000000) \*Cardtext} hundred "}↵
{IF{=MOD(INT(Val/1000000),100)}<> 0 "{IF{=MOD(INT(Val/100000000),10)}<> 0 "and "}"}↵
{IF{=INT(Val/100000)}<> 0 "{=MOD(INT(Val/1000000),100) \*Cardtext} million{IF{=MOD(INT(Val/1000),10)}<> 0 ","} "}↵
{IF{=MOD(INT(Val/100000),10)}<> 0 "{=MOD(INT(Val/100000),10) \*Cardtext} hundred "}↵
{IF{=MOD(INT(Val/1000),100)}<> 0 "{IF{=MOD(INT(Val/100000),10)}<> 0 "and "}" {IF{=MOD(INT(Val/1000),10)}<> 0 "thousand{IF{=MOD(INT(Val/1000),10)}= 0 "," }} "}↵
{IF{=MOD(INT(Val/1000),10)}<> 0 "{=MOD(INT(Val/1000),100) \*Cardtext} thousand{IF{= MOD(INT(Val/100),10)}<> 0 ","} "}↵
{IF{=MOD(INT(Val/100),10)}<> 0 "{=MOD(INT(Val/100),10) \*Cardtext} hundred "}↵
{IF{=MOD(INT(Val),100)}<> 0 "{IF{=INT(Val/100)}<> 0 "and "}{=MOD(INT(Val),100) \*Cardtext} "}↵
{=INT(Val) \# \dollar}{IF{=INT(Val)}<> 0 "s"}↵
" and {=MOD(Val,1)*100 \*Cardtext} cent{IF{=MOD(Val,1)}<> 0.01 s}" \* FirstCap}
Note: The field brace pairs (i.e. '{ }') for the above example are all created in the document itself, via Ctrl-F9 (Cmd-F9 on a Mac or, if you’re using a laptop, you might need to use Ctrl-Fn-F9); 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, the chevrons (i.e. '« »') are part of the actual mergefields - which you can insert from the 'Insert Merge Field' dropdown (i.e. you can't type or copy & paste them from this message, either). The spaces represented in the field constructions are all required. Instead of the ↵ symbols, you should use real line/paragraph breaks. Alternatively, for a macro to convert the above into a working field code, see Convert Text Representations of Fields to Working Fields in the Mailmerge Tips and Tricks 'Sticky' thread at the top of this forum:
Mailmerge Tips & Tricks
__________________
Cheers,
Paul Edstein
[MS MVP - Word]
Reply With Quote
  #3  
Old 02-20-2020, 07:52 AM
Naveen Dhanaraj Naveen Dhanaraj is offline Express Mergefield Number as Text Windows 10 Express Mergefield Number as Text Office 2019
Novice
Express Mergefield Number as Text
 
Join Date: Jan 2020
Posts: 20
Naveen Dhanaraj is on a distinguished road
Default

I had done exactly same as you had suggested but getting error


"Minus Error! Unknown op code for conditional.Error! Unknown op code for conditional.six thousand, zero hundred zerodollars"


Thanks,
Naveen
Reply With Quote
  #4  
Old 02-20-2020, 02:44 PM
macropod's Avatar
macropod macropod is offline Express Mergefield Number as Text Windows 7 64bit Express Mergefield Number as Text Office 2010 32bit
Administrator
 
Join Date: Dec 2010
Location: Canberra, Australia
Posts: 20,063
macropod has a brilliant futuremacropod has a brilliant futuremacropod has a brilliant futuremacropod has a brilliant futuremacropod has a brilliant futuremacropod has a brilliant futuremacropod has a brilliant futuremacropod has a brilliant futuremacropod has a brilliant futuremacropod has a brilliant futuremacropod has a brilliant future
Default

That tells me you haven't matched exactly the field coding I posted. The error could be as simple as the omission of a space character.
__________________
Cheers,
Paul Edstein
[MS MVP - Word]
Reply With Quote
  #5  
Old 03-16-2020, 12:13 PM
Naveen Dhanaraj Naveen Dhanaraj is offline Express Mergefield Number as Text Windows 10 Express Mergefield Number as Text Office 2019
Novice
Express Mergefield Number as Text
 
Join Date: Jan 2020
Posts: 20
Naveen Dhanaraj is on a distinguished road
Default

If I have 1000.00 dollars. This formula doesn't give output value as One Thousand Dollars and Zero Cents. Instead it is coming only as One Thousand Dollars, It is not bringing decimal characters only for zero cents... could you please help me on this
Reply With Quote
  #6  
Old 03-16-2020, 03:23 PM
macropod's Avatar
macropod macropod is offline Express Mergefield Number as Text Windows 7 64bit Express Mergefield Number as Text Office 2010 32bit
Administrator
 
Join Date: Dec 2010
Location: Canberra, Australia
Posts: 20,063
macropod has a brilliant futuremacropod has a brilliant futuremacropod has a brilliant futuremacropod has a brilliant futuremacropod has a brilliant futuremacropod has a brilliant futuremacropod has a brilliant futuremacropod has a brilliant futuremacropod has a brilliant futuremacropod has a brilliant futuremacropod has a brilliant future
Default

I have revised the field code in post #2. Try it now.
__________________
Cheers,
Paul Edstein
[MS MVP - Word]
Reply With Quote
  #7  
Old 03-17-2020, 05:40 AM
Naveen Dhanaraj Naveen Dhanaraj is offline Express Mergefield Number as Text Windows 10 Express Mergefield Number as Text Office 2019
Novice
Express Mergefield Number as Text
 
Join Date: Jan 2020
Posts: 20
Naveen Dhanaraj is on a distinguished road
Default

Thanks Paul,
while I was testing I found this issue,For Example if my amount was 5000000, output is like this Five Million Zero Hundred Zero Thousand Dollars And Zero Cents but instead it should show like this Five Million Dollars and Zero cents.

How could I achieve this....
Reply With Quote
  #8  
Old 03-17-2020, 03:11 PM
macropod's Avatar
macropod macropod is offline Express Mergefield Number as Text Windows 7 64bit Express Mergefield Number as Text Office 2010 32bit
Administrator
 
Join Date: Dec 2010
Location: Canberra, Australia
Posts: 20,063
macropod has a brilliant futuremacropod has a brilliant futuremacropod has a brilliant futuremacropod has a brilliant futuremacropod has a brilliant futuremacropod has a brilliant futuremacropod has a brilliant futuremacropod has a brilliant futuremacropod has a brilliant futuremacropod has a brilliant futuremacropod has a brilliant future
Default

Try the revised field code - changes noted in red.
__________________
Cheers,
Paul Edstein
[MS MVP - Word]
Reply With Quote
Reply

Thread Tools
Display Modes


Similar Threads
Thread Thread Starter Forum Replies Last Post
Mergefield - Replace Prefix of Document Number boonlailee Mail Merge 13 06-13-2019 04:38 PM
Word MergeField Code Text issue stevenel Mail Merge 17 09-27-2018 09:12 PM
Express Mergefield Number as Text Printing selected pages based on a MERGEFIELD.g. If MERGEFIELD=x then print pages 1,2,4 if MERGEFIEL Richard.p Word 3 04-21-2015 01:14 AM
Omit mergefield if it contains specific text MikeStewart Mail Merge 3 10-01-2014 01:31 AM
Express Mergefield Number as Text IF statement just generates text "MERGEFIELD" in document - what am I doing wrong? Eri Mail Merge 1 03-03-2012 03:03 PM


All times are GMT -7. The time now is 06:03 PM.


Powered by vBulletin® Version 3.8.11
Copyright ©2000 - 2020, vBulletin Solutions Inc.
Search Engine Optimisation provided by DragonByte SEO (Lite) - vBulletin Mods & Addons Copyright © 2020 DragonByte Technologies Ltd.
MSOfficeForums.com is not affiliated with Microsoft