How to ..

[sbgvp]

Hello,

I'm a new member and need assistance! How could I have the value of a certain cell which is a number, to another cell in words. Is that possible? For example, when I type in cel B2 the number 32 can I have in cell C2 the words "THIRTY TWO"? Of course, if I type in cell B2 another number, for example 45 I must have "FORTY FIVE" in cell C2.Thank you in advance!

[swatsp0p]

You can use this User Defined Function to do what you ask: copy and paste this into a new module (do you need help with that?) in the worksheet where you will be using it:

[vba]
'****************' 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[/vba]

Note: to make this spell out "Dollars" and "Cents", remove the REMARK apostrophes from the Select Case statements in the Main Function section.

In Cell C2, enter =spellnumber(B2)
------------------------------------------------------

Credits: I have had this code around for a long time, the source of which is not documented in the code. My thanks to the unnamed author of this code. If the author sees this, feel free to let us know who you are. Thank you.

-------------------------------------------------------

Good Luck

Bruce

[sbgvp]

Bruce I'm afraid that I realy need help for copy and paste this into a new module!Sorry to ask that much and many thanks.

[swatsp0p]

Open the Excel file (workbook) that you will be using this code in. Press ALT+F11 (this will open the Visual Basic editor). On the main menu at the top, click Insert and select "Module". This will open a blank window in the right hand pane. Copy the code from this post and Paste it here. Close the VB editor (ALT+Q)

Your UDF should now work just fine.

In cell B2 enter 43
In cell C2 enter =spellnumber(B2) and press enter. The cell should read "Forty Three" (without the quotes, of course)

Does it work for you?

Bruce

[sbgvp]

Now it works fine and it is really amazing! Thank you so much!

Sophocles

Athens
Greece

[swatsp0p]

I am glad it works for you. Thanks for the feedback, it is always appreciated.

Cheers!

Bruce

[bellman]

what can I do to add a dash to numbers like 25 (exp. twenty-five) while
avoiding adding a dash to 5 (five)?

"swatsp0p" wrote:

>
> I am glad it works for you. Thanks for the feedback, it is always
> appreciated.
>
> Cheers!
>
> Bruce
>
>
> --
> swatsp0p
>
>
> ------------------------------------------------------------------------
> swatsp0p's Profile: http://www.excelforum.com/member.php...o&userid=15101
> View this thread: http://www.excelforum.com/showthread...hreadid=393261
>
>

[swatsp0p]

change the 'GetTens' module portion to this:[vba]
'*********************************************
' 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
If Val(Right(TensText, 1)) = 0 Then
Result = Result & GetDigit(Right(TensText, 1)) 'Retrieve ones place
Else
Result = Result & "-" & GetDigit(Right(TensText, 1))
'adds hyphen to number e.g. "twenty-one"
End If
GetTens = Result
End Function
[/vba]

Does this work for you?