Numbers to words conversion Euros Spanish

  • Thread starter Thread starter George Andrews
  • Start date Start date
G

George Andrews

Dear All

Using Excel 2000

I have adjusted code copied from the MS Knowledge Base to convert Numbers to
Words. The MS version converts to dollars and Cents whereas I need to use
this in Spanish for Euros and Cents.

The code below works for most instances but for the range 100 - 199 and
1000 - 1999 it does not. Can somebody help me with the additional code to
cope with this.

In Spanish, unlike English, there is a special way to say One Hundred and
something i.e. you drop the word ONE and replace the Hundred with 'Ciento'.
Likewise with One Thousand, you drop ONE and replace the Thousand with
'Mil'. From 200 and 2000 the rules are the same as

So as an example:

135 = One Hundred and Thirty Five
135 = Ciento treinta y cinco

235 = Two Hundred and Thirty Five
235 = Dos Cientos Treinta y cinco

1200 = One Thouand two hundred
1200 = Mil Dos Cientos

2200 = Two Thouand two hundred
2200 = Dos Mil Dos Cientos

Ok here is the adapted code. I hope somebody can help.

Regards

George Andrews
..
..


Option Explicit

'****************
' Main Function *
'****************

Function SpellNumber(ByVal MyNumber)
Dim Euros, Centimos, Temp
Dim DecimalPlace, Count

ReDim Place(9) As String
Place(2) = " Mil "
Place(3) = " Millon "
Place(4) = " Billon "
Place(5) = " Trillon "

' String representation of amount.
MyNumber = Trim(Str(MyNumber))

' Position of decimal place 0 if none.
DecimalPlace = InStr(MyNumber, ".")
' Convert cents and set MyNumber to dollar amount.
If DecimalPlace > 0 Then
Centimos = 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 Euros = Temp & Place(Count) & Euros
If Len(MyNumber) > 3 Then
MyNumber = Left(MyNumber, Len(MyNumber) - 3)
Else
MyNumber = ""
End If
Count = Count + 1
Loop

Select Case Euros
Case ""
Euros = "Zero Euros"
Case "Un"
Euros = "Un Euro"
Case Else
Euros = Euros & " Euros"
End Select

Select Case Centimos
Case ""
Centimos = " con Zero Centimos"
Case "Un"
Centimos = " con Un Centimo"
Case Else
Centimos = " con " & Centimos & " Centimos"
End Select

SpellNumber = Euros & Centimos
End Function

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

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
Result = GetDigit(Mid(MyNumber, 1, 1)) & " Cientos "
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. *
'*********************************************

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...
Select Case Val(Left(TensText, 1))
Case 2: Result = "Viente "
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
Result = Result & GetDigit _
(Right(TensText, 1)) ' Retrieve ones place.
End If
GetTens = Result
End Function

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

Function GetDigit(Digit)
Select Case Val(Digit)
Case 1: GetDigit = "Un"
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
 
George,

Here is a solution.

Fixing Un Mil is a bit of a kludge, but hey, it works.

Option Explicit

Dim f100 As Boolean
Dim f1000 As Boolean

'****************
' Main Function *
'****************

Function SpellNumber(ByVal MyNumber)
Dim Euros, Centimos, Temp
Dim DecimalPlace, Count

ReDim Place(9) As String
Place(2) = " Mil "
Place(3) = " Millon "
Place(4) = " Billon "
Place(5) = " Trillon "

' String representation of amount.
MyNumber = Trim(Str(MyNumber))

' Position of decimal place 0 if none.
DecimalPlace = InStr(MyNumber, ".")
' Convert cents and set MyNumber to dollar amount.
If DecimalPlace > 0 Then
Centimos = 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 Euros = Temp & Place(Count) & Euros
If Len(MyNumber) > 3 Then
MyNumber = Left(MyNumber, Len(MyNumber) - 3)
Else
MyNumber = ""
End If
Count = Count + 1
Loop

Select Case Euros
Case ""
Euros = "Zero Euros"
Case "Un"
Euros = "Un Euro"
Case Else
Euros = Euros & " Euros"
End Select

Select Case Centimos
Case ""
Centimos = " con Zero Centimos"
Case "Un"
Centimos = " con Un Centimo"
Case Else
Centimos = " con " & Centimos & " Centimos"
End Select

Euros = Replace(Euros, "Un Mil", "Mil")

SpellNumber = Euros & Centimos
End Function

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

Function GetHundreds(ByVal MyNumber)
Dim Result As String

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

' Convert the hundreds place.
f100 = False
If Mid(MyNumber, 1, 1) <> "0" Then
If Mid(MyNumber, 1, 1) = "1" Then
f100 = True
Result = "Ciento "
Else
Result = GetDigit(Mid(MyNumber, 1, 1)) & " Cientos "
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. *
'*********************************************

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...
Select Case Val(Left(TensText, 1))
Case 2: Result = "Viente "
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
Result = Result & GetDigit _
(Right(TensText, 1)) ' Retrieve ones place.
End If
GetTens = Result
End Function

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

Function GetDigit(Digit)
Select Case Val(Digit)
Case 1: GetDigit = "Un"
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



--

HTH

Bob Phillips
... looking out across Poole Harbour to the Purbecks
(remove nothere from the email address if mailing direct)
 
The other day I was playing around with a number to word translation in
spanish. Maybe you can try this piece of code.

< CODE BEGINS >

Option Explicit

Function Cifra2Texto(RngCifra As Range) As Variant
Dim Cifra As Double
Dim Matriz(1 To 48, 1 To 2) As Variant

PoblarMatriz Matriz

Cifra = RngCifra.Value
Cifra2Texto = ATexto(Cifra, Matriz)
End Function

Function ATexto(Cifra As Double, Matriz As Variant) As String
Dim CifraT As String

Select Case Cifra
Case Is < 100
ATexto = Decenas(Cifra, Matriz)
Case Is = 100
ATexto = "cien"
Case Is < 1000
ATexto = Centenas(Cifra, Matriz)
Case Is < 1000000
ATexto = Miles(Cifra \ 1000, Matriz)
If Cifra Mod 1000 <> 0 Then ATexto = ATexto & " " &
ATexto(Cifra Mod 1000, Matriz)
Case Is <= 2147483647#
ATexto = Millones(Cifra \ 1000000, Matriz)
If Cifra Mod 1000000 <> 0 Then ATexto = ATexto & " " &
ATexto(Cifra Mod 1000000, Matriz)
Case Else
CifraT = CStr(Cifra)
ATexto = ATextoT(CifraT, Matriz)
End Select
End Function

Function ATextoT(CifraT As String, Matriz As Variant) As String
Select Case Len(CifraT)
Case Is < 13
ATextoT = Millones(Val(Left(CifraT, Len(CifraT) - 6)), Matriz)
If Right(CifraT, 6) <> "000000" Then
ATextoT = ATextoT & " " & _
ATexto(Val(Right(CifraT, 6)), Matriz)
End If
Case Else
ATextoT = "Max Number Excedido!!"
End Select
End Function

Function Decenas(Cifra As Double, Matriz As Variant) As String
If Cifra <= 30 Or Cifra Mod 10 = 0 Then
Decenas = Application.VLookup(Cifra, Matriz, 2, 0)
Else
Decenas = Application.VLookup((Cifra \ 10) * 10, Matriz, 2, 0) & _
" y " & Application.VLookup(Cifra Mod 10, Matriz, 2, 0)
End If
End Function

Function Centenas(Cifra As Double, Matriz As Variant) As String
If Cifra Mod 100 = 0 Then
Centenas = Application.VLookup(Cifra, Matriz, 2, 0)
Else
Centenas = Application.VLookup((Cifra \ 100) * 100, Matriz, 2,
0) & _
" " & Decenas(Cifra Mod 100, Matriz)
End If
End Function

Function Miles(Cifra As Double, Matriz As Variant) As String
Select Case Cifra
Case Is < 100
Miles = Decenas(Cifra, Matriz) & " mil"
Case Is = 100
Miles = "cien mil"
Case Is < 1000
Miles = Centenas(Cifra, Matriz) & " mil"
End Select
End Function

Function Millones(Cifra As Double, Matriz As Variant) As String
If Cifra = 1 Then
Millones = " un millón"
Else
Millones = ATexto(Cifra, Matriz) & " millones"
End If
End Function

Sub PoblarMatriz(ByRef Matriz As Variant)
Matriz(1, 1) = 0: Matriz(1, 2) = "cero"
Matriz(2, 1) = 1: Matriz(2, 2) = "un"
Matriz(3, 1) = 2: Matriz(3, 2) = "dos"
Matriz(4, 1) = 3: Matriz(4, 2) = "tres"
Matriz(5, 1) = 4: Matriz(5, 2) = "cuatro"
Matriz(6, 1) = 5: Matriz(6, 2) = "cinco"
Matriz(7, 1) = 6: Matriz(7, 2) = "seis"
Matriz(8, 1) = 7: Matriz(8, 2) = "siete"
Matriz(9, 1) = 8: Matriz(9, 2) = "ocho"
Matriz(10, 1) = 9: Matriz(10, 2) = "nueve"
Matriz(11, 1) = 10: Matriz(11, 2) = "diez"
Matriz(12, 1) = 11: Matriz(12, 2) = "once"
Matriz(13, 1) = 12: Matriz(13, 2) = "doce"
Matriz(14, 1) = 13: Matriz(14, 2) = "trece"
Matriz(15, 1) = 14: Matriz(15, 2) = "catorce"
Matriz(16, 1) = 15: Matriz(16, 2) = "quince"
Matriz(17, 1) = 16: Matriz(17, 2) = "dieciséis"
Matriz(18, 1) = 17: Matriz(18, 2) = "diecisiete"
Matriz(19, 1) = 18: Matriz(19, 2) = "dieciocho"
Matriz(20, 1) = 19: Matriz(20, 2) = "diecinueve"
Matriz(21, 1) = 20: Matriz(21, 2) = "veinte"
Matriz(22, 1) = 21: Matriz(22, 2) = "veintiun"
Matriz(23, 1) = 22: Matriz(23, 2) = "veintidós"
Matriz(24, 1) = 23: Matriz(24, 2) = "veintitrés"
Matriz(25, 1) = 24: Matriz(25, 2) = "veinticuatro"
Matriz(26, 1) = 25: Matriz(26, 2) = "veinticinco"
Matriz(27, 1) = 26: Matriz(27, 2) = "veintiséis"
Matriz(28, 1) = 27: Matriz(28, 2) = "veintisiete"
Matriz(29, 1) = 28: Matriz(29, 2) = "veintiocho"
Matriz(30, 1) = 29: Matriz(30, 2) = "veintinueve"
Matriz(31, 1) = 30: Matriz(31, 2) = "treinta"
Matriz(32, 1) = 40: Matriz(32, 2) = "cuarenta"
Matriz(33, 1) = 50: Matriz(33, 2) = "cincuenta"
Matriz(34, 1) = 60: Matriz(34, 2) = "sesenta"
Matriz(35, 1) = 70: Matriz(35, 2) = "setenta"
Matriz(36, 1) = 80: Matriz(36, 2) = "ochenta"
Matriz(37, 1) = 90: Matriz(37, 2) = "noventa"
Matriz(38, 1) = 100: Matriz(38, 2) = "ciento"
Matriz(39, 1) = 200: Matriz(39, 2) = "doscientos"
Matriz(40, 1) = 300: Matriz(40, 2) = "trescientos"
Matriz(41, 1) = 400: Matriz(41, 2) = "cuatrocientos"
Matriz(42, 1) = 500: Matriz(42, 2) = "quinientos"
Matriz(43, 1) = 600: Matriz(43, 2) = "seiscientos"
Matriz(44, 1) = 700: Matriz(44, 2) = "setecientos"
Matriz(45, 1) = 800: Matriz(45, 2) = "ochocientos"
Matriz(46, 1) = 900: Matriz(46, 2) = "novencientos"
Matriz(47, 1) = 1000: Matriz(47, 2) = "mil"
Matriz(48, 1) = 1000000: Matriz(48, 2) = "millones"
End Sub

< CODE ENDS >

The max number is 999,999,999,999.
I just realized it doesn't take care of cents, because here in Chilean
currency is not used. But it is easy to create another function to call
this one for the integer and decimal part, just adding the needed words
in between.

Regards,
 
Thanks to those that have helped. This needs tinkering with to make it
perfect but it is fine for now.
George
 
George,

In what way? It does exactly what you asked.

--

HTH

Bob Phillips
... looking out across Poole Harbour to the Purbecks
(remove nothere from the email address if mailing direct)
 
Bob, here you go.

I need to extend the 10 - 19 range to 10 - 29 as each one has a specific
word
..
..
and then after 30 the system used is to say Thirty AND One, (Treinta Y Uno),
Thirty And Two (Treinta Y Dos), Eighty AND Eight (Ochenta Y Ocho), One
Hundred Thirty AND Eight (Ciento Treinta Y Ocho) or in Spanish. i.e. an AND
(Y) is added between the last two digits of the phrase. 31 is 30 AND 1, 75
is 70 AND 5.
..
..
and between 30 and 100, the One is no longer UN but UNO so you get Treinta y
UNO. This applies all the way through after the first 30 of each hundred.
i.e. 30-100, 130-200, 230 -300 etc 1030 -1100 etc. etc.

You did ask. If you want to help I would be delighted.

Regards

George
 
George,

Can't resist a challenge. Not doing it tonight, time for bed, but I will
look at it tomorrow, so check back tomorrow evening, Sun Morning, to see if
I have made any progress.

What are the numbers 20-29?

--

HTH

Bob Phillips
... looking out across Poole Harbour to the Purbecks
(remove nothere from the email address if mailing direct)
 
--

HTH

Bob Phillips
... looking out across Poole Harbour to the Purbecks
(remove nothere from the email address if mailing direct)
 
Okay George, how is this?

I think I have catered for all the bits you mention.

Option Explicit

Dim f100 As Boolean
Dim f1000 As Boolean

'****************
' Main Function *
'****************

Function SpellNumber(ByVal MyNumber)
Dim Euros, Centimos, Temp
Dim DecimalPlace, Count

ReDim Place(9) As String
Place(2) = " Mil "
Place(3) = " Millon "
Place(4) = " Billon "
Place(5) = " Trillon "

' String representation of amount.
MyNumber = Trim(Str(MyNumber))

' Position of decimal place 0 if none.
DecimalPlace = InStr(MyNumber, ".")
' Convert cents and set MyNumber to dollar amount.
If DecimalPlace > 0 Then
Centimos = 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 Euros = Temp & Place(Count) & Euros
If Len(MyNumber) > 3 Then
MyNumber = Left(MyNumber, Len(MyNumber) - 3)
Else
MyNumber = ""
End If
Count = Count + 1
Loop

Select Case Euros
Case ""
Euros = "Zero Euros"
Case "Un"
Euros = "Un Euro"
Case Else
Euros = Euros & " Euros"
End Select

Select Case Centimos
Case ""
Centimos = " con Zero Centimos"
Case "Un"
Centimos = " con Un Centimo"
Case Else
Centimos = " con " & Centimos & " Centimos"
End Select

'Bob's special kludges to handle
' Un Mil becomes Mil
' Un between 30 and 100 becomes Uno
Euros = Replace(Euros, "Un Mil", "Mil")
If InStr(1, Euros, " Y ", vbTextCompare) > 0 Then
Euros = Replace(Euros, "Un ", "Uno ")
End If

SpellNumber = Euros & Centimos
End Function

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

Function GetHundreds(ByVal MyNumber)
Dim Result As String

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

' Convert the hundreds place.
f100 = False
If Mid(MyNumber, 1, 1) <> "0" Then
If Mid(MyNumber, 1, 1) = "1" Then
f100 = True
Result = "Ciento "
Else
Result = GetDigit(Mid(MyNumber, 1, 1)) & " Cientos "
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. *
'*********************************************

Function GetTens(TensText)
Dim Result As String

Result = "" ' Null out the temporary function value.
If Val(Left(TensText, 1)) = 2 Then ' If value between 20-29...
Select Case Val(TensText)
Case 20: Result = "Viente"
Case 21: Result = "Vientiuino"
Case 22: Result = "Vientidos"
Case 23: Result = "Vientitres"
Case 24: Result = "Vienticuatro"
Case 25: Result = "Vienticinco"
Case 26: Result = "Vientiseis"
Case 27: Result = "Vientisiete"
Case 28: Result = "Vientiocho"
Case 29: Result = "Vientinueve"
Case Else
End Select
ElseIf 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...
Select Case Val(Left(TensText, 1))
Case 2: Result = "Viente Y "
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
Result = Result & GetDigit _
(Right(TensText, 1)) ' Retrieve ones place.
End If
GetTens = Result
End Function

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

Function GetDigit(Digit)
Select Case Val(Digit)
Case 1: GetDigit = "Un"
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





--

HTH

Bob Phillips
... looking out across Poole Harbour to the Purbecks
(remove nothere from the email address if mailing direct)
 
Back
Top