Forum Discussion
Displaying Numbers in Words
- 6 years ago
Here is a more concise version that can also be easily extended to more digit triplets, since the lookup table will be the same for each of the triplets.
Spelt2 = var l = {("1","one","eleven","ten"),("2","two","twelve","twenty"),("3","three","thirteen","thirty"), ("4","four","fourteen","fourty"),("5","five","fifteen","fifty"),("6","six","sixteen","sixty"), ("7","seven","seventeen","seventy"),("8","eight","eighteen","eighty"),("9","nine","nineteen","ninety")} var t = "0" & format(ParameterNumber[ParameterNumber Value],"#") var s = right(t,1) var d = left(right(t,2),1) var h = left(right(t,3),1) var sd = if(d="1",concatenatex(filter(l,[Value1]=s),[Value3]),concatenatex(filter(l,[Value1]=s),[Value2])) var dd = if(d>"1" || d & s = "10",concatenatex(filter(l,[Value1]=d),[Value4]) & " ") var hd = if(h>"0",concatenatex(filter(l,[Value1]=h),[Value2]) & " hundred ") return hd & dd & sdHere's the version for up to six digits:
Spelt2 = var l = {("1","one","eleven","ten"),("2","two","twelve","twenty"),("3","three","thirteen","thirty"), ("4","four","fourteen","fourty"),("5","five","fifteen","fifty"),("6","six","sixteen","sixty"), ("7","seven","seventeen","seventy"),("8","eight","eighteen","eighty"),("9","nine","nineteen","ninety")} var t = "0" & format(ParameterNumber[ParameterNumber Value],"#") var s = right(t,1) var d = left(right(t,2),1) var h = left(right(t,3),1) var ss = if(d="1",concatenatex(filter(l,[Value1]=s),[Value3]),concatenatex(filter(l,[Value1]=s),[Value2])) var dd = if(d>"1" || d & s = "10",concatenatex(filter(l,[Value1]=d),[Value4]) & " ") var hd = if(h>"0",concatenatex(filter(l,[Value1]=h),[Value2]) & " hundred ") var s2 = left(right(t,4),1) var d2 = left(right(t,5),1) var h2 = left(right(t,6),1) var ss2 = if(d2="1",concatenatex(filter(l,[Value1]=s2),[Value3]),concatenatex(filter(l,[Value1]=s2),[Value2])) var dd2 = if(d2>"1" || d2 & s2 = "10",concatenatex(filter(l,[Value1]=d2),[Value4]) & " ") var hd2 = if(h2>"0",concatenatex(filter(l,[Value1]=h2),[Value2]) & " hundred ") return if(ss2>"",hd2 & dd2 & ss2 & " thousand ") & hd & dd & ssIn action:
Here is a more concise version that can also be easily extended to more digit triplets, since the lookup table will be the same for each of the triplets.
Spelt2 =
var l = {("1","one","eleven","ten"),("2","two","twelve","twenty"),("3","three","thirteen","thirty"),
("4","four","fourteen","fourty"),("5","five","fifteen","fifty"),("6","six","sixteen","sixty"),
("7","seven","seventeen","seventy"),("8","eight","eighteen","eighty"),("9","nine","nineteen","ninety")}
var t = "0" & format(ParameterNumber[ParameterNumber Value],"#")
var s = right(t,1)
var d = left(right(t,2),1)
var h = left(right(t,3),1)
var sd = if(d="1",concatenatex(filter(l,[Value1]=s),[Value3]),concatenatex(filter(l,[Value1]=s),[Value2]))
var dd = if(d>"1" || d & s = "10",concatenatex(filter(l,[Value1]=d),[Value4]) & " ")
var hd = if(h>"0",concatenatex(filter(l,[Value1]=h),[Value2]) & " hundred ")
return hd & dd & sd
Here's the version for up to six digits:
Spelt2 =
var l = {("1","one","eleven","ten"),("2","two","twelve","twenty"),("3","three","thirteen","thirty"),
("4","four","fourteen","fourty"),("5","five","fifteen","fifty"),("6","six","sixteen","sixty"),
("7","seven","seventeen","seventy"),("8","eight","eighteen","eighty"),("9","nine","nineteen","ninety")}
var t = "0" & format(ParameterNumber[ParameterNumber Value],"#")
var s = right(t,1)
var d = left(right(t,2),1)
var h = left(right(t,3),1)
var ss = if(d="1",concatenatex(filter(l,[Value1]=s),[Value3]),concatenatex(filter(l,[Value1]=s),[Value2]))
var dd = if(d>"1" || d & s = "10",concatenatex(filter(l,[Value1]=d),[Value4]) & " ")
var hd = if(h>"0",concatenatex(filter(l,[Value1]=h),[Value2]) & " hundred ")
var s2 = left(right(t,4),1)
var d2 = left(right(t,5),1)
var h2 = left(right(t,6),1)
var ss2 = if(d2="1",concatenatex(filter(l,[Value1]=s2),[Value3]),concatenatex(filter(l,[Value1]=s2),[Value2]))
var dd2 = if(d2>"1" || d2 & s2 = "10",concatenatex(filter(l,[Value1]=d2),[Value4]) & " ")
var hd2 = if(h2>"0",concatenatex(filter(l,[Value1]=h2),[Value2]) & " hundred ")
return if(ss2>"",hd2 & dd2 & ss2 & " thousand ") & hd & dd & ss
In action:
Hi Sir,
We have decimals also, we want to convert amount to words along with decimals based on CCY
ex. INR 50.25 = Fifty rupees and twenty five paisa
USD 50.25 = Fifty dollars and twenty five cent
INR 50000.25 = Fifty thousands twenty five paisa
INR 565786.50 = Five lakh sixty five thousand seven hundred eight six fifty paisa
this should be inline with CCY like lakh, crore for INR .. million, billion for USD (as applicable)
Please help me the code.
- lbendlin4 years ago
Super User
Anonymous please provide more samples, especially with crore and lakh sized values. Please indicate if "and" is required before the paisa.
Attached is aproposal to handle the Indian number format. Please validate.
- Anonymous4 years agoNot applicable
Yes "and" is required before paisa.
Please find the below samples.
5,75,786.56 -- Five lakh seventy five thousand seven hundred eighty six Rupees and fifty-six Paisa
89,75,88,789.25 --- Eighty Nine core seventy five lakhs eighty eight thousand seven hundred eighty nine Rupees and twenty five paisa.
I will validate the PBIX and confirm you sir.
- Anonymous4 years agoNot applicable
Examples :-
5,75,786.56 -- Five lakh seventy five thousand seven hundred eighty six Rupees and fifty-six Paisa
89,75,88,789.25 --- Eighty Nine core seventy five lakhs eighty eight thousand seven hundred eighty nine Rupees and twenty five paisa.
USD 1,025,300.25 = One Million Twenty Five Thousand Three Hundred Dollars and Twenty-Five Cents
INR 10,25,300.25 = Ten Lakh Twenty Five Thousand Three Hundred Rupees and Twenty-Five Paisa Only