I need to vonvert a number to text or charactor (Eg:if I enter in a cell
"200" on the next cell "Two Hundred" has to display

OK, so I've been watching this thread to see if anyone gave an
answer... It interested me. I haven't seen one, so I've taken a whack
at it. Not sure why you would really want this but here it is.
There's a lot of folks on here smarter than I am, so I'm curious if
they have a different approach.

It handles numbers up to 999,999,999

Paste this code in then use =jtest(A1) to convert numbers to their
text equilivant.

Let me know what you think,
Jamie


Function jtest(var1 As String)
'By Jamie Hildebrand 7/18/06
Dim vHundreds As String
Dim vThousands As String
Dim vMillions As String

a$ = var1
x = Len(a$)

'Code to limit entry without throwing error
If x 9 Then
jtest = "Num too big"
Exit Function
End If


'Hundreds
If x = 3 Then
vHundreds = Right(a$, 3)
Else
'Incase we have number smaller than hundreds
vHundreds = a$
End If

'Thousands
If x 3 Then
vThousands = Left(a$, Len(a$) - 3)

If Len(vThousands) = 3 Then
vThousands = Right(vThousands, 3)
Else
'Incase we have number smaller than 100k
vThousands = vThousands
End If
End If

'Millions
If x < 10 And x 6 Then
vMillions = Left(a$, Len(a$) - 6)

If Len(vMillions) = 3 Then
vMillions = Right(vMillions, 3)
Else
'Incase we have number smaller than 100MM
vMillions = vMillions
End If
End If

If vMillions < Empty Then
b1$ = jtest2(vMillions)
If Trim(b1$) < "" Then b1$ = b1$ & " Million "

End If

If vThousands < Empty Then
b2$ = jtest2(vThousands)
If Trim(b2$) < "" Then b2$ = b2$ & " Thousand "

End If

If vHundreds < Empty Then
b3$ = jtest2(vHundreds)

End If

b$ = b1$
If b2 < "" Then b$ = b$ & b2$
If b3$ < "" Then b$ = b$ & b3$

b$ = Trim(b$)

jtest = b$
End Function
Function jtest2(var1 As String)
'By Jamie Hildebrand 7/18/06
x = Len(var1)

If x = 3 Then
h = Left(var1, 1)
If h = "0" Then h = ""
If Val(h) = 1 Then h = "One"
If Val(h) = 2 Then h = "Two"
If Val(h) = 3 Then h = "Three"
If Val(h) = 4 Then h = "Four"
If Val(h) = 5 Then h = "Five"
If Val(h) = 6 Then h = "Six"
If Val(h) = 7 Then h = "Seven"
If Val(h) = 8 Then h = "Eight"
If Val(h) = 9 Then h = "Nine"

If h < "" Then
h = h & " Hundred"
End If

End If

If x = 2 Then
t = Left(Right(var1, 2), 1)

If t = "0" Then t = ""
If Val(t) = 1 Then t = "Teen"
If Val(t) = 2 Then t = "Twenty"
If Val(t) = 3 Then t = "Thirty"
If Val(t) = 4 Then t = "Fourty"
If Val(t) = 5 Then t = "Fifty"
If Val(t) = 6 Then t = "Sixty"
If Val(t) = 7 Then t = "Seventy"
If Val(t) = 8 Then t = "Eighty"
If Val(t) = 9 Then t = "Ninety"
End If

o = Right(var1, 1)
If o = "0" Then o = ""
If Val(o) = 1 Then o = "One"
If Val(o) = 2 Then o = "Two"
If Val(o) = 3 Then o = "Three"
If Val(o) = 4 Then o = "Four"
If Val(o) = 5 Then o = "Five"
If Val(o) = 6 Then o = "Six"
If Val(o) = 7 Then o = "Seven"
If Val(o) = 8 Then o = "Eight"
If Val(o) = 9 Then o = "Nine"

If t = "Teen" Then
t = Right(var1, 2)
If Val(t) = 10 Then t = "Ten"
If Val(t) = 11 Then t = "Eleven"
If Val(t) = 12 Then t = "Twelve"
If Val(t) = 13 Then t = "Thirteen"
If Val(t) = 14 Then t = "Fourteen"
If Val(t) = 15 Then t = "Fifteen"
If Val(t) = 16 Then t = "Sixteen"
If Val(t) = 17 Then t = "Seventeen"
If Val(t) = 18 Then t = "Eightteen"
If Val(t) = 19 Then t = "Nineteen"

o = ""

End If

j$ = h
If t < "" Then j$ = j$ & " " & t
If o < "" Then j$ = j$ & " " & o

jtest2 = Trim(j$)

End Function





Dilip wrote:
I need to vonvert a number to text or charactor (Eg:if I enter in a cell
"200" on the next cell "Two Hundred" has to display

This function at this site seems to work
http://www.meadinkent.co.uk/xlnumberstext.htm


--
davesexcel


------------------------------------------------------------------------
davesexcel's Profile: http://www.excelforum.com/member.php...o&userid=31708
View this thread: http://www.excelforum.com/showthread...hreadid=555884