Number to word conversion tool

[darogael]

Currently there is no out-of-box feature available in PowerApps and Flow to convert a numerical value into word and hence this blog walks you through how to build one. This blog is an extension of Brian Dang's 'Expanded Word Form for place value' blog.

Scenario:
A scenario I came across was to take a numerical dollar value and convert that into a word form to be printed on pre-printed checks. Hence I created a screen which has the numerical value, the word form and the full name of the person. This blog only focuses on the numerical to word conversion piece.

Video:

Formulas:

Set(WholeNumberVar, First(Split(TextInput.Text,".")));If(!IsBlank(Find(".",TextInput.Text)),Set(DecimalNumberVar,Last(Split(TextInput.Text,"."))),"");

Clear(expandedForm);

// Create a table that separates each digit to include its Value and Position. Make a placeholder for its Place and Period.
ForAll(RenameColumns(Split(WholeNumberVar.Result,""),"Result","Char"),
Collect(expandedForm,
{
Value: Value(Char),
Position:
// Determine the position of each character as a difference between the length of the number and how many digits have already been collected.
Len(WholeNumberVar.Result)-CountRows(expandedForm),
Place: 0,
Period: 0
}
)
);

// Update the Place and Period using the Position.
ForAll(RenameColumns(expandedForm,"Position","Pos"),
Patch(expandedForm,LookUp(expandedForm,Pos=Position),
{
Place:
// Determine if a given column is a one, ten, or hundred based on its position.
Switch(true,
Mod(Pos-1,3)=0,1,
Mod(Pos+1,3)=0,10,
100
),
Period:
// Determine what Period a digit belongs to based on its position.
RoundUp(Pos/3,0)-1
}
)
)

If(IsBlank(Find(".",TextInput.Text)),
Concatenate(
Concat(
GroupBy(expandedForm,"Period","PeriodGroup"),

// Read the hundred in the period.
LookUp(PlaceValue_1,Digit=LookUp(PeriodGroup,Place=100).Value,Word & " ") &

// For 10-19, read the tens and ones together, otherwise separately.
If(LookUp(PeriodGroup,Place=10).Value=1,
LookUp(PlaceValue_1,Digit=Value(Concat(Filter(PeriodGroup,Place<>100),Text(Value))),Word & " "),
Concat(Filter(PeriodGroup,Place<>100),LookUp(PlaceValue_1,Digit=Value && Column=Place,Word & " "))
) &

// Read the period.
Coalesce(LookUp(Period_1,Group=Period,Name & ", "),"")),"dollars"),
Concatenate(
Concat(
GroupBy(expandedForm,"Period","PeriodGroup"),

// Read the hundred in the period.
LookUp(PlaceValue_1,Digit=LookUp(PeriodGroup,Place=100).Value,Word & " ") &

// For 10-19, read the tens and ones together, otherwise separately.
If(LookUp(PeriodGroup,Place=10).Value=1,
LookUp(PlaceValue_1,Digit=Value(Concat(Filter(PeriodGroup,Place<>100),Text(Value))),Word & " "),
Concat(Filter(PeriodGroup,Place<>100),LookUp(PlaceValue_1,Digit=Value && Column=Place,Word & " "))
) &

// Read the period.
Coalesce(LookUp(Period_1,Group=Period,Name & ", "),"")
),Concatenate("dollars & ",DecimalNumberVar.Result,"/100 cents")))

Here's what the expandedForm collection looks like

Conclusion:

As mentioned in the video this app is an extension of Brian Dang's app which uses Components. Attached is a zipped file contains a copy of both the Excel spreadsheet and the MSAPP file which you can use. 

NumerToTextConversion.zip

[zakirlko]

Very useful, thanks.

[ldskfklodsf]

@darogael this is great! However for 1 000 000 the wording comes out as one million thousand dollars rather than one million dollars. Been trying to troubleshoot the code with no luck yet.

[Anonymous]

The collection for expandForm is not given here and it is one of the steps that you overlooked in your video.

[MR_Malakooti]

While thanking @darogael for the code,

@ldskfklodsf  Yes, You are right! I wrote a simple script to correct the result phrase. Maybe it is not the best solution but it is fast.

Just, I removed the extra words! Even by following steps, it will be easier to understand my explanation.

You may add another "Label" to the screen and put the following code to the Text field.

Here is the code: 

//to remove extra ", million" & ", billion" & ...
Substitute(Substitute(Substitute(Substitute(Substitute(WordLabel.Text,", thousand,",""),", million",""),", billion",""),", trillion",""),", quadrillion","")

 Or 

//to remove extra ", million" & ", billion" & ...
Substitute(
    Substitute(
        Substitute(
            Substitute(
                Substitute(
                    WordLabel.Text,
                    ", thousand,",
                    ""
                ),
                ", million",
                ""
            ),
            ", billion",
            ""
        ),
        ", trillion",
        ""
    ),
    ", quadrillion",
    ""
)

Here is the result:

[Oluwaseyi]

Thanks @MR_Malakooti . The adjustment was very helpful. I need some help if you will be so kind in translating the figures after the periods to words as well. So in your example it will be one billion dollars and twenty five cents(1000000000.25)

[alexaristide]

Hello, I tried your app, is very useful..,

trying the input text.., I noted it is not working when I write the number 20, 22, 23, ....,

I already checked the excel file out, but I could not find any solution...,

please if you could tell us how to do that..,

thank you

[Mehta_Tech]

I made invoice system in power apps and i want to display word format in label but which field i want to convert in to word is also calculated field and your code is not work on it.

[Sherif-Abolsaud]

Hi Daniel,

I have two requests in this regard:

1- In power app, how to do this for amount field appearing in gallery?

2- If amount field is existing in the share point list, is there a way to convert it to text in a flow? so I can use 'update item' function to write the result back to the list item?

[rsadhula]

@darogael - I'm getting Lookup has invalid arguments. Am I missing anything here ?

[rsadhula]

I had removed excel file added again, it works fine

[Abayomi_Farinde]

Hello @darogael Great job  with the tutorial and solution. I was able to use it for my app and it works. I was wondering how I could get the decimalNumberVar in words as well. Like instead of '25/100 cents' I get 'twenty five cents' 

[Abdulsalamgarba]

this is a very good article it helps us solve our problem we appreciate it thanks  

[tbeerman6646]

I love this solution. One of the few solutions that works great stand-alone without much effort!  

I did find one thing that seems like a flaw. Any number input >99 with "20" doesn't recognize the 20. Seems like a small error, but I am unable to find it or fix it in your formula.

[Abayomi_Farinde]

Hi @tbeerman6646 You are right. It is a wonderful solution and there is that slight error.

You can correct the error by simply editing the place value sheet of the excel document at line 21. The number 20 should be 2 since the value is 10.

2*10 gives 20 to which would compute correctly as opposed to the current 20*10 which gives 200.

Hope this helps. 

[tbeerman6646]

Perfect!  That did the trick. Thanks!

[Adam-Artelia]

@Abayomi_Farinde So I cheated and borrowed this superb piece of work, I generated a new collection for the decimals, and I've made it all in pence, but here you go:

On Change

Set(WholeNumberVar, First(Split(DataCardValue124.Text,".")));If(!IsBlank(Find(".",DataCardValue124.Text)),Set(DecimalNumberVar,Last(Split(DataCardValue124.Text,"."))),"");

Clear(expandedForm);

// Create a table that separates each digit to include its Value and Position. Make a placeholder for its Place and Period.
ForAll(RenameColumns(Split(WholeNumberVar.Result,""),"Result","Char"),
Collect(expandedForm,
{
Value: Value(Char),
Position:
// Determine the position of each character as a difference between the length of the number and how many digits have already been collected.
Len(WholeNumberVar.Result)-CountRows(expandedForm),
Place: 0,
Period: 0
}
)
);

// Update the Place and Period using the Position.
ForAll(RenameColumns(expandedForm,"Position","Pos"),
Patch(expandedForm,LookUp(expandedForm,Pos=Position),
{
Place:
// Determine if a given column is a one, ten, or hundred based on its position.
Switch(true,
Mod(Pos-1,3)=0,1,
Mod(Pos+1,3)=0,10,
100
),
Period:
// Determine what Period a digit belongs to based on its position.
RoundUp(Pos/3,0)-1
}
)
);Clear(expandedForm_Decimal);

// Create a table that separates each digit to include its Value and Position. Make a placeholder for its Place and Period.
ForAll(RenameColumns(Split(DecimalNumberVar.Result,""),"Result","Char"),
Collect(expandedForm_Decimal,
{
Value: Value(Char),
Position:
// Determine the position of each character as a difference between the length of the number and how many digits have already been collected.
Len(DecimalNumberVar.Result)-CountRows(expandedForm_Decimal),
Place: 0,
Period: 0
}
)
);

// Update the Place and Period using the Position.
ForAll(RenameColumns(expandedForm_Decimal,"Position","Pos"),
Patch(expandedForm_Decimal,LookUp(expandedForm_Decimal,Pos=Position),
{
Place:
// Determine if a given column is a one, ten, or hundred based on its position.
Switch(true,
Mod(Pos-1,3)=0,1,
Mod(Pos+1,3)=0,10,
100
),
Period:
// Determine what Period a digit belongs to based on its position.
RoundUp(Pos/3,0)-1
}
)
)

Text to read as words

If(IsBlank(Find(".",DataCardValue124.Text)),
Concatenate(
Concat(
GroupBy(expandedForm,"Period","PeriodGroup"),

// Read the hundred in the period.
LookUp(PlaceValue_1,Digit=LookUp(PeriodGroup,Place=100).Value,Word & " ") &

// For 10-19, read the tens and ones together, otherwise separately.
If(LookUp(PeriodGroup,Place=10).Value=1,
LookUp(PlaceValue_1,Digit=Value(Concat(Filter(PeriodGroup,Place<>100),Text(Value))),Word & " "),
Concat(Filter(PeriodGroup,Place<>100),LookUp(PlaceValue_1,Digit=Value && Column=Place,Word & " "))
) &

// Read the period.
Coalesce(LookUp(Period_1,Group=Period,Name & " "),"")),"Pounds"),
Concatenate(
Concat(
GroupBy(expandedForm,"Period","PeriodGroup"),

// Read the hundred in the period.
LookUp(PlaceValue_1,Digit=LookUp(PeriodGroup,Place=100).Value,Word & " ") &

// For 10-19, read the tens and ones together, otherwise separately.
If(LookUp(PeriodGroup,Place=10).Value=1,
LookUp(PlaceValue_1,Digit=Value(Concat(Filter(PeriodGroup,Place<>100),Text(Value))),Word & " "),
Concat(Filter(PeriodGroup,Place<>100),LookUp(PlaceValue_1,Digit=Value && Column=Place,Word & " "))
) &

// Read the period.
Coalesce(LookUp(Period_1,Group=Period,Name & ", "),"")
),Concatenate("Pounds & ",Concat(
GroupBy(expandedForm_Decimal,"Period","PeriodGroup"),

// Read the period.
Coalesce(LookUp(Period_1,Group=Period,Name & " "),""))),
Concatenate(
Concat(
GroupBy(expandedForm_Decimal,"Period","PeriodGroup"),

// Read the hundred in the period.
LookUp(PlaceValue_1,Digit=LookUp(PeriodGroup,Place=100).Value,Word) &

// For 10-19, read the tens and ones together, otherwise separately.
If(LookUp(PeriodGroup,Place=10).Value=1,
LookUp(PlaceValue_1,Digit=Value(Concat(Filter(PeriodGroup,Place<>100),Text(Value))),Word & " "),
Concat(Filter(PeriodGroup,Place<>100),LookUp(PlaceValue_1,Digit=Value && Column=Place,Word & " "))
) &

// Read the period.
Coalesce(LookUp(Period_1,Group=Period,Name & ", "),"")
),"Pence")))

Fair warning, I may have butchered some of it in the process, but seems to be working for me.

[davidpiedra]

Hi, I translated your app to spanish. If you like to have it or post it in Spanish, just message me.

Thanks for your effort on developing this code, was very helpful to me.

[JorgeSanM]

Hi @davidpiedra, I sent you a message.

[jufguu]

It was awesome! Thank you so much.😀

[AmgadAhmed]

I took the existing codes and stored the output in a variable with the ability to specify the type of currency

thanks @darogael and @Adam-Artelia 

Text input chose between USD or another currency Lbl_Cur.Text

output stored in variable called NumWord

Onchange of DataCardValue124.Text

Set(Varcurrency ,If(Lbl_Cur.Text="USD"," Dollars "," Yemeni Rial "));
Set(VarcurrencyDiv,If(Lbl_Cur.Text="USD"," Cents "," Fils "));

Set(WholeNumberVar, First(Split(DataCardValue124.Text,".")));If(!IsBlank(Find(".",DataCardValue124.Text)),Set(DecimalNumberVar,Last(Split(DataCardValue124.Text,"."))),"");

Clear(expandedForm);

// Create a table that separates each digit to include its Value and Position. Make a placeholder for its Place and Period.
ForAll(RenameColumns(Split(WholeNumberVar.Result,""),"Result","Char"),
Collect(expandedForm,
{
Value: Value(Char),
Position:
// Determine the position of each character as a difference between the length of the number and how many digits have already been collected.
Len(WholeNumberVar.Result)-CountRows(expandedForm),
Place: 0,
Period: 0
}
)
);

// Update the Place and Period using the Position.
ForAll(RenameColumns(expandedForm,"Position","Pos"),
Patch(expandedForm,LookUp(expandedForm,Pos=Position),
{
Place:
// Determine if a given column is a one, ten, or hundred based on its position.
Switch(true,
Mod(Pos-1,3)=0,1,
Mod(Pos+1,3)=0,10,
100
),
Period:
// Determine what Period a digit belongs to based on its position.
RoundUp(Pos/3,0)-1
}
)
);Clear(expandedForm_Decimal);

// Create a table that separates each digit to include its Value and Position. Make a placeholder for its Place and Period.
ForAll(RenameColumns(Split(DecimalNumberVar.Result,""),"Result","Char"),
Collect(expandedForm_Decimal,
{
Value: Value(Char),
Position:
// Determine the position of each character as a difference between the length of the number and how many digits have already been collected.
Len(DecimalNumberVar.Result)-CountRows(expandedForm_Decimal),
Place: 0,
Period: 0
}
)
);

// Update the Place and Period using the Position.
ForAll(RenameColumns(expandedForm_Decimal,"Position","Pos"),
Patch(expandedForm_Decimal,LookUp(expandedForm_Decimal,Pos=Position),
{
Place:
// Determine if a given column is a one, ten, or hundred based on its position.
Switch(true,
Mod(Pos-1,3)=0,1,
Mod(Pos+1,3)=0,10,
100
),
Period:
// Determine what Period a digit belongs to based on its position.
RoundUp(Pos/3,0)-1
}
)
);

//end of the main code

Set(NumberTowords,

If(IsBlank(Find(".",DataCardValue124.Text)),
Concatenate(
Concat(
GroupBy(expandedForm,"Period","PeriodGroup"),

//  1 Read the hundred in the period.
LookUp(PlaceValue_1,Digit=LookUp(PeriodGroup,Place=100).Value,Word & " ") &
//  2 For 10-19, read the tens and ones together, otherwise separately.
If(LookUp(PeriodGroup,Place=10).Value=1,
LookUp(PlaceValue_1,Digit=Value(Concat(Filter(PeriodGroup,Place<>100),Text(Value))),Word & " "),
Concat(Filter(PeriodGroup,Place<>100),LookUp(PlaceValue_1,Digit=Value && Column=Place,Word & " "))
) &

// 3 Read the period.
Coalesce(LookUp(Period_1,Group=Period,Name & ","),"")),Varcurrency),
Concatenate(
Concat(
GroupBy(expandedForm,"Period","PeriodGroup"),

// 4 Read the hundred in the period.
LookUp(PlaceValue_1,Digit=LookUp(PeriodGroup,Place=100).Value,Word & " ") &

// 5 For 10-19, read the tens and ones together, otherwise separately.
If(LookUp(PeriodGroup,Place=10).Value=1,
LookUp(PlaceValue_1,Digit=Value(Concat(Filter(PeriodGroup,Place<>100),Text(Value))),Word & " "),
Concat(Filter(PeriodGroup,Place<>100),LookUp(PlaceValue_1,Digit=Value && Column=Place,Word & " "))
) &

// 6 Read the period.
Coalesce(LookUp(Period_1,Group=Period,Name & ","),"")
),Concatenate(Varcurrency," & ",

Concat(
GroupBy(expandedForm_Decimal,"Period","PeriodGroup"),


// 2 Read the period.
Coalesce(LookUp(Period_1,Group=Period,Name & " "),""))),
Concatenate(
Concat(
GroupBy(expandedForm_Decimal,"Period","PeriodGroup"),

// 3 Read the hundred in the period.
LookUp(PlaceValue_1,Digit=LookUp(PeriodGroup,Place=100).Value,Word) &

// 4 For 10-19, read the tens and ones together, otherwise separately.
If(LookUp(PeriodGroup,Place=10).Value=1,
LookUp(PlaceValue_1,Digit=Value(Concat(Filter(PeriodGroup,Place<>100),Text(Value))),Word & " "),
Concat(Filter(PeriodGroup,Place<>100),LookUp(PlaceValue_1,Digit=Value && Column=Place,Word & " "))
) &

// 5 Read the period.
Coalesce(LookUp(Period_1,Group=Period,Name & ", "),"")
),VarcurrencyDiv)))


);

//to remove extra ", million" & ", billion" & ...
Set(NumWord,
Substitute(Substitute(Substitute(Substitute(Substitute(NumberTowords,",thousand,",""),",million",""),",billion",""),",trillion",""),",quadrillion","") );

[efraimmoreira33]

Very good!

How would I do, in the cents part, instead of displaying the number, display it in full writing?

Thanks!

[simmyjer16]

@AmgadAhmed / @Adam-Artelia 

I need your help 😕  It's showing as "three" instead of "thirty"

Can you help identify what's wrong with my code? I just copy pasted it

Set(varWholeNumber, First(Split(varMKTAvailGrandTotal,".")));If(!IsBlank(Find(".",varMKTAvailGrandTotal)),Set(varDecimalNumber,Last(Split(varMKTAvailGrandTotal,"."))),"");

Clear(expandedForm);

// Create a table that separates each digit to include its Value and Position. Make a placeholder for its Place and Period.
ForAll(RenameColumns(Split(varWholeNumber.Value,""),"Value","Char"),
Collect(expandedForm,
{
Value: Value(Char),
Position:
// Determine the position of each character as a difference between the length of the number and how many digits have already been collected.
Len(varWholeNumber.Value)-CountRows(expandedForm),
Place: 0,
Period: 0
}
)
);

// Update the Place and Period using the Position.
ForAll(RenameColumns(expandedForm,"Position","Pos"),
Patch(expandedForm,LookUp(expandedForm,Pos=Position),
{
Place:
// Determine if a given column is a one, ten, or hundred based on its position.
Switch(true,
Mod(Pos-1,3)=0,1,
Mod(Pos+1,3)=0,10,
100
),
Period:
// Determine what Period a digit belongs to based on its position.
RoundUp(Pos/3,0)-1
}
)
);Clear(expandedForm_Decimal);

// Create a table that separates each digit to include its Value and Position. Make a placeholder for its Place and Period.
ForAll(RenameColumns(Split(varDecimalNumber.Value,""),"Value","Char"),
Collect(expandedForm_Decimal,
{
Value: Value(Char),
Position:
// Determine the position of each character as a difference between the length of the number and how many digits have already been collected.
Len(varDecimalNumber.Value)-CountRows(expandedForm_Decimal),
Place: 0,
Period: 0
}
)
);

// Update the Place and Period using the Position.
ForAll(RenameColumns(expandedForm_Decimal,"Position","Pos"),
Patch(expandedForm_Decimal,LookUp(expandedForm_Decimal,Pos=Position),
{
Place:
// Determine if a given column is a one, ten, or hundred based on its position.
Switch(true,
Mod(Pos-1,3)=0,1,
Mod(Pos+1,3)=0,10,
100
),
Period:
// Determine what Period a digit belongs to based on its position.
RoundUp(Pos/3,0)-1
}
)
);

//Text to read as words
Set(varAmountInWords,
If(IsBlank(Find(".",varMKTAvailGrandTotal)),
Concatenate(
Concat(
GroupBy(expandedForm,"Period","PeriodGroup"),

// Read the hundred in the period.
LookUp(PlaceValue_1,Value(Dig)=LookUp(PeriodGroup,Value(Place)=100).Value,Word & " ") &

// For 10-19, read the tens and ones together, otherwise separately.
If(Value(LookUp(PeriodGroup,Value(Place)=10).Value)=1,
LookUp(PlaceValue_1,Value(Dig)=Value(Concat(Filter(PeriodGroup,Value(Place)<>100),Text(Value))),Word & " "),
Concat(Filter(PeriodGroup,Value(Place)<>100),LookUp(PlaceValue_1,Value(Dig)=Value(Value) && Value(Col)=Value(Place),Word & " "))
) &

// Read the period.
Coalesce(LookUp(Period_1,Value(Group)=Value(Period),Name & " "),"")),"Pounds"),
Concatenate(
Concat(
GroupBy(expandedForm,"Period","PeriodGroup"),

// Read the hundred in the period.
LookUp(PlaceValue_1,Value(Dig)=Value(LookUp(PeriodGroup,Value(Place)=100).Value),Word & " ") &

// For 10-19, read the tens and ones together, otherwise separately.
If(Value(LookUp(PeriodGroup,Value(Place)=10).Value)=1,
LookUp(PlaceValue_1,Value(Dig)=Value(Concat(Filter(PeriodGroup,Value(Place)<>100),Text(Value))),Word & " "),
Concat(Filter(PeriodGroup,Value(Place)<>100),LookUp(PlaceValue_1,Value(Dig)=Value(Value) && Value(Col)=Value(Place),Word & " "))
) &

// Read the period.
Coalesce(LookUp(Period_1,Value(Group)=Value(Period),Name & ", "),"")
),Concatenate("Pounds & ",Concat(
GroupBy(expandedForm_Decimal,"Period","PeriodGroup"),

// Read the period.
Coalesce(LookUp(Period_1,Value(Group)=Value(Period),Name & " "),""))),
Concatenate(
Concat(
GroupBy(expandedForm_Decimal,"Period","PeriodGroup"),

// Read the hundred in the period.
LookUp(PlaceValue_1,Value(Dig)=Value(LookUp(PeriodGroup,Value(Place)=100).Value),Word) &

// For 10-19, read the tens and ones together, otherwise separately.
If(Value(LookUp(PeriodGroup,Value(Place)=10).Value)=1,
LookUp(PlaceValue_1,Value(Dig)=Value(Concat(Filter(PeriodGroup,Value(Place)<>100),Text(Value))),Word & " "),
Concat(Filter(PeriodGroup,Value(Place)<>100),LookUp(PlaceValue_1,Value(Dig)=Value(Value) && Value(Col)=Value(Place),Word & " "))
) &

// Read the period.
Coalesce(LookUp(Period_1,Value(Group)=Value(Period),Name & ", "),"")
),"Pence"))));

[tbeerman6646]

Is the issue only with the "3" or is it a problem with "40" being "4," "50" being "5," etc.?

[jufguu]

I want convert cents values also in the text. How can I update this code?

[vvvvvv]

Halo, this is really nice. But when it comes to my app, it is not giving correct result whenever I try putting 20+ digits. Need help

[Anonymous]

RenameColumns(Split(WholeNumberVar.Value, ""), "Result", "Char") the function renamecolumn have some invalid argument

[WeamGhaffar]

the name 'Result' isn't valid, how can I fix this?

[tu22]

does anyone have a solution to this yet? 

RenameColumns(Split(WholeNumberVar.Value, ""), "Result", "Char") the function renamecolumn have some invalid argument

please let me know

[tu22]

for anyone who got the same error:

RenameColumns(Split(WholeNumberVar.Value, ""), "Result", "Char") the function renamecolumn have some invalid argument

change it .Result to .Value and it should be fine