SOLVED

How to CONVERT Numbers to Words with Built-in Excel Formula?

%3CLINGO-SUB%20id%3D%22lingo-sub-168783%22%20slang%3D%22en-US%22%3EHow%20to%20CONVERT%20Numbers%20to%20Words%20with%20Built-in%20Excel%20Formula%3F%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-168783%22%20slang%3D%22en-US%22%3E%3CP%3EI%20am%20writing%20cheque%20and%20invoices%20that%20must%20have%20the%20dollar%20amounts%20typed%20in%20words%20and%20it%20is%20hectic%20to%20type%20the%20numbers%20and%20then%20type%20words%20manually.%26nbsp%3BFor%20example%26nbsp%3B%24541%2C125.57%26nbsp%3B%20should%20convert%20to%26nbsp%3Bfive%20hundred%20forty-one%20thousand%20one%20hundred%20twenty-five%20Dollars%20and%20fifty-seven%20Cents%3C%2FP%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3EI%20cannot%20use%20Macros%2FVBA%2C%20the%20only%20solution%20to%20work%20for%20me%20is%20built-in%20Excel%20formula%20or%20anything%20that%20would%20not%20use%20Macros.%3C%2FP%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3EThanks.%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-LABS%20id%3D%22lingo-labs-168783%22%20slang%3D%22en-US%22%3E%3CLINGO-LABEL%3EExcel%3C%2FLINGO-LABEL%3E%3CLINGO-LABEL%3EExcel%20on%20Mac%3C%2FLINGO-LABEL%3E%3CLINGO-LABEL%3EExcel%20Online%3C%2FLINGO-LABEL%3E%3CLINGO-LABEL%3EFormulas%20and%20Functions%3C%2FLINGO-LABEL%3E%3CLINGO-LABEL%3EOffice%20365%3C%2FLINGO-LABEL%3E%3C%2FLINGO-LABS%3E%3CLINGO-SUB%20id%3D%22lingo-sub-389235%22%20slang%3D%22en-US%22%3ERe%3A%20How%20to%20CONVERT%20Numbers%20to%20Words%20with%20Built-in%20Excel%20Formula%3F%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-389235%22%20slang%3D%22en-US%22%3E%3CP%3EBrilliant!%20Just%20replace%20Dollar%20to%20your%20own%20currency%20and%20you%20are%20good%20to%20go.%20This%20is%20amazing%20work.%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-168814%22%20slang%3D%22en-US%22%3ERe%3A%20How%20to%20CONVERT%20Numbers%20to%20Words%20with%20Built-in%20Excel%20Formula%3F%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-168814%22%20slang%3D%22en-US%22%3EHaytham%2C%3CBR%20%2F%3EDue%20to%20corporate%20domain%20environment%20administration%20setting%2C%20macros%20are%20blocked%20by%20default%20in%20our%20office%20domain.%3CBR%20%2F%3E%3CBR%20%2F%3EI%20got%20an%20excellent%20solution%20with%20built-in%20formulas%20provided%20by%20Jamil%20Mohammad.%20%3CBR%20%2F%3EThanks%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-168810%22%20slang%3D%22en-US%22%3ERe%3A%20How%20to%20CONVERT%20Numbers%20to%20Words%20with%20Built-in%20Excel%20Formula%3F%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-168810%22%20slang%3D%22en-US%22%3EWow!%3CBR%20%2F%3E%3CBR%20%2F%3ETHANK%20YOU%20SO%20VERY%20MUCH!%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-168805%22%20slang%3D%22en-US%22%3ERe%3A%20How%20to%20CONVERT%20Numbers%20to%20Words%20with%20Built-in%20Excel%20Formula%3F%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-168805%22%20slang%3D%22en-US%22%3E%3CP%3EHi%20Flora%2C%3C%2FP%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3EIn%20fact%2C%20there%20is%20no%26nbsp%3Bbuilt-in%20function%20to%20do%20that.%3C%2FP%3E%0A%3CP%3E%3CSPAN%3EI%20do%20not%20think%20this%20can%20easily%20be%20achieved%20using%20a%20formula.%3C%2FSPAN%3E%3C%2FP%3E%0A%3CP%3E%3CSPAN%3EBut%20always%20there%26nbsp%3Bis%20a%20solution%20with%20VBA%2FMacros.%3C%2FSPAN%3E%3C%2FP%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3EP%3CSPAN%3Elease%20let%20us%20know%20why%20you%20can%20not%20use%20the%20VBA%2FMacros%3F%3CBR%20%2F%3EIs%20it%20disabled%20on%20your%20computer%3F%3C%2FSPAN%3E%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-168791%22%20slang%3D%22en-US%22%3ERe%3A%20How%20to%20CONVERT%20Numbers%20to%20Words%20with%20Built-in%20Excel%20Formula%3F%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-168791%22%20slang%3D%22en-US%22%3E%3CP%3EI%20had%20seen%20this%20question%20quite%20often%2C%20from%20colleagues%2C%20Q%26amp%3BA%20forums%20etc%2C%20but%20while%20there%20were%20many%20UDF%20solutions%2C%20there%20wasn't%20any%20solution%20with%20Built-In%20Formula%20(at%20least%20not%20to%20my%20knowledge).%26nbsp%3B%26nbsp%3B%3C%2FP%3E%0A%3CP%3EYou%20lucked%20out%2C%20as%20I%20had%20already%20written%20this%20Formula%26nbsp%3Bcouple%20of%20years%20ago.%26nbsp%3B%3C%2FP%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3EHere%20is%20the%20formula.%26nbsp%3B%20I%20have%20also%20uploaded%20the%20file%20with%20formula.%3C%2FP%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3EIt%20only%20has%20one%20limitation%2C%20It%20cannot%20convert%20more%20than%20number%26nbsp%3B%20%24999%2C999%2C999%2C999.00%26nbsp%3B%26nbsp%3B%22Nine%20Hundred%20Ninety-Nine%20Billion%20Nine%20Hundred%20Ninety-Nine%20Million%20Nine%20Hundred%20Ninety-Nine%20Thousand%20Nine%20Hundred%20Ninety-Nine%20Dollars%20and%20Zero%20Cent%22%3C%2FP%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3EBut%20I%20think%20it%20will%20do%20the%20job%20for%20you.%3C%2FP%3E%0A%3CP%3E%3CSPAN%20class%3D%22lia-inline-image-display-wrapper%20lia-image-align-inline%22%20style%3D%22width%3A%20999px%3B%22%3E%3CIMG%20src%3D%22https%3A%2F%2Fgxcuf89792.i.lithium.com%2Ft5%2Fimage%2Fserverpage%2Fimage-id%2F29853iCBDFA01EFD4D3AE5%2Fimage-size%2Flarge%3Fv%3D1.0%26amp%3Bpx%3D999%22%20alt%3D%22A.png%22%20title%3D%22A.png%22%20%2F%3E%3C%2FSPAN%3E%3C%2FP%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%0A%3CPRE%3E%3DIF(A1%26lt%3B%3D0%2C%22Referred%20Cell%20Must%20be%20Empty%22%2CIF(A1%26gt%3B10%5E12-0.01%2C%22Exceeds%20Maximum%20which%20is%20999999999999%22%2CTRIM(PROPER(SUBSTITUTE(CONCATENATE(CHOOSE(MID(TEXT(INT(A1)%2CREPT(0%2C12))%2C1%2C1)%2B1%2C%22%22%2C%22one%20hundred%20%22%2C%22two%20hundred%20%22%2C%22three%20hundred%20%22%2C%22four%20hundred%20%22%2C%22five%20hundred%20%22%2C%22six%20hundred%20%22%2C%22seven%20hundred%20%22%2C%22eight%20hundred%20%22%2C%22nine%20hundred%20%22)%2CCHOOSE(MID(TEXT(INT(A1)%2CREPT(0%2C12))%2C2%2C1)%2B1%2C%22%22%2CCHOOSE(MID(TEXT(INT(A1)%2CREPT(0%2C12))%2C3%2C1)%2B1%2C%22ten%22%2C%22eleven%22%2C%22twelve%22%2C%22thirteen%22%2C%22fourteen%22%2C%22fifteen%22%2C%22sixteen%22%2C%22seventeen%22%2C%22eighteen%22%2C%22nineteen%22)%2C%22twenty%22%2C%22thirty%22%2C%22forty%22%2C%22fifty%22%2C%22sixty%22%2C%22seventy%22%2C%22eighty%22%2C%22ninety%22)%2CIF(VALUE(MID(TEXT(INT(A1)%2CREPT(0%2C12))%2C2%2C1))%26gt%3B1%2CCHOOSE(MID(TEXT(INT(A1)%2CREPT(0%2C12))%2C3%2C1)%2B1%2C%22%22%2C%22-one%22%2C%22-two%22%2C%22-three%22%2C%22-four%22%2C%22-five%22%2C%22-six%22%2C%22-seven%22%2C%22-eight%22%2C%22-nine%22)%2CIF(VALUE(MID(TEXT(INT(A1)%2CREPT(0%2C12))%2C2%2C1))%3D0%2CCHOOSE(MID(TEXT(INT(A1)%2CREPT(0%2C12))%2C3%2C1)%2B1%2C%22%22%2C%22one%22%2C%22two%22%2C%22three%22%2C%22four%22%2C%22five%22%2C%22six%22%2C%22seven%22%2C%22eight%22%2C%22nine%22)%2C%22%22))%2CIF(A1%26gt%3B%3D10%5E9%2C%22%20billion%20%22%2C%22%22)%2CCHOOSE(MID(TEXT(INT(A1)%2CREPT(0%2C12))%2C4%2C1)%2B1%2C%22%22%2C%22one%20hundred%20%22%2C%22two%20hundred%20%22%2C%22three%20hu%0Andred%20%22%2C%22four%20hundred%20%22%2C%22five%20hundred%20%22%2C%22six%20hundred%20%22%2C%22seven%20hundred%20%22%2C%22eight%20hundred%20%22%2C%22nine%20hundred%20%22)%2CCHOOSE(MID(TEXT(INT(A1)%2CREPT(0%2C12))%2C5%2C1)%2B1%2C%22%22%2CCHOOSE(MID(TEXT(INT(A1)%2CREPT(0%2C12))%2C6%2C1)%2B1%2C%22ten%22%2C%22eleven%22%2C%22twelve%22%2C%22thirteen%22%2C%22fourteen%22%2C%22fifteen%22%2C%22sixteen%22%2C%22seventeen%22%2C%22eighteen%22%2C%22nineteen%22)%2C%22twenty%22%2C%22thirty%22%2C%22forty%22%2C%22fifty%22%2C%22sixty%22%2C%22seventy%22%2C%22eighty%22%2C%22ninety%22)%2CIF(VALUE(MID(TEXT(INT(A1)%2CREPT(0%2C12))%2C5%2C1))%26gt%3B1%2CCHOOSE(MID(TEXT(INT(A1)%2CREPT(0%2C12))%2C6%2C1)%2B1%2C%22%22%2C%22-one%22%2C%22-two%22%2C%22-three%22%2C%22-four%22%2C%22-five%22%2C%22-six%22%2C%22-seven%22%2C%22-eight%22%2C%22-nine%22)%2CIF(VALUE(MID(TEXT(INT(A1)%2CREPT(0%2C12))%2C5%2C1))%3D0%2CCHOOSE(MID(TEXT(INT(A1)%2CREPT(0%2C12))%2C6%2C1)%2B1%2C%22%22%2C%22one%22%2C%22two%22%2C%22three%22%2C%22four%22%2C%22five%22%2C%22six%22%2C%22seven%22%2C%22eight%22%2C%22nine%22)%2C%22%22))%2CIF(VALUE(MID(TEXT(INT(A1)%2CREPT(0%2C12))%2C4%2C3))%26gt%3B0%2C%22%20million%20%22%2C%22%22)%2CCHOOSE(MID(TEXT(INT(A1)%2CREPT(0%2C12))%2C7%2C1)%2B1%2C%22%22%2C%22one%20hundred%20%22%2C%22two%20hundred%20%22%2C%22three%20hundred%20%22%2C%22four%20hundred%20%22%2C%22five%20hundred%20%22%2C%22six%20hundred%20%22%2C%22seven%20hundred%20%22%2C%22eight%20hundred%20%22%2C%22nine%20hundred%20%22)%2CCHOOSE(MID(TEXT(INT(A1)%2CREPT(0%2C12))%2C8%2C1)%2B1%2C%22%22%2CCHOOSE(MID(TEXT(INT(A1)%2CREPT(0%2C%0A12))%2C9%2C1)%2B1%2C%22ten%22%2C%22eleven%22%2C%22twelve%22%2C%22thirteen%22%2C%22fourteen%22%2C%22fifteen%22%2C%22sixteen%22%2C%22seventeen%22%2C%22eighteen%22%2C%22nineteen%22)%2C%22twenty%22%2C%22thirty%22%2C%22forty%22%2C%22fifty%22%2C%22sixty%22%2C%22seventy%22%2C%22eighty%22%2C%22ninety%22)%2CIF(VALUE(MID(TEXT(INT(A1)%2CREPT(0%2C12))%2C8%2C1))%26gt%3B1%2CCHOOSE(MID(TEXT(INT(A1)%2CREPT(0%2C12))%2C9%2C1)%2B1%2C%22%22%2C%22-one%22%2C%22-two%22%2C%22-three%22%2C%22-four%22%2C%22-five%22%2C%22-six%22%2C%22-seven%22%2C%22-eight%22%2C%22-nine%22)%2CIF(VALUE(MID(TEXT(INT(A1)%2CREPT(0%2C12))%2C8%2C1))%3D0%2CCHOOSE(MID(TEXT(INT(A1)%2CREPT(0%2C12))%2C9%2C1)%2B1%2C%22%22%2C%22one%22%2C%22two%22%2C%22three%22%2C%22four%22%2C%22five%22%2C%22six%22%2C%22seven%22%2C%22eight%22%2C%22nine%22)%2C%22%22))%2CIF(VALUE(MID(TEXT(INT(A1)%2CREPT(0%2C12))%2C7%2C3))%2C%22%20thousand%20%22%2C%22%22)%2CCHOOSE(MID(TEXT(INT(A1)%2CREPT(0%2C12))%2C10%2C1)%2B1%2C%22%22%2C%22one%20hundred%20%22%2C%22two%20hundred%20%22%2C%22three%20hundred%20%22%2C%22four%20hundred%20%22%2C%22five%20hundred%20%22%2C%22six%20hundred%20%22%2C%22seven%20hundred%20%22%2C%22eight%20hundred%20%22%2C%22nine%20hundred%20%22)%2CCHOOSE(MID(TEXT(INT(A1)%2CREPT(0%2C12))%2C11%2C1)%2B1%2C%22%22%2CCHOOSE(MID(TEXT(INT(A1)%2CREPT(0%2C12))%2C12%2C1)%2B1%2C%22ten%22%2C%22eleven%22%2C%22twelve%22%2C%22thirteen%22%2C%22fourteen%22%2C%22fifteen%22%2C%22sixteen%22%2C%22seventeen%22%2C%22eighteen%22%2C%22nineteen%22)%2C%22twenty%22%2C%22thirty%22%2C%22forty%22%2C%22fifty%22%2C%22sixty%22%2C%22seventy%22%2C%22eighty%22%2C%22ninety%0A%22)%2CIF(VALUE(MID(TEXT(INT(A1)%2CREPT(0%2C12))%2C11%2C1))%26gt%3B1%2CCHOOSE(MID(TEXT(INT(A1)%2CREPT(0%2C12))%2C12%2C1)%2B1%2C%22%22%2C%22-one%22%2C%22-two%22%2C%22-three%22%2C%22-four%22%2C%22-five%22%2C%22-six%22%2C%22-seven%22%2C%22-eight%22%2C%22-nine%22)%2CIF(VALUE(MID(TEXT(INT(A1)%2CREPT(0%2C12))%2C11%2C1))%3D0%2CCHOOSE(MID(TEXT(INT(A1)%2CREPT(0%2C12))%2C12%2C1)%2B1%2C%22%22%2C%22one%22%2C%22two%22%2C%22three%22%2C%22four%22%2C%22five%22%2C%22six%22%2C%22seven%22%2C%22eight%22%2C%22nine%22)%2C%22%22)))%2C%22%20%22%2C%22%20%22)%26amp%3BIF(FLOOR(A1%2C1)%26gt%3B1%2C%22%20dollars%22%2C%22%20%22)%26amp%3BIF(AND(A1%26gt%3B%3D1%2CA1%26lt%3B2)%2C%22dollar%22%2C%22%22))%26amp%3B%22%20and%20%22%26amp%3BIF(ISERROR(FIND(%22.%22%2CA1))%2C%22Zero%20Cent%22%2CPROPER(IF(LEN(LEFT(TRIM(MID(SUBSTITUTE(A1%2C%22.%22%2CREPT(%22%20%22%2C255))%2C255%2C200))%2C2))%3D1%2CCHOOSE(1*LEFT(TRIM(MID(SUBSTITUTE(A1%2C%22.%22%2CREPT(%22%20%22%2C255))%2C255%2C200))%2C2)%2C%22ten%22%2C%22twenty%22%2C%22thirty%22%2C%22forty%22%2C%22fifty%22%2C%22sixty%22%2C%22seventy%22%2C%22eighty%22%2C%22ninety%22)%26amp%3B%22%20cents%22%2C%22%22)%26amp%3BCONCATENATE(CHOOSE(MID(TEXT(INT(LEFT(TRIM(MID(SUBSTITUTE(A1%2C%22.%22%2CREPT(%22%20%22%2C255))%2C255%2C200))%2C2))%2CREPT(0%2C12))%2C11%2C1)%2B1%2C%22%22%2CCHOOSE(MID(TEXT(INT(LEFT(TRIM(MID(SUBSTITUTE(A1%2C%22.%22%2CREPT(%22%20%22%2C255))%2C255%2C200))%2C2))%2CREPT(0%2C12))%2C12%2C1)%2B1%2C%22ten%22%2C%22eleven%22%2C%22twelve%22%2C%22thirteen%22%2C%22fourteen%22%2C%22fifteen%22%2C%22sixteen%22%2C%22seventeen%22%2C%22eighteen%22%2C%22nineteen%22)%26amp%3B%22%20cents%22%2C%22twent%0Ay%22%2C%22thirty%22%2C%22forty%22%2C%22fifty%22%2C%22sixty%22%2C%22seventy%22%2C%22eighty%22%2C%22ninety%22)%2CIF(VALUE(MID(TEXT(INT(LEFT(TRIM(MID(SUBSTITUTE(A1%2C%22.%22%2CREPT(%22%20%22%2C255))%2C255%2C200))%2C2))%2CREPT(0%2C12))%2C11%2C1))%26gt%3B1%2CCHOOSE(MID(TEXT(INT(LEFT(TRIM(MID(SUBSTITUTE(A1%2C%22.%22%2CREPT(%22%20%22%2C255))%2C255%2C200))%2C2))%2CREPT(0%2C12))%2C12%2C1)%2B1%2C%22%22%2C%22-one%22%2C%22-two%22%2C%22-three%22%2C%22-four%22%2C%22-five%22%2C%22-six%22%2C%22-seven%22%2C%22-eight%22%2C%22-nine%22)%26amp%3B%22%20cents%22%2CIF(LEFT(TRIM(MID(SUBSTITUTE(A1%2C%22.%22%2CREPT(%22%20%22%2C255))%2C255%2C200))%2C2)%3D%2200%22%2C%22zero%20cent%22%2CIF(LEFT(TRIM(MID(SUBSTITUTE(A1%2C%22.%22%2CREPT(%22%20%22%2C255))%2C255%2C200))%2C2)%3D%2201%22%2C%22one%20cent%22%2CIF(LEFT(TRIM(MID(SUBSTITUTE(A1%2C%22.%22%2CREPT(%22%20%22%2C255))%2C255%2C200))%2C1)%3D%220%22%2CCHOOSE(MID(TEXT(INT(LEFT(TRIM(MID(SUBSTITUTE(A1%2C%22.%22%2CREPT(%22%20%22%2C255))%2C255%2C200))%2C2))%2CREPT(0%2C12))%2C12%2C1)%2B1%2C%22%22%2C%22one%22%2C%22two%22%2C%22three%22%2C%22four%22%2C%22five%22%2C%22six%22%2C%22seven%22%2C%22eight%22%2C%22nine%22)%26amp%3B%22%20cents%22%2C%22%22))))))))))%3C%2FPRE%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-1220007%22%20slang%3D%22en-US%22%3ERe%3A%20How%20to%20CONVERT%20Numbers%20to%20Words%20with%20Built-in%20Excel%20Formula%3F%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1220007%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F24499%22%20target%3D%22_blank%22%3E%40Jamil%20Mohammad%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%3CP%3EHi%20dear%20sir%20%2C%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EI%20am%20very%20happy%20to%20see%20your%20formula%20about%20Numbers%20to%20Words%20converter.%20from%20long%20time%20i%20was%20searching%20it.%20I%20use%20your%20formula%20and%20its%20working%20fine.%20But%20there%20is%20a%20little%20problem.%20I%20am%20living%20in%20Bahrain.%20I%20already%20choose%20Bahraini%20Dinar%20to%20small%20currency%20fils.%20But%20fils%20if%20I%20write%20in%20cell%2C%20for%20example%20450.350%20BD%20.%20It%20should%20be%20four%20hundred%20and%20fifty%20BD%20and%20three%20hundred%20fifty%20fils.%20But%20your%20formula%20said%20four%20hundred%20and%20fifty%20BD%20and%20thirty%20five%20fils.%26nbsp%3B%20But%203rd%20decimal%20Place%20your%20formula%20is%20not%20calculating.%20Can%20you%20help%20me%20please%20%3F%20Waiting%20for%20your%20reply.%20Thanks%20If%20you%20can%20contact%20me%20on%20my%20whats-app%20it%20will%20be%20also%20great%20.%20%2B97338103220.%20Waiting%20for%20your%20reply.%20Thanks%26nbsp%3B%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EBest%20Regards%26nbsp%3B%3C%2FP%3E%3CP%3EAzeem%26nbsp%3B%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-1220019%22%20slang%3D%22en-US%22%3ERe%3A%20How%20to%20CONVERT%20Numbers%20to%20Words%20with%20Built-in%20Excel%20Formula%3F%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1220019%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F122716%22%20target%3D%22_blank%22%3E%40Flora%20Sim%3C%2FA%3E%26nbsp%3BNot%20what%20you're%20looking%20for%2C%20but%20a%20good%20piece%20of%20Excel%20trivia%3A%20This%20functionality%20is%20built-in%2C%20but%20only%20for%20Thai%20Baht%3A%20%3DBAHTTEXT.%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-1220024%22%20slang%3D%22en-US%22%3ERe%3A%20How%20to%20CONVERT%20Numbers%20to%20Words%20with%20Built-in%20Excel%20Formula%3F%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1220024%22%20slang%3D%22en-US%22%3E%3CP%3EHi%20dear%20sir%2C%20i%20already%20changed%20Dollar%20to%20Bahraini%20Dinar%20%2C%20but%20this%20is%20work%20for%202%20decimal%20numbers%20%2C%20i%20want%203%20decimal%20numbers%20.%20Because%20Bahrain%20Currency%20have%20one%20thousand%20fills%20then%20its%20equal%20to%201%20Bahrain%20Dinar.%20If%20i%20write%200.530%20BD%20converted%20to%20words%20cell%20fifty%20three%20fills%20.%20It%20should%20be%20five%20hundred%20thirty%20fils.%20I%20hope%20you%20understand%20dear%20sir%20.%20Please%20help%20me%20.%20Thanks%26nbsp%3B%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-1220045%22%20slang%3D%22en-US%22%3ERe%3A%20How%20to%20CONVERT%20Numbers%20to%20Words%20with%20Built-in%20Excel%20Formula%3F%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1220045%22%20slang%3D%22en-US%22%3ECan%20you%20describe%20what%20is%20the%20decimal%20separator%20in%20your%20Excel%3F%3CBR%20%2F%3E%3CBR%20%2F%3EI%20mean%20for%20dollar%20it%20is%20.%20is%20it%20comma%20%2C%20for%20your%20computer%3F%3CBR%20%2F%3E%3CBR%20%2F%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-1220057%22%20slang%3D%22en-US%22%3ERe%3A%20How%20to%20CONVERT%20Numbers%20to%20Words%20with%20Built-in%20Excel%20Formula%3F%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1220057%22%20slang%3D%22en-US%22%3EYou%20need%20to%20change%20your%20computer%20regional%20setting.%20because%20from%20what%20you%20describe%2C%20it%20appears%20that%20your%20computer%20regional%20setting%20is%20recognizing%20dot%20as%20thousand%20separator%20and%20comma%20as%20decimal%20points.%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-1220190%22%20slang%3D%22en-US%22%3ERe%3A%20How%20to%20CONVERT%20Numbers%20to%20Words%20with%20Built-in%20Excel%20Formula%3F%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1220190%22%20slang%3D%22en-US%22%3EOk.%20I%20will%20reply%20later%20today.%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-1220059%22%20slang%3D%22en-US%22%3ERe%3A%20How%20to%20CONVERT%20Numbers%20to%20Words%20with%20Built-in%20Excel%20Formula%3F%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1220059%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F24499%22%20target%3D%22_blank%22%3E%40Jamil%20Mohammad%3C%2FA%3E%26nbsp%3BHi%20sir%20%2C%20I%20just%20copy%20past%20your%20formula%20%2C%20and%20just%20go%20to%20Formula%20and%20change%20all%20dollars%20in%20BD%20and%20Cents%20in%20to%20Fils%20.%20I%20not%20change%20any%20comma%20.%20Can%20you%20check%20and%20help%20me%20or%20just%20check%20your%20formula%20and%20put%20BD%20and%20fills.%20But%20fills%20should%20be%20in%203%20digits%20.%20Like%20we%20have%20one%20thousand%20fils%20in%20one%20Bahraini%20Dinar.%20Have%20you%20understand%20sir%20%3F%20Please%20reply.%20thanks%26nbsp%3B%3C%2FP%3E%3C%2FLINGO-BODY%3E
Occasional Contributor

I am writing cheque and invoices that must have the dollar amounts typed in words and it is hectic to type the numbers and then type words manually. For example $541,125.57  should convert to five hundred forty-one thousand one hundred twenty-five Dollars and fifty-seven Cents

 

I cannot use Macros/VBA, the only solution to work for me is built-in Excel formula or anything that would not use Macros.

 

Thanks.

17 Replies
Best Response confirmed by Flora Sim (Occasional Contributor)
Solution

I had seen this question quite often, from colleagues, Q&A forums etc, but while there were many UDF solutions, there wasn't any solution with Built-In Formula (at least not to my knowledge).  

You lucked out, as I had already written this Formula couple of years ago. 

 

Here is the formula.  I have also uploaded the file with formula.

 

It only has one limitation, It cannot convert more than number  $999,999,999,999.00  "Nine Hundred Ninety-Nine Billion Nine Hundred Ninety-Nine Million Nine Hundred Ninety-Nine Thousand Nine Hundred Ninety-Nine Dollars and Zero Cent"

 

But I think it will do the job for you.

A.png

 

=IF(A1<=0,"Referred Cell Must be Empty",IF(A1>10^12-0.01,"Exceeds Maximum which is 999999999999",TRIM(PROPER(SUBSTITUTE(CONCATENATE(CHOOSE(MID(TEXT(INT(A1),REPT(0,12)),1,1)+1,"","one hundred ","two hundred ","three hundred ","four hundred ","five hundred ","six hundred ","seven hundred ","eight hundred ","nine hundred "),CHOOSE(MID(TEXT(INT(A1),REPT(0,12)),2,1)+1,"",CHOOSE(MID(TEXT(INT(A1),REPT(0,12)),3,1)+1,"ten","eleven","twelve","thirteen","fourteen","fifteen","sixteen","seventeen","eighteen","nineteen"),"twenty","thirty","forty","fifty","sixty","seventy","eighty","ninety"),IF(VALUE(MID(TEXT(INT(A1),REPT(0,12)),2,1))>1,CHOOSE(MID(TEXT(INT(A1),REPT(0,12)),3,1)+1,"","-one","-two","-three","-four","-five","-six","-seven","-eight","-nine"),IF(VALUE(MID(TEXT(INT(A1),REPT(0,12)),2,1))=0,CHOOSE(MID(TEXT(INT(A1),REPT(0,12)),3,1)+1,"","one","two","three","four","five","six","seven","eight","nine"),"")),IF(A1>=10^9," billion ",""),CHOOSE(MID(TEXT(INT(A1),REPT(0,12)),4,1)+1,"","one hundred ","two hundred ","three hu
ndred ","four hundred ","five hundred ","six hundred ","seven hundred ","eight hundred ","nine hundred "),CHOOSE(MID(TEXT(INT(A1),REPT(0,12)),5,1)+1,"",CHOOSE(MID(TEXT(INT(A1),REPT(0,12)),6,1)+1,"ten","eleven","twelve","thirteen","fourteen","fifteen","sixteen","seventeen","eighteen","nineteen"),"twenty","thirty","forty","fifty","sixty","seventy","eighty","ninety"),IF(VALUE(MID(TEXT(INT(A1),REPT(0,12)),5,1))>1,CHOOSE(MID(TEXT(INT(A1),REPT(0,12)),6,1)+1,"","-one","-two","-three","-four","-five","-six","-seven","-eight","-nine"),IF(VALUE(MID(TEXT(INT(A1),REPT(0,12)),5,1))=0,CHOOSE(MID(TEXT(INT(A1),REPT(0,12)),6,1)+1,"","one","two","three","four","five","six","seven","eight","nine"),"")),IF(VALUE(MID(TEXT(INT(A1),REPT(0,12)),4,3))>0," million ",""),CHOOSE(MID(TEXT(INT(A1),REPT(0,12)),7,1)+1,"","one hundred ","two hundred ","three hundred ","four hundred ","five hundred ","six hundred ","seven hundred ","eight hundred ","nine hundred "),CHOOSE(MID(TEXT(INT(A1),REPT(0,12)),8,1)+1,"",CHOOSE(MID(TEXT(INT(A1),REPT(0,
12)),9,1)+1,"ten","eleven","twelve","thirteen","fourteen","fifteen","sixteen","seventeen","eighteen","nineteen"),"twenty","thirty","forty","fifty","sixty","seventy","eighty","ninety"),IF(VALUE(MID(TEXT(INT(A1),REPT(0,12)),8,1))>1,CHOOSE(MID(TEXT(INT(A1),REPT(0,12)),9,1)+1,"","-one","-two","-three","-four","-five","-six","-seven","-eight","-nine"),IF(VALUE(MID(TEXT(INT(A1),REPT(0,12)),8,1))=0,CHOOSE(MID(TEXT(INT(A1),REPT(0,12)),9,1)+1,"","one","two","three","four","five","six","seven","eight","nine"),"")),IF(VALUE(MID(TEXT(INT(A1),REPT(0,12)),7,3))," thousand ",""),CHOOSE(MID(TEXT(INT(A1),REPT(0,12)),10,1)+1,"","one hundred ","two hundred ","three hundred ","four hundred ","five hundred ","six hundred ","seven hundred ","eight hundred ","nine hundred "),CHOOSE(MID(TEXT(INT(A1),REPT(0,12)),11,1)+1,"",CHOOSE(MID(TEXT(INT(A1),REPT(0,12)),12,1)+1,"ten","eleven","twelve","thirteen","fourteen","fifteen","sixteen","seventeen","eighteen","nineteen"),"twenty","thirty","forty","fifty","sixty","seventy","eighty","ninety
"),IF(VALUE(MID(TEXT(INT(A1),REPT(0,12)),11,1))>1,CHOOSE(MID(TEXT(INT(A1),REPT(0,12)),12,1)+1,"","-one","-two","-three","-four","-five","-six","-seven","-eight","-nine"),IF(VALUE(MID(TEXT(INT(A1),REPT(0,12)),11,1))=0,CHOOSE(MID(TEXT(INT(A1),REPT(0,12)),12,1)+1,"","one","two","three","four","five","six","seven","eight","nine"),"")))," "," ")&IF(FLOOR(A1,1)>1," dollars"," ")&IF(AND(A1>=1,A1<2),"dollar",""))&" and "&IF(ISERROR(FIND(".",A1)),"Zero Cent",PROPER(IF(LEN(LEFT(TRIM(MID(SUBSTITUTE(A1,".",REPT(" ",255)),255,200)),2))=1,CHOOSE(1*LEFT(TRIM(MID(SUBSTITUTE(A1,".",REPT(" ",255)),255,200)),2),"ten","twenty","thirty","forty","fifty","sixty","seventy","eighty","ninety")&" cents","")&CONCATENATE(CHOOSE(MID(TEXT(INT(LEFT(TRIM(MID(SUBSTITUTE(A1,".",REPT(" ",255)),255,200)),2)),REPT(0,12)),11,1)+1,"",CHOOSE(MID(TEXT(INT(LEFT(TRIM(MID(SUBSTITUTE(A1,".",REPT(" ",255)),255,200)),2)),REPT(0,12)),12,1)+1,"ten","eleven","twelve","thirteen","fourteen","fifteen","sixteen","seventeen","eighteen","nineteen")&" cents","twent
y","thirty","forty","fifty","sixty","seventy","eighty","ninety"),IF(VALUE(MID(TEXT(INT(LEFT(TRIM(MID(SUBSTITUTE(A1,".",REPT(" ",255)),255,200)),2)),REPT(0,12)),11,1))>1,CHOOSE(MID(TEXT(INT(LEFT(TRIM(MID(SUBSTITUTE(A1,".",REPT(" ",255)),255,200)),2)),REPT(0,12)),12,1)+1,"","-one","-two","-three","-four","-five","-six","-seven","-eight","-nine")&" cents",IF(LEFT(TRIM(MID(SUBSTITUTE(A1,".",REPT(" ",255)),255,200)),2)="00","zero cent",IF(LEFT(TRIM(MID(SUBSTITUTE(A1,".",REPT(" ",255)),255,200)),2)="01","one cent",IF(LEFT(TRIM(MID(SUBSTITUTE(A1,".",REPT(" ",255)),255,200)),1)="0",CHOOSE(MID(TEXT(INT(LEFT(TRIM(MID(SUBSTITUTE(A1,".",REPT(" ",255)),255,200)),2)),REPT(0,12)),12,1)+1,"","one","two","three","four","five","six","seven","eight","nine")&" cents",""))))))))))

 

Hi Flora,

 

In fact, there is no built-in function to do that.

I do not think this can easily be achieved using a formula.

But always there is a solution with VBA/Macros.

 

Please let us know why you can not use the VBA/Macros?
Is it disabled on your computer?

Wow!

THANK YOU SO VERY MUCH!
Haytham,
Due to corporate domain environment administration setting, macros are blocked by default in our office domain.

I got an excellent solution with built-in formulas provided by Jamil Mohammad.
Thanks

Brilliant! Just replace Dollar to your own currency and you are good to go. This is amazing work.

@Jamil Mohammad 

Hi dear sir ,

 

I am very happy to see your formula about Numbers to Words converter. from long time i was searching it. I use your formula and its working fine. But there is a little problem. I am living in Bahrain. I already choose Bahraini Dinar to small currency fils. But fils if I write in cell, for example 450.350 BD . It should be four hundred and fifty BD and three hundred fifty fils. But your formula said four hundred and fifty BD and thirty five fils.  But 3rd decimal Place your formula is not calculating. Can you help me please ? Waiting for your reply. Thanks If you can contact me on my whats-app it will be also great . +97338103220. Waiting for your reply. Thanks  

 

Best Regards 

Azeem 

@Flora Sim Not what you're looking for, but a good piece of Excel trivia: This functionality is built-in, but only for Thai Baht: =BAHTTEXT.

Hi dear sir, i already changed Dollar to Bahraini Dinar , but this is work for 2 decimal numbers , i want 3 decimal numbers . Because Bahrain Currency have one thousand fills then its equal to 1 Bahrain Dinar. If i write 0.530 BD converted to words cell fifty three fills . It should be five hundred thirty fils. I hope you understand dear sir . Please help me . Thanks 

Can you describe what is the decimal separator in your Excel?

I mean for dollar it is . is it comma , for your computer?

You need to change your computer regional setting. because from what you describe, it appears that your computer regional setting is recognizing dot as thousand separator and comma as decimal points.

@Jamil Mohammad Hi sir , I just copy past your formula , and just go to Formula and change all dollars in BD and Cents in to Fils . I not change any comma . Can you check and help me or just check your formula and put BD and fills. But fills should be in 3 digits . Like we have one thousand fils in one Bahraini Dinar. Have you understand sir ? Please reply. thanks 

Ok. I will reply later today.

@Azeem143 

 

I have written another formula to suit your needs.  also in the attached workbook.

 

=IF(A1<=0,"Referred Cell Must be Empty",IF(A1>10^12-0.01,"Exceeds Maximum which is 999999999999",TRIM(PROPER(SUBSTITUTE(CONCATENATE(CHOOSE(MID(TEXT(INT(A1),REPT(0,12)),1,1)+1,"","one hundred ","two hundred ","three hundred ","four hundred ","five hundred ","six hundred ","seven hundred ","eight hundred ","nine hundred "),CHOOSE(MID(TEXT(INT(A1),REPT(0,12)),2,1)+1,"",CHOOSE(MID(TEXT(INT(A1),REPT(0,12)),3,1)+1,"ten","eleven","twelve","thirteen","fourteen","fifteen","sixteen","seventeen","eighteen","nineteen"),"twenty","thirty","forty","fifty","sixty","seventy","eighty","ninety"),IF(A1>=10^9," billion ",""),CHOOSE(MID(TEXT(INT(A1),REPT(0,12)),4,1)+1,"","one hundred ","two hundred ","three hu
ndred ","four hundred ","five hundred ","six hundred ","seven hundred ","eight hundred ","nine hundred "),CHOOSE(MID(TEXT(INT(A1),REPT(0,12)),5,1)+1,"",CHOOSE(MID(TEXT(INT(A1),REPT(0,12)),6,1)+1,"ten","eleven","twelve","thirteen","fourteen","fifteen","sixteen","seventeen","eighteen","nineteen"),"twenty","thirty","forty","fifty","sixty","seventy","eighty","ninety"),IF(VALUE(MID(TEXT(INT(A1),REPT(0,12)),5,1))>1,CHOOSE(MID(TEXT(INT(A1),REPT(0,12)),6,1)+1,"","-one","-two","-three","-four","-five","-six","-seven","-eight","-nine"),IF(VALUE(MID(TEXT(INT(A1),REPT(0,12)),5,1))=0,CHOOSE(MID(TEXT(INT(A1),REPT(0,12)),6,1)+1,"","one","two","three","four","five","six","seven","eight","nine"),"")),IF(VALUE(MID(TEXT(INT(A1),REPT(0,12)),4,3))>0," million ",""),CHOOSE(MID(TEXT(INT(A1),REPT(0,12)),7,1)+1,"","one hundred ","two hundred ","three hundred ","four hundred ","five hundred ","six hundred ","seven hundred ","eight hundred ","nine hundred "),CHOOSE(MID(TEXT(INT(A1),REPT(0,12)),8,1)+1,"",CHOOSE(MID(TEXT(INT(A1),REPT(0,
12)),9,1)+1,"ten","eleven","twelve","thirteen","fourteen","fifteen","sixteen","seventeen","eighteen","nineteen"),"twenty","thirty","forty","fifty","sixty","seventy","eighty","ninety"),IF(VALUE(MID(TEXT(INT(A1),REPT(0,12)),8,1))>1,CHOOSE(MID(TEXT(INT(A1),REPT(0,12)),9,1)+1,"","-one","-two","-three","-four","-five","-six","-seven","-eight","-nine"),IF(VALUE(MID(TEXT(INT(A1),REPT(0,12)),8,1))=0,CHOOSE(MID(TEXT(INT(A1),REPT(0,12)),9,1)+1,"","one","two","three","four","five","six","seven","eight","nine"),"")),IF(VALUE(MID(TEXT(INT(A1),REPT(0,12)),7,3))," thousand ",""),CHOOSE(MID(TEXT(INT(A1),REPT(0,12)),10,1)+1,"","one hundred ","two hundred ","three hundred ","four hundred ","five hundred ","six hundred ","seven hundred ","eight hundred ","nine hundred "),CHOOSE(MID(TEXT(INT(A1),REPT(0,12)),11,1)+1,"",CHOOSE(MID(TEXT(INT(A1),REPT(0,12)),12,1)+1,"ten","eleven","twelve","thirteen","fourteen","fifteen","sixteen","seventeen","eighteen","nineteen"),"twenty","thirty","forty","fifty","sixty","seventy","eighty","ninety
"),IF(VALUE(MID(TEXT(INT(A1),REPT(0,12)),11,1))>1,CHOOSE(MID(TEXT(INT(A1),REPT(0,12)),12,1)+1,"","-one","-two","-three","-four","-five","-six","-seven","-eight","-nine"),IF(VALUE(MID(TEXT(INT(A1),REPT(0,12)),11,1))=0,CHOOSE(MID(TEXT(INT(A1),REPT(0,12)),12,1)+1,"","one","two","three","four","five","six","seven","eight","nine"),"")))," "," ")&IF(FLOOR(A1,1)>1," BDs"," ")&IF(AND(A1>=1,A1<2),"BD",""))&" and "&CONCATENATE(CHOOSE(MID(TEXT(INT(TRIM(MID(SUBSTITUTE(A1,".",REPT(" ",255)),255,200))),REPT(0,12)),10,1)+1,"","one hundred ","two hundred ","three hundred ","four hundred ","five hundred ","six hundred ","seven hundred ","eight hundred ","nine hundred "),CHOOSE(MID(TEXT(INT(TRIM(MID(SUBSTITUTE(A1,".",REPT(" ",255)),255,200))),REPT(0,12)),11,1)+1,"",CHOOSE(MID(TEXT(INT(TRIM(MID(SUBSTITUTE(TRIM(MID(SUBSTITUTE(A1,".",REPT(" ",255)),255,200)),".",REPT(" ",255)),255,200))),REPT(0,12)),12,1)+1,"ten","eleven","twelve","thirteen","fourteen","fifteen","sixteen","seventeen","eighteen","nineteen"),"twenty","thirty","forty","fifty","sixty","seventy","eighty","ninety
"),IF(VALUE(MID(TEXT(INT(TRIM(MID(SUBSTITUTE(A1,".",REPT(" ",255)),255,200))),REPT(0,12)),11,1))>1,CHOOSE(MID(TEXT(INT(TRIM(MID(SUBSTITUTE(A1,".",REPT(" ",255)),255,200))),REPT(0,12)),12,1)+1,"","-one","-two","-three","-four","-five","-six","-seven","-eight","-nine"),IF(VALUE(MID(TEXT(INT(TRIM(MID(SUBSTITUTE(A1,".",REPT(" ",255)),255,200))),REPT(0,12)),11,1))=0,CHOOSE(MID(TEXT(INT(TRIM(MID(SUBSTITUTE(A1,".",REPT(" ",255)),255,200))),REPT(0,12)),12,1)+1,"","one","two","three","four","five","six","seven","eight","nine"),"")))&IF(FLOOR(TRIM(MID(SUBSTITUTE(A1,".",REPT(" ",255)),255,200)),1)>1," fils"," ")&IF(AND(TRIM(MID(SUBSTITUTE(A1,".",REPT(" ",255)),255,200))>=1,TRIM(MID(SUBSTITUTE(A1,".",REPT(" ",255)),255,200))<2),"Fil",""))))

@Jamil Mohammad Hi dear sir , thanks so much for your great help. First time i am using a comunity and its help me a lot. I am really happy dear sir  . God bless you so much . Good luck in your whole life sir , Amen. thanks again. Best regards Azeem 

Hi dear sir ,

 

So sorry to disturb you again, This formula you make for me , is very good and working perfect . But there is a little issue, if i write , 35.251 BD then its result is thirty five BD and two hundred fifty one fils. But if i write 35.250 BD then the result is thirty five BD and twenty five fils . This result should be thirty five BD and two hundred fifty fils . Can you check and help me again please . You are helping me a lot , thanks so so so much dear sir . 

 

Best Regards 

Azeem  

@Azeem143 Hi dear sir , there is another issue also in the formula. If I write simple 450 BD , there is #VALUE! ... If i write 450.225 then result is Four Hundred Fifty Bds and Two Hundred Twenty-Five fils.  Do you understand dear sir . Please reply. Waiting for your reply. Thanks 

 

Regards 

Azeem

@Azeem143 

 

Hi, I modified the formula, because the formula length became too long, so I had to use a helper cell in column c for splitting the formula into half and then use C1 into the B1 formula as a reference.

 

please see attached.