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:
Hi,
I have been asked to produce something very similar, but also include the decimals as part of the text string. How would I be able to augment the dax measure code below to include the decimal values.
i.e. £1001.99
converts to One Thousand and One Pounds and Ninety Nine Pence
- lbendlin4 years ago
Super User
shouldn't that be "One Thousand One Pound and Ninety Nine Pence " ?
You will need to explain the (british) english rules a bit more.
- Anonymous4 years agoNot applicable
Yes, that is correct typo on my part. I've listed out below a list of examples which follow the convention used in English I think you mean by rules. If the unit value is 1 we use the singular Pound, once pound value is greater than 1 we use the plural (Pounds). Anything to the right of the decimal point is always pence. If I've mis-interpreted your ask please let me know.
Some simple rules:
£0.99 = Ninety Nine Pence
£1.00 = One Pound
£1.99 = One Pound and Ninety Nine Pence
£10.00 = Ten Pounds
£100.00 = One Hundred Pounds
£101.00 = One Hundred and One Pounds
£110.00 = One Hundred and Ten Pounds£1000.00 = One Thousand Pounds
£1001.00 = One Thousand and One Pounds
£1,010.00 = One Thousand and Ten Pounds
£1,100.00 = One Thousand One Hundred Pounds
£10,000.00 = Ten Thousand Pounds
£10,001.00 = Ten Thousand and One Pounds
£10,010.00 = Ten Thousand and Ten Pounds
£10,100.00 = Ten Thousand One Hundred Pounds
£10,101.00 = Ten Thousand One Hundred and One Pounds
£10,110.00 = Ten Thousand One Hundred and Ten Pounds
£100,000.00 = One Hundred Thousand Pounds
£100,001.00 = One Hundred Thousand and One Pounds
£100,010.00 = One Hundred Thousand and Ten Pounds
£100,100.00 = One Hundred Thousand and One Hundred Pounds
£100,110.00 = One Hundred Thousand One Hundred and Ten Pounds
£101,000.00 = One Hundred and One Thousand Pounds
£101,001.00 = One Hundred and One Thousand and One Pounds
£101,010.00 = One Hundred and One Thousand and Ten Pounds
£101,110.00 = One Hundred and One Thousand One Hundred and Ten Pounds
£110,000.00 = One Hundred and Ten Thousand Pounds
£1,000,000.00 = One Million Pounds
£1,000,001.00 = One Million and One Pounds
£1,000,010.00 = One Million and Ten Pounds
£1,000,100.00 = One Million and One Hundred Pounds
£1,000,110.00 = One Million One Hundred and Ten Pounds
£1,001,110.00 = One Million One Thousand One Hundred and Ten Pounds
£1,010,110.00 = One Million Ten Thousand One Hundred and Ten Pounds
£1,110,110.00 = One Million One Hundred and Ten Thousand One Hundred and Ten Pounds
Regards
JAD22
- lbendlin4 years ago
Super User
Sorry but this part
£100,100.00 = One Hundred Thousand and One Hundred Pounds
£100,110.00 = One Hundred Thousand One Hundred and Ten Pounds
makes no sense. Should be either - or. Can we compromise on
£100,100.00 = One Hundred Thousand One Hundred Pounds
£100,110.00 = One Hundred Thousand One Hundred and Ten Pounds
?