TOP

The amount written in English in LibreOffice Calc

LibreOffice Calc does not have a built-in function that can display numbers as English words on the worksheet, but you can add this capability by inserting the following SpellNum_YouLibreCalc function macro into the Basic module.

This function allows you to convert numbers to words in any currency using a formula, for example the number 22.50 will be read as "Twenty Two Dollars and Fifty Cents" or "Twenty Two Pesos and Fifty Centavos".

This can be very useful if you use Calc as a template to fill out checks or other accounting documents.



BASIC code for the SpellNum_YouLibreCalc function

To add your own function for the sum written in English, open the menu Tools - Macros - Edit Macros..., select Module1 and copy the following code text to the selected module:

 
  Function SpellNum_YouLibreCalc(ByVal MyNumber, Optional CurrNameS, Optional CurrNamePl, Optional CentNameS, Optional CentNamePl, Optional Modifier, Optional CurrPlace, Optional AddZero)
      'moonexcel.com.ua
      Dim Place(9) As String   
      Dim FCalc    As Object
      Dim Dollars, Cents, Temp, DecimalPlace, Count, CalcResult 
      
      If Len(MyNumber) = 0 Then Exit Function   
      
      FCalc = CreateUnoService("com.sun.star.sheet.FunctionAccess")
      
      If IsMissing(CurrNameS)  Then CurrNameS  = 0
      If IsMissing(CurrNamePl) Then CurrNamePl = 0
      If IsMissing(CentNameS)  Then CentNameS  = 0
      If IsMissing(CentNamePl) Then CentNamePl = 0
      If IsMissing(Modifier)   Then Modifier   = 0           
      If IsMissing(CurrPlace)  Then CurrPlace  = 0
      If IsMissing(AddZero)    Then AddZero    = 0
                 
      If CurrNameS  = 0 Then CurrNameS  = ""            
      If CurrNamePl = 0 Then CurrNamePl = ""
      If CentNameS  = 0 Then CentNameS  = ""      
      If CentNamePl = 0 Then CentNamePl = ""      
      
      Place(2) = " Thousand "
      Place(3) = " Million "
      Place(4) = " Billion "
      Place(5) = " Trillion " 

      MyNumber     = Trim(Str(MyNumber))
      ValMyNumber  = Val(MyNumber)
      DecimalPlace = InStr(MyNumber, ".")
      
      If DecimalPlace > 0 Then      
        If Modifier = 5 Then
          Cents = GetHundreds(Left(Mid(MyNumber, DecimalPlace + 1) & "000", 3))
        Else 
          Cents = GetTens(Left(Mid(MyNumber, DecimalPlace + 1) & "00", 2))
        End If        
        
        MyNumber = Trim(Left(MyNumber, DecimalPlace - 1))
      End If        
           
      Count = 1   
         
      Do While MyNumber <> ""
          Temp = GetHundreds(Right(MyNumber, 3))          
          If Temp <> "" Then Dollars = Temp & Place(Count) & Dollars          
          If Len(MyNumber) > 3 Then
              MyNumber = Left(MyNumber, Len(MyNumber) - 3)
          Else
              MyNumber = ""
          End If          
          Count = Count + 1
      Loop                                            
                                          
      If Modifier = 3 Then
        CalcResult = FCalc.callFunction("MOD", Array(ValMyNumber, 1))
        CalcResult = FCalc.callFunction("ROUND", Array(CalcResult, 2))                     
        Cents      = Str(CalcResult * 100) & "/100" 
      End If
      
      If Modifier = 4 Then        
        CalcResult = FCalc.callFunction("MOD", Array(ValMyNumber, 1))                  
        CalcResult = FCalc.callFunction("ROUND", Array(CalcResult, 3))                      
        Cents      = Str(CalcResult * 1000) & "/1000"              
      End If
      
      If CurrPlace = 1 Then
          If Len(Dollars) = 0 Then 
	        Dollars =  CurrNamePl & " Zero"
	      ElseIf Dollars = "One" Then
	        Dollars = CurrNameS & " One"
	      Else
	        Dollars = CurrNamePl & " " & Dollars 
	      End If      
      Else
	      If Len(Dollars) = 0 Then 
	        Dollars = "Zero " & CurrNamePl
	      ElseIf Dollars = "One" Then
	        Dollars = "One " & CurrNameS
	      Else
	        Dollars = Dollars & " " & CurrNamePl        
	      End If 
      End If           
      
      LenCents = Len(Cents)  
      
      If CurrPlace = 1 Then	      
	      If LenCents = 0 Then 
	        Cents = CentNamePl & " Zero"
	      ElseIf Cents = "One" Then
	        Cents = CentNamePl & " One"
	      Else
	        Cents = CentNamePl & " " & Cents 
	      End If
      Else
          If LenCents = 0 Then 
	        Cents = "Zero " & CentNamePl
	      ElseIf Cents = "One" Then
	        Cents = "One " & CentNameS
	      Else
	        Cents = Cents & " " & CentNamePl        
	      End If      
      End If
      
      If Modifier <> 2 Then Cents = " and " & Cents      
          
      If LenCents = 0 And AddZero <> 1 Then Cents = ""     
      
      Select Case Modifier
        Case 1:    SpellNumber = Dollars
        Case 2:    SpellNumber = Cents
        Case Else: SpellNumber = Dollars & Cents    
      End Select           
                                
      SpellNum_YouLibreCalc = FCalc.callFunction("TRIM", Array(SpellNumber))
  End Function
  

  Function GetHundreds(ByVal MyNumber)
      Dim Result As String
      If Val(MyNumber) = 0 Then Exit Function
      MyNumber = Right("000" & MyNumber, 3)
      'Convert the hundreds digit
      If Mid(MyNumber, 1, 1) <> "0" Then
          Result = GetDigit(Mid(MyNumber, 1, 1)) & " Hundred "
      End If
      'Convert tens and ones digits
      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
      'Reset the value of the temporary function
      Result = ""
      'If the value is between 10-19...
      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 'If the value is between 20-99...
          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
          'Get your seat
          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
  

Then, close Macro Editor, return to LibreOffice Calc, select any cell and use our new function.