"Translating" numbers into words

Hi,

I´m trying to automate the payment process for employee totals and bank transfers for checks and and nominas.

What I would basically like to do is this:

Imagine in cell A1 is the number 11.233,23 I would basically like excel to translate the number into. "Eleven thousand two hundred and thirty four euros and 23 cents"

Any ideas?

Hi,

Thanks for link, its really helpfull.

I´m trying to adapt the code to translate into spanish and venezuelan Bolivares and so far I have come to only 1 (ONE) problem that I hope you could help me with.


With the thousand it will put "Un" before "Mil" but in spanish thats wrong.

1000 - is just "mil" not "Un Mil"
1001 is "mil uno" not "Un Mil Uno"

The way the code is, it will put the 1 digit number before the thousand.

with 2000, 3000, etc, I have no problem, 2000 is "dos mil"

Any help is more than welcome!

Bellow is the modified code i managed to adapt:

Function MisReales(ByVal MyNumber)
Dim Temp
         Dim Boliveres, Cts
         Dim DecimalPlace, Count

         ReDim Place(9) As String
         Place(2) = " Mil "
         Place(3) = " Million "
         Place(4) = " Billion "
         Place(5) = " Trillion "

         ' Convert MyNumber to a string, trimming extra spaces.
         MyNumber = Trim(Str(MyNumber))

         ' Find decimal place.
         DecimalPlace = InStr(MyNumber, ".")

         ' If we find decimal place...
         If DecimalPlace > 0 Then
            ' Convert Cts
            Temp = Left(Mid(MyNumber, DecimalPlace + 1) & "00", 2)
            Cts = ConvertTens(Temp)

            ' Strip off Cts from remainder to convert.
            MyNumber = Trim(Left(MyNumber, DecimalPlace - 1))
         End If

         Count = 1
         Do While MyNumber <> ""
            ' Convert last 3 digits of MyNumber to English Boliveres.
            Temp = ConvertHundreds(Right(MyNumber, 3))
            If Temp <> "" Then Boliveres = Temp & Place(Count) & Boliveres
            If Len(MyNumber) > 3 Then
               ' Remove last 3 converted digits from MyNumber.
               MyNumber = Left(MyNumber, Len(MyNumber) - 3)
            Else
               MyNumber = ""
            End If
            Count = Count + 1
         Loop

         ' Clean up Boliveres.
         Select Case Boliveres
            Case ""
               Boliveres = "zero Boliveres"
            Case "Un"
               Boliveres = "Un Bolivar"
            Case Else
               Boliveres = Boliveres & " Boliveres"
         End Select

         ' Clean up Cts.
         Select Case Cts
            Case ""
               Cts = " Y zero Cts"
            Case "One"
               Cts = " Y un Cent"
            Case Else
               Cts = " Y " & Cts & " Cts"
         End Select

         MisReales = Boliveres & Cts
End Function



Private Function ConvertHundreds(ByVal MyNumber)
Dim Result As String

         ' Exit if there is nothing to convert.
         If Val(MyNumber) = 0 Then Exit Function

         ' Append leading zeros to number.
         MyNumber = Right("000" & MyNumber, 3)
        
         

         ' Do we have a hundreds place digit to convert?
         If Left(MyNumber, 1) = "1" Then
         Result = " Ciento "
         Select Case Val(MyNumber)
               Case 100: Result = "Cien"
               Case Else
            End Select
         
            
        End If
        
             If Left(MyNumber, 1) = "2" Then
            Result = " Doscientos "
             End If
             
            If Left(MyNumber, 1) = "3" Then
            Result = " Trescientos "
             End If
            
            If Left(MyNumber, 1) = "4" Then
            Result = " Cuatrocientos "
             End If
            
            If Left(MyNumber, 1) = "5" Then
            Result = " Quinientos "
             End If
        
            If Left(MyNumber, 1) = "6" Then
            Result = " Seiscentos "
             End If
             
              If Left(MyNumber, 1) = "7" Then
            Result = " Setecientos "
             End If
             
                If Left(MyNumber, 1) = "8" Then
            Result = " Ochocientos "
             End If
                If Left(MyNumber, 1) = "9" Then
            Result = " Novecientos "
             End If
            

         ' Do we have a tens place digit to convert?
         If Mid(MyNumber, 2, 1) <> "0" Then
            Result = Result & ConvertTens(Mid(MyNumber, 2))
         Else
            ' If not, then convert the ones place digit.
            Result = Result & ConvertDigit(Mid(MyNumber, 3))
         End If

         ConvertHundreds = Trim(Result)
End Function



Private Function ConvertTens(ByVal MyTens)
Dim Result As String

         ' Is value between 10 and 19?
         If Val(Left(MyTens, 1)) = 1 Then
            Select Case Val(MyTens)
               Case 10: Result = "Diez"
               Case 11: Result = "Onze"
               Case 12: Result = "Doze"
               Case 13: Result = "Treze"
               Case 14: Result = "Quatorce"
               Case 15: Result = "Quinze"
               Case 16: Result = "Dezesseis"
               Case 17: Result = "Dezessiete"
               Case 18: Result = "Dezeocho"
               Case 19: Result = "decenuebe"
               Case Else
            End Select
            
             
              

            ' .. otherwise it's between 20 and 99.
            Select Case Val(Left(MyTens, 1))
               Case 2: Result = "Vente "
               Case 3: Result = "Trenta "
               Case 4: Result = "Quarenta "
               Case 5: Result = "Cincuenta "
               Case 6: Result = "Sessenta "
               Case 7: Result = "Setenta "
               Case 8: Result = "Ochenta "
               Case 9: Result = "Nobenta "
               Case Else
            End Select
         Else
            ' .. otherwise it's between 20 and 99.
            Select Case Val(Left(MyTens, 1))
               Case 2: Result = "Vente "
               Case 3: Result = "Trenta "
               Case 4: Result = "Quarenta "
               Case 5: Result = "Cincuenta "
               Case 6: Result = "Sessenta "
               Case 7: Result = "Setenta "
               Case 8: Result = "Ochenta "
               Case 9: Result = "Nobenta "
               Case Else
            End Select

            ' Convert ones place digit.
            Result = Result & ConvertDigit(Right(MyTens, 1))
         End If

         ConvertTens = Result
End Function



Private Function ConvertDigit(ByVal MyDigit)
Select Case Val(MyDigit)
            Case 1: ConvertDigit = "Un"
            Case 2: ConvertDigit = "Dos"
            Case 3: ConvertDigit = "Tres"
            Case 4: ConvertDigit = "Quatro"
            Case 5: ConvertDigit = "Cinco"
            Case 6: ConvertDigit = "Seis"
            Case 7: ConvertDigit = "Siete"
            Case 8: ConvertDigit = "Ocho"
            Case 9: ConvertDigit = "Nuebe"
            Case Else: ConvertDigit = ""
         End Select
End Function

Finnally finished the code and got around the problem of:

1000 - is just "mil" not "Un Mil"
1001 is "mil uno" not "Un Mil Uno".

Function LoTioHablaBolivares(ByVal MyNumber, Sep, Mon)
          Dim Dollars, Cents, Temp
          Dim DecimalPlace, Count
          Dim origNumLen, origNum
          
          ReDim place(9) As String
          place(2) = " Mil "
          place(3) = " Millon(es) "
          place(4) = " Billones "
          place(5) = " Trillones "

          ' String representation of amount.
          MyNumber = Trim(CStr(MyNumber))
          origNumLen = Len(MyNumber)
          origNum = MyNumber
          
          ' Position of decimal place 0 if none.
          If Sep = "." Then DecimalPlace = InStr(MyNumber, ".")
          If Sep = "," Then DecimalPlace = InStr(MyNumber, ",")
          
          ' Convert cents and set MyNumber to currency amount.
          If DecimalPlace > 0 Then
              Cents = 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
                If Temp = "UN" And Count > 2 Then
                        Dollars = Temp & Left(place(Count), Len(place(Count)) - 3) _
                            & " " & Dollars
                Else
                        Dollars = Temp & place(Count) & Dollars
                End If
            End If
              If Len(MyNumber) > 3 Then
                  MyNumber = Left(MyNumber, Len(MyNumber) - 3)
              Else
                  MyNumber = ""
              End If
              Count = Count + 1
          Loop

          Select Case Dollars
              Case ""
                  Dollars = "Cero " & Mon
              Case "UN"
                  Dollars = "Un " & Left(Mon, Len(Mon) - 1)
              Case Else
                  If origNumLen > 6 And (origNum Mod 1000000) = 0 Then
                    Dollars = Dollars & " " & "de " & Mon
                  Else
                    Dollars = Dollars & " " & Mon
                  End If
          End Select

          Select Case Cents
              Case ""
                  Cents = " con cero centavos"
              Case "One"
                  Cents = " con un centavo"
              Case Else
                  Cents = " con " & Cents & " centavos"
          End Select

          LoTioHablaBolivares = UCase(Trim(Dollars & Cents))
      End Function



      '*******************************************
      ' Converts a number from 100-999 into text *
      '*******************************************

      Private Function GetHundreds(ByVal MyNumber)
          Dim Result As String

          If Val(MyNumber) = 0 Then Exit Function
          MyNumber = Right("000" & MyNumber, 3)

          ' Convert the hundreds place.
          If Mid(MyNumber, 1, 1) <> "0" Then
            If MyNumber = "100" Then
                Result = "cien "
            Else
            Select Case Mid(MyNumber, 1, 1)
                Case 1
                    Select Case Len(MyNumber)
                        Case 1
                            Result = "Un "
                        Case 3
                            Result = "Ciento "
                        Case 4
                            Result = ""
                        Case 6
                            Result = "Ciento "
                        Case 9
                            Result = "Ciento "
                    End Select
           
                Case 2
                    Result = "doscientos "
                Case 3
                    Result = "trescientos "
                Case 4
                    Result = "cuatrocientos "
                Case 5
                    Result = "quinientos "
                Case 6
                    Result = "seiscientos "
                Case 7
                    Result = "setecientos "
                Case 8
                    Result = "ochocientos "
                Case 9
                    Result = "novecientos "
            End Select
          End If
        End If
          ' Convert the tens and ones place.
          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



      '*********************************************
      ' Converts a number from 10 to 99 into text. *
      '*********************************************

     Private Function GetTens(TensText)
          Dim Result As String

          Result = ""           ' Null out the temporary function value.
          If Val(Left(TensText, 1)) = 1 Then   ' If value between 10-19...
              Select Case Val(TensText)
                  Case 10: Result = "Diez"
                  Case 11: Result = "Once"
                  Case 12: Result = "Doce"
                  Case 13: Result = "Trece"
                  Case 14: Result = "Catorce"
                  Case 15: Result = "Quince"
                  Case 16: Result = "Dieciseis"
                  Case 17: Result = "Diecisiete"
                  Case 18: Result = "Dieciocho"
                  Case 19: Result = "Diecinueve"
                  Case Else
              End Select
          Else                                 ' If value between 20-99...
              If Val(Right(TensText, 1)) = 0 Then
                Select Case Val(Left(TensText, 1))
                    Case 2: Result = "veinte"
                    Case 3: Result = "treinta"
                    Case 4: Result = "cuarenta"
                    Case 5: Result = "cincuenta"
                    Case 6: Result = "sesenta"
                    Case 7: Result = "setenta"
                    Case 8: Result = "ochenta"
                    Case 9: Result = "noventa"
                    Case Else
                End Select
              Else
                Select Case Val(Left(TensText, 1))
                    Case 2: Result = "veinti"
                    Case 3: Result = "treinta y "
                    Case 4: Result = "cuarenta y "
                    Case 5: Result = "cincuenta y "
                    Case 6: Result = "sesenta y "
                    Case 7: Result = "setenta y "
                    Case 8: Result = "ochenta y "
                    Case 9: Result = "noventa y "
                    Case Else
                End Select
              End If
              
              Result = Result & GetDigit _
                  (Right(TensText, 1))  ' Retrieve ones place.
          End If
          GetTens = Result
      End Function




      '*******************************************
      ' Converts a number from 1 to 9 into text. *
      '*******************************************

      Private Function GetDigit(Digit)
          Select Case Val(Digit)
              Case 1: GetDigit = "UNO"
              Case 2: GetDigit = "DOS"
              Case 3: GetDigit = "TRES"
              Case 4: GetDigit = "CUATRO"
              Case 5: GetDigit = "CINCO"
              Case 6: GetDigit = "SEIS"
              Case 7: GetDigit = "SIETE"
              Case 8: GetDigit = "OCHO"
              Case 9: GetDigit = "NUEVE"
              Case Else: GetDigit = ""
          End Select
      End Function

This function,s problems can be solved with the following formula referencing the cell where the result of the function is:

="(BSF)    "&SI(LEFT(A1;10)="UNO MILLON";A1;IF(LEFT(A1;3)="UNO";IF(ISNUMBER(SEARCH("MIL";A1));MID(A1;SEARCH(" ";A1)+1;10^5);B1);B1))