Convert amount from numbers to words

I have a form that is used for cash purposes. In it, I have a total amount in numbers and the same total in letters. I want to convert the amount from numbers to written words. For example: if the total is $114.75, then it should be automatically converted to the text "One hundred and fourteen dollars and seventy five cents"

I have done it in the past using Excel, C#, and other programming languages using a pre-defined function that accepts amount, main currency, sub-currency and it would return the amount in letters as mentioned above.

Is there a way to do this in an XLSForm?

Thanks
Isslam

Would you mind to share this function, please, to facilitate further discussion here?

Excel VBA function to convert amount from numbers to words (not mine, I found it online and used it in Excel and it works):

Function NumberToTextEN(ByVal MyNumber, MainCurrency As String, SubCurrency As String)

Dim Number1, Number2, Temp

Dim DecimalPlace, Count

ReDim Place(9) As String

Place(2) = " Thousand "

Place(3) = " Million "

Place(4) = " Billion "

Place(5) = " Trillion "

MyNumber = Trim(Str(MyNumber))

DecimalPlace = InStr(MyNumber, ".")

If DecimalPlace > 0 Then

Number2 = GetTens(Left(Mid(MyNumber, DecimalPlace + 1) & "00", 2))

MyNumber = Trim(Left(MyNumber, DecimalPlace - 1))

End If

Count = 1

Do While MyNumber <> ""

Temp = GetHundreds(Right(MyNumber, 3))

If Temp <> "" Then Number1 = Temp & Place(Count) & Number1

If Len(MyNumber) > 3 Then

MyNumber = Left(MyNumber, Len(MyNumber) - 3)

Else

MyNumber = ""

End If

Count = Count + 1

Loop

Select Case Number1

Case ""

Number1 = "No " & MainCurrency

Case Else

Number1 = Number1 & " " & MainCurrency

End Select

Select Case Number2

Case ""

Number2 = ""

Case Else

Number2 = " and " & Number2 & " " & SubCurrency

End Select

NumberToTextEN = Number1 & Number2

End Function



Function GetHundreds(ByVal MyNumber)

Dim Result As String

If Val(MyNumber) = 0 Then Exit Function

MyNumber = Right("000" & MyNumber, 3)

If Mid(MyNumber, 1, 1) <> "0" Then

Result = GetDigit(Mid(MyNumber, 1, 1)) & " Hundred "

End If


If Mid(MyNumber, 2, 1) <> "0" Then

Result = Result & GetTens(Mid(MyNumber, 2))

Else

Result = Result & GetDigit(Mid(MyNumber, 3))

End If

GetHundreds = Result

End Function



Function GetTens(TensText)

Dim Result As String


Result = ""

If Val(Left(TensText, 1)) = 1 Then

Select Case Val(TensText)

Case 10: Result = "Ten"

Case 11: Result = "Eleven"

Case 12: Result = "Twelve"

Case 13: Result = "Thirteen"

Case 14: Result = "Fourteen"

Case 15: Result = "Fifteen"

Case 16: Result = "Sixteen"

Case 17: Result = "Seventeen"

Case 18: Result = "Eighteen"

Case 19: Result = "Nineteen"

Case Else

End Select

Else

Select Case Val(Left(TensText, 1))

Case 2: Result = "Twenty "

Case 3: Result = "Thirty "

Case 4: Result = "Forty "

Case 5: Result = "Fifty "

Case 6: Result = "Sixty "

Case 7: Result = "Seventy "

Case 8: Result = "Eighty "

Case 9: Result = "Ninety "

Case Else

End Select

Result = Result & GetDigit(Right(TensText, 1))

End If

GetTens = Result

End Function



Function GetDigit(Digit)

Select Case Val(Digit)

Case 1: GetDigit = "One"

Case 2: GetDigit = "Two"

Case 3: GetDigit = "Three"

Case 4: GetDigit = "Four"

Case 5: GetDigit = "Five"

Case 6: GetDigit = "Six"

Case 7: GetDigit = "Seven"

Case 8: GetDigit = "Eight"

Case 9: GetDigit = "Nine"

Case Else: GetDigit = ""

End Select

End Function

@wroos any ideas please?

Maybe an external call could help, see https://docs.getodk.org/collect-external-apps/#launching-external-apps-to-populate-single-fields.

Side-note: Word also has an option (poorly documented), see https://m.youtube.com/watch?v=biiADvajlTY. Similar in Excel, see https://www.fasteasy.at/tippshop/index.php?mp=1&id=2&id2=45&id3=1051, or https://m.youtube.com/watch?v=JDv-cBPUcFU (and https://m.youtube.com/watch?v=JDv-cBPUcFU).

What range of numbers do you need to cover? Do you have to handle decimal places? ... If limited range, eg at most hundreds w/ no decimals, then you could probably re-implement the equivalent of the above NumberToTextEN() function with a few XLSForm calculations. But for arbitrary numbers, you'd need some sort of loop (ala 'Do While MyNumber...') which isnt readily feasible with XPath calculations [although it could possibly be accomplished with a repeat, but it'd be messy I expect...]

Hi @Xiphware,
How would it be possible to call this external VBA function from XLSForm? Or is there an Android compliant version?

I haven't tested it a lot so it might contain some bugs but at first glance, it seems ok:
numbersToWords.xlsx (9.5 KB)
The form should support numbers <= 99 999 999.

3 Likes

if(${hundreds}>0, concat(if(${hundreds}=1, 'one', if(${hundreds}=2, 'two', if(${hundreds}=3, 'three', if(${hundreds}=4, 'four', if(${hundreds}=5, 'five', if(${hundreds}=6, 'six', if(${hundreds}=7, 'seven', if(${hundreds}=8, 'eight', if(${hundreds}=9, 'nine','') ) ) ) ) ) ) ) ), ‘ ’ ,‘hundred’),’’)

or a slightly more concise alternative

if(${hundreds}>0, concat(selected-at("one two three four five six seven eight nine", ${hundreds}-1), ‘ ’ ,‘hundred’),’’)

:slight_smile:

3 Likes

Right and this should be even better :slight_smile:
numbersToWords.xlsx (11.6 KB)

1 Like

[thnx, the 100-deep nested-if statement was making my eye twitch... :laughing:]

I see, thankfully I used ChatGPT so I didn't have to write that 100-deep nested-if statement on my own :slight_smile: