Number To Text

Use the TEXT Function.
Syntax Text(Value,Format_Text)
Choose a Format text from the Cell-Format-Number Menu.

Jaafar.

Sorry,
I think I have mis-lead you.
I want to convert the 123 to
"ONE HUNDERD AND TWENTY THREE"

Thanks!!

Hi Andrew:

I have listed the function hereunder -- sorry, I don't know the author -- so let us give the credit where the credit is due

<PRE>
Option Explicit

'****************' Main Function *'****************
Function SpellNumber(ByVal MyNumber)
Dim Dollars, Cents, Temp
Dim DecimalPlace, Count
ReDim Place(9) As String
Place(2) = " Thousand "
Place(3) = " Million "
Place(4) = " Billion "
Place(5) = " Trillion " ' 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
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 Dollars = Temp & Place(Count) & Dollars
If Len(MyNumber) > 3 Then
MyNumber = Left(MyNumber, Len(MyNumber) - 3)
Else
MyNumber = ""
End If
Count = Count + 1
Loop
Select Case Dollars
Case ""
Dollars = "No Dollars"
Case "One"
Dollars = "One Dollar"
Case Else
Dollars = Dollars & " Dollars"
End Select
Select Case Cents
Case ""
Cents = " and No Cents"
Case "One"
Cents = " and One Cent"
Case Else
Cents = " and " & Cents & " Cents"
End Select
SpellNumber = Dollars & Cents
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)) & " Hundred "
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 = "Ten"
Case 11: Result = "Eleven"
Case 12: Result = "Twelve"
Case 13: Result = "Thirteen"
Case 14: Result = "Fourteen"
Case 15: Result = "Fifteen"
Case 16: Result = "Sixteen"
Case 17: Result = "Seventeen"
Case 18: Result = "Eighteen"
Case 19: Result = "Nineteen"
Case Else
End Select
Else ' If value between 20-99
Select Case Val(Left(TensText, 1))
Case 2: Result = "Twenty "
Case 3: Result = "Thirty "
Case 4: Result = "Forty "
Case 5: Result = "Fifty "
Case 6: Result = "Sixty "
Case 7: Result = "Seventy "
Case 8: Result = "Eighty "
Case 9: Result = "Ninety "
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 = "One"
Case 2: GetDigit = "Two"
Case 3: GetDigit = "Three"
Case 4: GetDigit = "Four"
Case 5: GetDigit = "Five"
Case 6: GetDigit = "Six"
Case 7: GetDigit = "Seven"
Case 8: GetDigit = "Eight"
Case 9: GetDigit = "Nine"
Case Else: GetDigit = ""
End Select
End Function</PRE>

Howdy y'all, I think Yogi's code can be credited to MS. Here's a few thoughts on the subject:

http://www.mrexcel.com/board2/viewtopic.php?topic=32431&forum=2

Some different code and how to impliment. Hope this helps.

Yes, the function is BAHTTEXT(VALUE).However the result that i get in my computer is displayed in another language characters.
The Function on line Help says that this could be adjusted through Regional Settings in Control Panel.

I am not sure how to do that and i do not want to try changing my computer settings in case i mess it up.

Does anyone know the answer to this problem ?

Thanks.
Jaafar.

Thanks A lot, the SpellNumber function is what I have missed!!

Hi Andrew:

You are welcome!

A fellow Excel enthusist had sent me a copy with a request to modify the code to handle Indian Rupees and Paisas (like Dollars and Cents), but the challenge in handling the Indian currency, that instead of working with multiples of thousands, it works with multiples of first a thousand, and then subsequently with hundreds, and of course with different delineations (as compared to millions, billions, trillions, etc.)

Hi Jafaar

The reason you BAHTTEXT function is returned in another language, is that BAHT refers to Thai baht (currency) and returns the number as text and appends baht in thai to the end of the text.

I don't know why this currency was singled out for this treatment, but there is a similar dollar function, but doesn't convert to words.

The reason you BAHTTEXT function is returned in another language, is that BAHT refers to Thai baht (currency) and returns the number as text and appends baht in thai to the end of the text.

Click to expand...

Yes this is correct, there's a hypothesis for this oddity linked below. If you use the function above or the one I linked to (which has the MS variety and Ole's), you should get an English return string.

http://www.j-walk.com/ss/excel/odd/odd20.htm
_________________
Cheers,<font size=+2><font color="red"> Nate<font color="blue"> Oliver</font></font></font>
This message was edited by nateo on 2002-12-12 17:48