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?action=getinfo&userid=15101 View this 
thread: http://www.excelforum.com/showthread.php?threadid=393261 


0
bellman (2)
10/4/2005 9:28:04 PM
excel.misc 78881 articles. 5 followers. Follow

0 Replies
288 Views

Similar Articles

[PageSpeed] 40

Reply:

Similar Artilces:

Converting Claris Impact files (old Apple files) to M'soft Visio"
Hello I understand the the best way to access old Claris Impact files are to convert using m'soft visio. I'm completely new to this application & will be grateful if anyone can take me throughm the process of doing this. I've had a look at the Versiontracker website on salvaging claris impact files but I couldn't work out how it could help me! I really need some step by step help. I've got Visio 2007 installed on my XP Pro computer Many thanks Perhaps this link might help: http://www.thefreelibrary.com/Orbital+Successfully+Completes+File+Converter+to+Allow+Cla...

How to customize Outlook 2003, e.g. remove button text in toolbar?
I cannot find where to do that :( Thanks, Tien, Right click on the toolbar, select Customize, then right click on the button, select Default style. --� Milly Staples [MVP - Outlook] Post all replies to the group to keep the discussion intact. Due to the (insert latest virus name here) virus, all mail sent to my personal account will be deleted without reading. After searching google.groups.com and finding no answer, Do Quyet Tien asked: | I cannot find where to do that :( | Thanks, | Tien, Thanks, but why is it easy like customize IE toolbars, just select option "No text labels...

Converting Microsoft Office 2003 Trial into a full version
I have bought a Sony Vaio PCG-GRT360ZG notebook with installed Microsoft Office 2003. I registered Microsoft Office 2003 trial version. Now I am trying to convert it into a full version. Unfortunately I am getting message that my product key (from Certificate of Authenticity) is invalid. My trial version expires in Jan 2005. What should I do ? My product key from Certificate of Authenticity label is : J8FHJ-Q6BJM-G6PVD-FWM2B-PW6J8. Thank you very much Peter. ...

Converting Dates from YYMMDD to MMDDYYYY
Hi All; I'm looking for a format function that will convert a date in the text format of YYMMDD to text format of MMDDYYYY. Any help would be GREATLY appreciated! Thanks in advance! You can't do it using a Format function, but try: Function SwitchDateFormat(YYMMDD As String) As String Dim strDay As String Dim strMonth As String Dim strYear As String If Len(YYMMDD) = 6 Then strYear = Left$(YYMMDD, 2) strMonth = Mid$(YYMMDD, 3, 2) strDay = Right$(YYMMDD, 2) If strYear < "30" Then strYear = "19" & strYear Else strYear =...

How can I prioritize tasks by number in Outlook 2007?
I am trying to create a custom priority for my tasks in Outlook 2007, but I'm not able to edit the field that was created when I changed the settings to include a custom priority. I'm trying to put a number into it, so that I can sort my tasks in a certain category by number, but I'm not able to. We aren't watching you and your explanation is missing details. Where in Outlook are you trying to view the Tasks? There are 3 places to see Tasks. If in the Task Folder, what View are you in? If in Tasks folder you probably need to turn on in-line editing. Regards Judy Gle...

Calculate number of months
Hello, I have a field where I am trying to implement a calculation. I want it to take the date in a cell and subtract it from today's date to show me the total number of months between those two dates. So right now, it looks like =(TODAY())-D3 and it returns 167 - the total number of days. How do I make it show me months? THANK YOU IN ADVANCE =Month(Today())-Month(D3) "TxWebDesigner" <beverly@beverlylanedesigns.com> schreef in bericht news:e3fR#jXWJHA.5032@TK2MSFTNGP05.phx.gbl... > Hello, > > I have a field where I am trying to implement a calculation...

I have a problem with getting a total Sum of numbers (URGENT)
I'd really appreciate if someone could help me here. I am using Microsoft Excel 2002 on a Microsoft Office XP system. I a trying to develop a minor league baseball schedule, but am having som problems. I have attached a copy of my spreadsheet to give everyone some idea o what I'm doing here. I have where the fans list the "RUNS, HITS & ERRORS) of both teams an for each game of the series. I then have it set up in the TOTA WON/LOST column where if the T-Bones RUNS are more than the SALTDOG runs, a "1" appears in the WON column. If the SALTDOGS have a highe numbe...

how do i change or remove a signiture that is under outo text hea.
Someone please help i am using outlook 2003 and i am triyng to change a signiture that is under auto text. to get into it i open a message push insert then outo text then signiture. I want to change it. I have closed the untilted message gone to tools then options then mail format then signitures then remove all the signitures but the signiture under insert and outo text is still there See if this helps: http://office.microsoft.com/en-us/word/HP051860291033.aspx -- Bill R "nearly bald with frustration" <nearly bald with frustration@discussions.microsoft.com> wrote i...

Counting Texts
If cell A1 contains a paragraph of texts and if I want to count just letter "W"s (Upper or lower case), How can I achieve this? Example: A1 contains "How now brown cow" the formula should return "4". Thank you. Write a macro and use the VBA functions Instr(strName, "w") together with Split(strName, "w") and count the number of times that it finds "w" or "W" Chris "Keith" <Keith@discussions.microsoft.com> wrote in message news:CC60AA13-B569-4C55-BDC7-1B6295CDAFB9@microsoft.com... > If cell A1 co...

Emails missing text
We have about 80 users on our Exchange server. We have 2 users that have an issue with reading their email. It appears that when they open the email the right side of the email/text is gone. When I shadow their session I can see the entire email but they cannot. It is as if they right section of the text of the email was deleted. I'm sorry I'm not explaining this well. Any feedback is appreciated. Thanks ...

Export excel file to semicolon delimited text file
Hello all. We have the need to export a file from excel to a semicolon delimited text file. Is there a way to do this from Excel directly? If not, does anyone have any other suggestions? TIA for your help. If you change your regional settings to use the semicolon as your list separator, you can save as .csv and it should work. But this may affect other programs, too--since it's a windows setting. windows start button|settings|control panel|regional and language options| regional options tab|customize button near the bottom. On the other hand, if you don't want to fiddle with...

Converting Adobe PageMaker 6.5 files
Does anyone know if you can convert Adobe PageMaker version 6.5 files so that I can use MS Publisher 2007 to edit these files? I suspect the only way is the copy/paste solution. If you don't have PageMaker, maybe Adobe has help. There is a forum here that might have a solution: http://forums.adobe.com/thread/460491;jsessionid=39FEE1914BD4E0C9DF28733ADF71F413.node0?tstart=0 -- Mary Sauer http://msauer.mvps.org/ "SmithinSA" <SmithinSA@discussions.microsoft.com> wrote in message news:5CCB0500-7E15-4EDC-BC04-775B41F13D8F@microsoft.com... > Does anyone know if you can ...

number in system using arrivals and departures
Is there and easy way to figure out the total number of persons in a system if have access to the arrival and departure times. I have tried to use counts with if statements, but I keep getting the number 1. I know this is not correct. Is there any special macros or functions Thanks Hi you may provide some more details about your data. Could you post some example rows (plain text - no attachments please) and describe your expecte result -- Regards Frank Kabel Frankfurt, Germany megv wrote: > Is there and easy way to figure out the total number of persons in a > system if have access...

how would you calculate the number of hits to your website
Hi ,' can some one tell to me the answer of this question On a website, how would you calculate the number of hits to your website ...

text size in reading pane
Where is the font selection / text size control for the Outlook 2003 reading pane (incoming emails)? I just bought a brand new dell with windows xp and all and I can't even increase the text size of incoming emails as I could with older versions of Outlook Express. I have been trying to figure this out for around 15 hours now and have come up empty. What kind of progress is this anyway???? A program rendered unable to accomplish such a simple practical thing like that!! Im very upset. My vision is poor. Unless I get an answer soon, I am considering a return of this unit to dell...

can't see text in any office files anymore
Version: 2008 Operating System: Mac OS X 10.6 (Snow Leopard) Processor: Intel on my macbookpro: can't see text in any office files anymore (word, powerpoint, excel). only when using preview function from OSX. <br> what's wrong??? <br> do I have to re-install the all suite? any quick fix? ...

columns changed to numbers instead of letters?
I noticed my excel clumns have changed from letters to numbers and forumlas now look like =RC[-1]*R[-3]C[6] I was trying to make the R[-3]C[6] static (using the $) but it errors. Thanks Mike In Excel Options uncheck "R1C1 Reference Style" Gord Dibben MS Excel MVP On Thu, 28 Oct 2010 20:25:30 -0400, Mike <no_please@not.com> wrote: >I noticed my excel clumns have changed from letters to numbers and >forumlas now look like =RC[-1]*R[-3]C[6] >I was trying to make the R[-3]C[6] static (using the $) but it errors. >Thanks >Mike ...

A way to increase a number in a cell by using the + key
Hi, i do a lot of data entry and am looking for a way to increase the number in a cell (or range of cells) so that i dont have to enter the new number in each time. I have columns that separate items and i use the sheet to show the total count for each item in it's row. eg Item 1 Item 2 Item 3 2 5 4 I'd like to use the + key only to increase the counts for each item. Did i make sense?? It's a Friday and my mind isn't quite up for a lot of detailed explanations, lol. Any assistance would be appreciated. Tim That would require VBA How far are you will...

Help Creating A Formula To Copy and Paste Text
Hi everyone. Thanks in advance for any help you may have. I need to create a formula that searches for a specific word in a column then cut the word and paste it to another column. Of course this would be simple if it was the only word in the column, but there is other text that I would like to remain in the orginal column.. Thanks again, Mary You have a reply at your other thread. Mary wrote: > > Hi everyone. Thanks in advance for any help you may have. > > I need to create a formula that searches for a specific word in a > column then cut the word and paste it to ...

reference number #2
--------------------------------- Where does the information in the "reference number" field in the transaction table come from? I see tranactions with reference numbers - the first part of which appears to be the year, month and day - but I can't figure out the second part. It is nothing that we have intentionally entered while ringing out the customer. Pls help. -Tracey ##-----------------------------------------------## Newsgroup Access Courtesy http://www.rockryno.com/ Tax and Accounting Software Forums Web and RSS access to your favorite newsgroup - microsoft.p...

number changes to date ... how do I change
I am trying to do an "age" range: 1-9 10-19 20-29 Instead I get September 9, 2004 or October 19 I can't figure out how to change it. Please help! Thank you! kk Format->Cells->Number Change the type to Text, and it should keep everything the way you want it. If you don't specify a type, Excel takes a guess based on what you enter. Hope that helps. -Bob --- Message posted from http://www.ExcelForum.com/ ...

Rich Text formatting in Access 2007
In Access 2007, is it possible to add rich text formatting to text in a memo field (or mixed formatting within any text field). I have just upgraded to the new version of Access, and it appears that this feature still has not been added. Is this correct? (If so, why?) If it is not possible to format individual words within a field in Access, , is there a simple, free add-in that will enable Access to do this kind of basic formatting, such as adding italic, bold, and underlining? Thanks very much for any information you may have. Bob Rich Text format for Memos in: Tables - Look in t...

Add numbers accross columns after stripping away text
I have the following data in a spreadsheet: A1 B1 C1 D1 E1 F1 4.5f 6f 3.5f 3f 7.25f I need to be able to add the numbers together to give me 24.25, i.e. strip the fs away. The numbers will always be less than 10 and the there will only ever be .25 or.5 or .75 after the number (I don't know whether that is significant?). If anyone can show me how to do this I would be very grateful. I have been messing around with MID and FIND to no avail and then started thinking that SUMPRODUCT might have to get involved but it all got a bit m...

Random numbers, Canadian Zip Code style #2
Honestly, I don't even know where to begin -- Shocke ----------------------------------------------------------------------- Shocked's Profile: http://www.excelforum.com/member.php?action=getinfo&userid=1489 View this thread: http://www.excelforum.com/showthread.php?threadid=26523 ...

Mottled Text in GIF Image
I'm trying to put a GIF image into my signature page. The image is a Static GIF Image that consists of a graphic and some text. When I go to write the email it appears correctly, however when I send the email the text portion is mottled. I am sending the email to myself and this still occurrs so it is the same monitor, computer, software etc. Anyone have any ideas on what is causing this. Should I covert this image to a jpg or some other format? Thanks in advance ...