A useful function: number2text
 

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
credits to him.

Greetings
Robert

--
Message posted from http://www.ExcelForum.com

A useful function: number2text
 

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

--
Message posted from http://www.ExcelForum.com

A useful function: number2text
 

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.

--
Regards,
Tom Ogilvy

"hiroh2k " wrote in message
...
That's right, actually, its in any school exercise make such
functions...

BUT!

I did it first for spanish numbers.
so, if anyone need it IN SPANISH (it has more special cases than
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", "CUATRO
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
Roberto


---
Message posted from http://www.ExcelForum.com/