Currency conversion number to words excel

0 votes

I want to put the Kuwaiti riyal's three decimal places of value into English in an Excel document. Here is the formula I used:

=if(or(isBlank($E$89),not(isNumber($E$89)),$E$89>=power(10,15)),ifError(1/0),trim(arrayFormula(concatenate(if(trunc(mod($E$89,power(10,15,12,9,6,3}))/power(10,{12,9,6,3,0}))<100,"",switch(int(trunc(mod($E$89,power(10,{15,12,9,6,3}))/power(10,{12,9,6,3,0}))/100),1," one",2," two",3," three",4," four",5," five",6," six",7," seven",8," eight",9," nine") & " hundred") & if(mod(trunc(mod($E$89,power(10,{15,12,9,6,3}))/power(10,{12,9,6,3,0})),100)<>0,if(trunc(mod($E$89,power(10,{15,12,9,6,3}))/power(10,{12,9,6,3,0}))>100," and",if($E$89>power(10,{15,12,9,6,3}),switch({1,2,3,4,5},5," and"),"")),"") & if(mod(trunc(mod($E$89,power(10,{15,12,9,6,3}))/power(10,12,9,6,3,0})),100)=0,"",if(mod(trunc(mod($E$89,power(10,{15,12,9,6,3}))/power(10,{12,9,6,3,0})),100)<20,IFERROR(switch(mod(trunc(mod($E$89,power(10,{15,12,9,6,3}))/power(10,{12,9,6,3,0})),100),1," one",2," two",3," three",4," four",5," five",6," six",7," seven",8," eight",9," nine",10," ten",11," eleven",12," twelve",13," thirteen",14," fourteen",15," fifteen",16," sixteen",17," seventeen",18," eighteen",19," nineteen")),IFERROR(switch(int(mod(trunc(mod($E$89,power(10,{15,12,9,6,3}))/power(10,{12,9,6,3,0})),100)/10),2," twenty",3," thirty",4," forty",5," fifty",6," sixty",7," seventy",8," eighty",9," ninety")) & if(mod(mod(trunc(mod($E$89,power(10,{15,12,9,6,3}))/power(10,12,9,6,3,0})),100),10)=0,"","-" & IFERROR(switch(mod(mod(trunc(mod($E$89,power(10,{15,12,9,6,3}))/power(10,{12,9,6,3,0})),100),10),1,"one",2,"two",3,"three",4,"four",5,"five",6,"six",7,"seven",8,"eight",9,"nine"))))) & if(trunc(mod($E$89,power(10,{15,12,9,6,3}))/power(10,{12,9,6,3,0}))=0,"",IFERROR(switch({1,2,3,4,5},1," trillion",2," billion",3," million",4," thousand"))))) & if($E$89>=2," Dinar only",if($E$89>=1," Dinar only","")) & if((round($E$89-trunc($E$89),3)*1000=0)+($E$89<1),""," and") & switch(trunc(round($E$89-trunc($E$89),3)*1000,-2)/100,1," one-hundred",2," two",3,-hundred" three-hundred",4," four-hundred",5," five-hundred",6," six-hundred",7," seven-hundred",8," eight-hundred",9," nine-hundred"& if(round($E$89-trunc($E$89),3)*1000-trunc(round($E$89-trunc($E$89),3)*1000,-2)<20,switch(round($E$89-trunc($E$89),3)*1000-trunc(round($E$89-trunc($E$89),3)*1000,-1),0,"",1," one",2," two",3," three",4," four",5," five",6," six",7," seven",8," eight",9," nine",10," ten",11," eleven",12," twelve",13," thirteen",14," fourteen",15," fifteen",16," sixteen",17," seventeen",18," eighteen",19," nineteen"),switch((round($E$89-trunc($E$89),3)*1000-trunc(round($E$89-trunc($E$89),3)*1000,-2))/10,2," twenty",3," thirty",4," forty",5," fifty",6," sixty",7," seventy",8," eighty",9," ninety") & if(mod(round($E$89-trunc($E$89),3)*1000,10)=0,"","-" & switch(round($E$89-trunc($E$89),3)*1000-trunc(round($E$89-trunc($E$89),3)*1000,-1),0"",1,"one",2,"two",3,"three",4,"four",5,"five",6,"six",7,"seven",8,"eight",9,"nine"))) & " fils only")))

The error shown is

Formula parse error

For a given input:

111.250

I would expect to get the output as :

One hundred and eleven Dinar and two hundred fifty fils only.

Oct 28 in Others by Kithuzzz
• 20,660 points
45 views

1 answer to this question.

0 votes

Try looking for javascript solutions to use them as a custom functions.

Ref: https://stackoverflow.com/a/37409158/5372400

answered Oct 28 by narikkadan
• 37,660 points

Related Questions In Others

0 votes
1 answer

csv to excel conversion

So Basically u have to follow few ...READ MORE

answered Feb 9 in Others by gaurav
• 22,040 points
68 views
0 votes
1 answer

Conversion of PDF file to Excel in R

I looked at the pdf, and it ...READ MORE

answered Oct 16 in Others by narikkadan
• 37,660 points
73 views
0 votes
1 answer
0 votes
1 answer
0 votes
1 answer

How to split text values by a delimiter?

The Split function is what you are looking for: =Split(A1, ...READ MORE

answered Oct 11 in Others by narikkadan
• 37,660 points
78 views
0 votes
1 answer

Calculate Birthdate from an age using y,m,d in Excel

Hello, yes u can find your birthdate using ...READ MORE

answered Feb 16 in Others by Edureka
• 13,640 points
142 views
0 votes
1 answer

Calculate Birthdate from an age using y,m,d in Excel

Hi To Calculate the date, we can ...READ MORE

answered Feb 16 in Others by Edureka
• 13,640 points
242 views
0 votes
0 answers

Convert Rows to Columns with values in Excel using custom format

1 I having a Excel sheet with 1 ...READ MORE

Feb 17 in Others by Edureka
• 13,640 points
109 views
0 votes
1 answer

Excel automatically converting 7 digit CAS number to another number (date?)

Looks like you could use: The formula in D2: =SUBSTITUTE(F ...READ MORE

answered Sep 25 in Others by narikkadan
• 37,660 points
61 views
0 votes
1 answer

Convert text to number in Google Sheets & Excel

Try: =INDEX(IF(REGEXMATCH(A1:A4&""; "M"); REGEXEXTRACT(A1:A4; "\d+.\d+|\d+")*1000000; IF(REGEXMATCH(A1:A4&""; "k"); ...READ MORE

answered Oct 15 in Others by narikkadan
• 37,660 points
65 views
webinar REGISTER FOR FREE WEBINAR X
REGISTER NOW
webinar_success Thank you for registering Join Edureka Meetup community for 100+ Free Webinars each month JOIN MEETUP GROUP