A useful function: number2text

H

hiroh2k

Are you sick of writting the text expression of a numeric value?
this function change this:

15196739.32

Into this:

FIFTEEN MILLION ONE HUNDRED NINETY SIX THOUNSAND SEVEN HUNDRED THIRT
NINE

Is up to you if you want to use the function, and how to add it to yo
worksheet

Function Number2Text(Number)
a1a19 = Array("ONE", "TWO", "THREE", "FOUR", "FIVE", "SIX", "SEVEN"
"EIGHT", "NINE", "TEN", "ELEVEN", "TWELVE", "THIRTEEN", "FOURTEEN"
"FIFTEEN", "SIXTEEN", "SEVENTEEN", "EIGHTEEN", "NINETEEN")
aDecenas = Array("", "TWENTY", "THIRTY", "FOURTY", "FIFTY", "SIXTY"
"SEVENTY", "EIGHTY", "NINETY")
aLlones = Array("THOUNSAND", "MILLION", "BILLION", "TRILLION"
"QUADRILLION", "QUINTILLION", "SEXTILLION", "SEPTILLION", "OCTILLION"
"NONILLION", "DECILLION", "UNDECILLION", "DUODECILLION"
"TREDECILLION")

Number = Str(Number)
Pos = InStr(Number, ".")
If (Pos > 0) Then Number = Left(Number, Pos - 1)
If (Val(Number) = 0) Then
Text = "ZERO "
Else
millon = 0
Do
Number = "000" + LTrim(Number) 'Zeros to left to crop smooth
de1a99 = Val(Right(Number, 2))
If (de1a99 > 0) And (de1a99 < 20) Then
Text = a1a19(de1a99 - 1) + " " + Text
Else
unidad = Val(Right(Number, 1))
If (unidad > 0) Then Text = a1a19(unidad - 1) + " " + Text
decena = Val(Left(Right(Number, 2), 1))
If (decena > 0) Then Text = aDecenas(decena - 1) + " " + Text
End If
centena = Val(Left(Right(Number, 3), 1))
If (centena > 0) Then Text = a1a19(centena - 1) + " HUNDRED "
Text
Number = Left(Number, Len(Number) - 3)
If (Val(Number) > 0) Then
Text = aLlones(millon) + " " + Text
millon = millon + 1
End If
Loop While ((Number > 0) And (millon < 14))
End If
Number2Text = Text
End Function

I did 2 more functions, one to handle the cents and other with just th
Format as default parameter.

Function Number2Currency(Number, Frm)
Cents = Right(Format(Number, "#.00"), 2)
Text = Number2Text(Number)
If Frm <> "" Then
Frm = Replace(Frm, "%", Cents)
End If
Number2Currency = "(" + Text + Frm + ")"
End Function

Function Number2Dollar(Number)
Number2Dollar = Number2Currency(Number, "DOLLARS %/100 U.S.C.Y.")
End Function

About the function, i take the arrays from [email protected]
credits to him.

Greetings
Robert
 
T

Tom Ogilvy

Here are some other sources that beat you to the punch:

http://support.microsoft.com/?kbid=140704
XL: How to Convert a Numeric Value into English Words

http://support.microsoft.com/?kbid=95640
ACC: How to Convert Currency or Numbers into English Words


Also, check out Laurent Longre's free download morefunc which has a
worksheet function which will do this - coding has already been done, and a
well documented help file:

http://longre.free.fr/english/

Also, Bernie Deitrick has posted his version here many times.
Here is one posting:

http://groups.google.com/[email protected]&output=gplain
 
H

hiroh2k

That's right, actually, its in any school exercise make suc
functions...

BUT!

I did it first for spanish numbers.
so, if anyone need it IN SPANISH (it has more special cases tha
english)

Function NumeroALetra(Numero)
aUnidad = Array("UN", "DOS", "TRES", "CUATRO", "CINCO", "SEIS"
"SIETE", "OCHO", "NUEVE")
aDecena = Array("DIEZ", "VEINTE", "TREINTA", "CUARENTA", "CINCUENTA"
"SESENTA", "SETENTA", "OCHENTA", "NOVENTA")
aCentena = Array("UN CIENTO", "DOSCIENTOS", "TRESCIENTOS", "CUATR
CIENTOS", "QUINIENTOS", "SEISCIENTOS", "SETECIENTOS", "OCHOCIENTOS"
"NOVECIENTOS")
aResto = Array("ONCE", "DOCE", "TRECE", "CATORCE", "QUINCE")
Numero = Str(Numero)
Pos = InStr(Numero, ".")
If (Pos > 0) Then Numero = Left(Numero, Pos - 1)
Letra = ""
If (Val(Numero) = 0) Then
Letra = "CERO "
Else
millon = 0
Do
mil = False
Do
If (mil And (Right(Numero, 3) > 0)) Then Letra = "MIL "
Letra

' AJUSTE DE LA CLONACION DE NUMEROS INDESEABLES.
' If (Val(Right(Numero, 3)) > 0) Then
Numero = "000" + LTrim(Numero)
resto = Val(Right(Numero, 2))
If ((resto > 10) And (resto < 16)) Then
Letra = aResto(resto - 11) + " " + Letra
Else
unidad = Val(Right(Numero, 1))
If (unidad > 0) Then Letra = aUnidad(unidad - 1) + " "
Letra
decena = Val(Left(Right(Numero, 2), 1))
If (decena > 0) Then
If ((decena = 1) And (unidad > 5)) Then
Letra = "DIECI" + Letra
ElseIf ((decena = 2) And (unidad <> 0)) Then
Letra = "VEINTI" + Letra
Else
If (unidad > 0) Then Letra = "Y " + Letra
Letra = aDecena(decena - 1) + " " + Letra
End If
End If
End If
centena = Val(Left(Right(Numero, 3), 1))
If (centena > 0) Then
If (Val(Right(Numero, 3)) = 100) Then
Letra = "CIEN " + Letra
Else
Letra = aCentena(centena - 1) + " " + Letra
End If
End If
Numero = Left(Numero, Len(Numero) - 3)
If (Val(Numero) > 0) Then
mil = Not mil
Else
mil = False
End If
Loop While (mil)
If (Val(Numero) > 0) Then
millon = millon + 1
If (Val(Right(Numero, 6)) > 0) Then
Letra = " " + Letra

If (Val(Right(Numero, 1)) <> 1) Then Letra = "ES" + Letra
Select Case millon
Case 1
Letra = "MILLON" + Letra
Case 2
Letra = "BILLON" + Letra
Case 3
Letra = "TRILLON" + Letra
End Select
End If
End If
Loop While ((Numero > 0) And (millon <= 3))
End If
NumeroALetra = Letra
End Function

Function NumeroAMoneda(Numero, Formato)
Fraccion = Right(Format(Numero, "#.00"), 2)
Moneda = NumeroALetra(Numero)

If Formato <> "" Then
Formato = Replace(Formato, "%", Fraccion)
End If
NumeroAMoneda = "(" + Moneda + Formato + ")"
End Function

Function NumeroAPesos(Numero)
NumeroAPesos = NumeroAMoneda(Numero, "PESOS %/100 M.N.")
End Function

I hope this one would be useful to someone =)

Greetings
Robert
 
T

Tom Ogilvy

Actually, I think Laurent Longre's free addin supports several European
languages so you might want to look at that as well. (not positive, but
pretty sure plus you get 20+ other very useful functions including many
supporting 3D operations). I doubt it is in any school excercise. Seems
kind of complex for a newbie learning to code.
 
Top