Express Mergefield Number as Text

Naveen Dhanaraj · #1 02-19-2020, 01:18 AM

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

macropod · #2 02-19-2020, 05:19 AM

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

Naveen Dhanaraj · #3 02-20-2020, 07:52 AM

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

macropod · #4 02-20-2020, 02:44 PM

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.

Thread Tools
Show Printable Version Email this Page
Display Modes
Linear Mode Switch to Hybrid Mode Switch to Threaded Mode

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
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
IF statement just generates text "MERGEFIELD" in document - what am I doing wrong?	Eri	Mail Merge	1	03-03-2012 03:03 PM