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, 2022 in Others 335 views

1 answer to this question.

0 votes

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

answered Oct 28, 2022 by
• 63,420 points

0 votes
1 answer

csv to excel conversion

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

0 votes
1 answer

Conversion of PDF file to Excel in R

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

0 votes
1 answer

Excel function for divide or split number to maximum possible equal parts

The underlying math for this is as ...READ MORE

0 votes
1 answer

How to format numbers as lakhs and crores in excel/ google spreadsheet when the number could be negative too?

Excel formatting, in my opinion, can only ...READ MORE

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

+1 vote
1 answer

Excel or Google formula to count occurrences of an 8-digit number within a text string

To match an eight-digit number, you may ...READ MORE

0 votes
1 answer

Filter/Extract a text from excel sheet having similar values

ISNUMBER(SEARCH("Maria,",SUBSTITUTE(A1:A4,"]",",")))  shows TRUE if Maria is found and false if it would ...READ MORE

+1 vote
1 answer

Automatically move an entire row of reference cell when one cell is moved or manipulated

You are describing how manually entered data ...READ MORE

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

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