Changing numbers into words WIHTOUT "dollars" and "cents"

BELFRED6

Board Regular
Joined
Oct 31, 2008
Messages
110
Changing numbers into words is an issue that has been tackled many times on that board. However suggestions always propose alternatives to return numbers with the words "dollars" and "cents" attached.

Is there any way to have a code or function that would return the translation of the figure without any unit attached?
For example: 250 would return two hunderd and fifty and nothing more.

Thank you.
 

Excel Facts

How to create a cell-sized chart?
Tiny charts, called Sparklines, were added to Excel 2010. Look for Sparklines on the Insert tab.
<meta http-equiv="Content-Type" content="text/html; charset=utf-8"><meta name="ProgId" content="Word.Document"><meta name="Generator" content="Microsoft Word 11"><meta name="Originator" content="Microsoft Word 11"><link rel="File-List" href="file:///C:%5CDOCUME%7E1%5CADMINI%7E1%5CLOCALS%7E1%5CTemp%5Cmsohtml1%5C01%5Cclip_filelist.xml"><!--[if gte mso 9]><xml> <w:WordDocument> <w:View>Normal</w:View> <w:Zoom>0</w:Zoom> <w:PunctuationKerning/> <w:ValidateAgainstSchemas/> <w:SaveIfXMLInvalid>false</w:SaveIfXMLInvalid> <w:IgnoreMixedContent>false</w:IgnoreMixedContent> <w:AlwaysShowPlaceholderText>false</w:AlwaysShowPlaceholderText> <w:Compatibility> <w:BreakWrappedTables/> <w:SnapToGridInCell/> <w:WrapTextWithPunct/> <w:UseAsianBreakRules/> <w:DontGrowAutofit/> </w:Compatibility> <w:BrowserLevel>MicrosoftInternetExplorer4</w:BrowserLevel> </w:WordDocument> </xml><![endif]--><!--[if gte mso 9]><xml> <w:LatentStyles DefLockedState="false" LatentStyleCount="156"> </w:LatentStyles> </xml><![endif]--><style> <!-- /* Style Definitions */ p.MsoNormal, li.MsoNormal, div.MsoNormal {mso-style-parent:""; margin:0in; margin-bottom:.0001pt; mso-pagination:widow-orphan; font-size:12.0pt; font-family:"Times New Roman"; mso-fareast-font-family:"Times New Roman";} @page Section1 {size:8.5in 11.0in; margin:1.0in 1.25in 1.0in 1.25in; mso-header-margin:.5in; mso-footer-margin:.5in; mso-paper-source:0;} div.Section1 {page:Section1;} --> </style><!--[if gte mso 10]> <style> /* Style Definitions */ table.MsoNormalTable {mso-style-name:"Table Normal"; mso-tstyle-rowband-size:0; mso-tstyle-colband-size:0; mso-style-noshow:yes; mso-style-parent:""; mso-padding-alt:0in 5.4pt 0in 5.4pt; mso-para-margin:0in; mso-para-margin-bottom:.0001pt; mso-pagination:widow-orphan; font-size:10.0pt; font-family:"Times New Roman"; mso-ansi-language:#0400; mso-fareast-language:#0400; mso-bidi-language:#0400;} </style> <![endif]--> Start Microsoft Excel.
Press ALT+F11 to start the Visual Basic Editor.
On the Insert menu, click Module.
Type the following code into the module sheet.
Code:
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
<o:p> </o:p>
Source : http://en.kioskea.net/forum/affich-50057-convert-number-to-text-in-excel-sheet
 
Upvote 0
Thank you, Chris.
I did what you said, then, typed 150 in cell A1 and =spellnumber(A1) in cell B1. I got a #value! message.

Moreover, in your code, there seems to have dollars and cents info. My goal is to get a code that would change numbers into words WITHOUT any dollar, cent, euro type of words.

Any idea of what I did wrong?
 
Upvote 0

Forum statistics

Threads
1,213,561
Messages
6,114,312
Members
448,564
Latest member
ED38

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top