Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Converitng Numbers into text (4 as four)
How can i convert text into numbers such as
29 as twenty nine |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Converitng Numbers into text (4 as four)
Hi
see: http://www.xldynamic.com/source/xld.xlFAQ0004.html -- Regards Frank Kabel Frankfurt, Germany Numbers Problem wrote: How can i convert text into numbers such as 29 as twenty nine |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Converitng Numbers into text (4 as four)
Some suggestions at http://www.xldynamic.com/source/xld.xlFAQ0004.html
-- HTH ------- Bob Phillips "Numbers Problem" <Numbers wrote in message ... How can i convert text into numbers such as 29 as twenty nine |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Converitng Numbers into text (4 as four)
Hi,
I had made a function quite sometime back for this. It is not very well orgranized, but well it still works. It is only for integers (Long), and not for floating points. If the number entered is higher than Long data type permits it will give error "#Num". You can use it up to 2 billions approx. Add a module and cut pase the below code in to it. You can use the function in worksheet, sameway like a normal workseet function formula. e.g. =numTotext(A1) where the number will be in cell A1 Or you can use it in code, yourvariable = numbTotext(number) Sharad Public Function numTotext(numB As Long) As String Dim numbStr As String Dim s1 As String, s10 As String, s100 As String, _ s1000 As String, sMillion As String, sBillion As String numbStr = numB Select Case Right(numbStr, 1) Case 0 s1 = "Zero" Case 1 s1 = "One" Case 2 s1 = "Two" Case 3 s1 = "Three" Case 4 s1 = "Four" Case 5 s1 = "Five" Case 6 s1 = "Six" Case 7 s1 = "Seven" Case 8 s1 = "Eight" Case 9 s1 = "Nine" End Select If Len(numbStr) = 1 Then numTotext = s1 GoTo bye: End If If s1 = "Zero" Then s1 = "" Select Case Left(Right(numbStr, 2), 1) Case 0 s10 = "" Case 1 s10 = "Ten" Case 2 s10 = "Twenty" Case 3 s10 = "Thirty" Case 4 s10 = "Forty" Case 5 s10 = "Fifty" Case 6 s10 = "Sixty" Case 7 s10 = "Seventy" Case 8 s10 = "Eighty" Case 9 s10 = "Ninety" End Select s10 = s10 & " " & s1 If Left(s10, 3) = "Ten" Then Select Case Right(numbStr, 1) Case 1 s10 = "Eleven" Case 2 s10 = "Twelve" Case 3 s10 = "Thirteen" Case 4 s10 = "Fourteen" Case 5 s10 = "Fifteen" Case 6 s10 = "Sixteen" Case 7 s10 = "Seventeen" Case 8 s10 = "Eighteen" Case 9 s10 = "Nineteen" End Select End If If Len(numbStr) = 2 Then numTotext = s10 GoTo bye: End If If s10 = " " Then s100 = numTotext(Left(Right(numbStr, 3), 1)) & " Hundred" Else s100 = numTotext(Left(Right(numbStr, 3), 1)) & " Hundred and " End If If Left(Right(numB, 3), 1) = 0 Then s100 = "" s100 = s100 & s10 If Len(numbStr) = 3 Then numTotext = s100 GoTo bye: End If s1000 = numTotext(Right((Left(numB, Len(numbStr) - 3)), 3)) & " Thousand, " If Right((Left(numB, Len(numbStr) - 3)), 3) = 0 Then s1000 = "" s1000 = Trim(s1000 & s100) If Right(s1000, 1) = "," Then s1000 = Left(s1000, Len(s1000) - 1) If Len(numbStr) 3 And Len(numbStr) < 7 Then numTotext = s1000 GoTo bye: End If sMillion = numTotext(Right((Left(numB, Len(numbStr) - 6)), 3)) & " Million, " If Right((Left(numB, Len(numbStr) - 6)), 3) = 0 Then sMillion = "" sMillion = Trim(sMillion & s1000) If Right(sMillion, 1) = "," Then sMillion = Left(sMillion, Len(sMillion) - 1) If Len(numbStr) 6 And Len(numbStr) < 10 Then numTotext = sMillion GoTo bye: End If sBillion = numTotext(Left(numB, 1)) & " billion, " sBillion = Trim(sBillion & sMillion) If Right(sBillion, 1) = "," Then sBillion = Left(sBillion, Len(sBillion) - 1) numTotext = sBillion bye: End Function *** Sent via Developersdex http://www.developersdex.com *** Don't just participate in USENET...get rewarded for it! |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Using TEXT and &TEXT - display numbers with commas, underline text | Excel Discussion (Misc queries) | |||
how to extract numbers from imported cell with text and numbers? | Excel Discussion (Misc queries) | |||
VLOOKUP should compare numbers stored as text to plain numbers. | Excel Worksheet Functions | |||
Convert numbers stored as text to numbers Excel 2000 | Excel Discussion (Misc queries) | |||
How to convert Excel imported numbers from text to numbers? | Excel Discussion (Misc queries) |