Reply
 
LinkBack Thread Tools Search this Thread Display Modes
  #1   Report Post  
bellman
 
Posts: n/a
Default convert numbers to text

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

'****************' 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

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 -- swatsp0p
------------------------------------------------------------------------
swatsp0p's Profile:
http://www.excelforum.com/member.php...o&userid=15101 View this
thread: http://www.excelforum.com/showthread...hreadid=393261


Reply
Thread Tools Search this Thread
Search this Thread:

Advanced Search
Display Modes

Posting Rules

Smilies are On
[IMG] code is On
HTML code is Off
Trackbacks are On
Pingbacks are On
Refbacks are On


Similar Threads
Thread Thread Starter Forum Replies Last Post
How do I convert excel file into ASCII text file with alignment? Rosaiah Excel Discussion (Misc queries) 2 June 27th 05 12:17 PM
convert a range of lowercase text to upper text or vice versa jackdaw Excel Worksheet Functions 2 May 16th 05 09:31 PM
How to reformat numbers stored as text (apostrophe at beginning) Dave Excel Discussion (Misc queries) 1 May 11th 05 02:34 AM
Sort or Filter option? Mcobra41 Excel Worksheet Functions 3 February 23rd 05 08:22 PM
Convert text to numbers gennario Excel Discussion (Misc queries) 6 January 11th 05 12:56 AM


All times are GMT +1. The time now is 11:24 AM.

Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
Copyright ©2004-2024 ExcelBanter.
The comments are property of their posters.
 

About Us

"It's about Microsoft Excel"