Format Numbers

  • Thread starter Thread starter Bryan Hughes
  • Start date Start date
B

Bryan Hughes

Hello,

Is it possible to convert numbers to there coresponding text values?

1 = First
2 = Second
3 = Third
4 = Four
etc.

-Bryan
 
You will have to make a function to do what you want to
do. Here isa sample that does what you want to do.

Public Function NumConverter(ByVal sNumber As String) As
String
Dim sNum As String
Select Case sNumber
Case Is = "0"
sNum = "Zero"
Case Is = "1"
sNum = "One"
Case Is = "2"
sNum = "Two"
Case Is = "3"
sNum = "Thee"
Case Else
sNum = "You did not enter a Number"

End Select
NumConverter = sNum
End Function

Hope this helps,
Doug
 
Hello,

Thanks for the replies. Marshall, I actually want to use ordinal numbers .
It will be used for a msgbox to users when entering in new clint records and
return back to them the ordinal number after doing a count for the record
criteria.

I tried using the function Doug gave as an example but it keep giving me
errors. I can rember what I think it was object not defined.

-Bryan
 
Bryan said:
Thanks for the replies. Marshall, I actually want to use ordinal numbers .
It will be used for a msgbox to users when entering in new clint records and
return back to them the ordinal number after doing a count for the record
criteria.
Click to expand...

Ok here it is, have fun unraveling the word wrapping ;-)

Function TextOrdinal(ByVal lngAmt As Long) As String
Dim astrOrdOnes As Variant, astrOrdTens As Variant, astrOnes
As Variant, astrTens As Variant, astrGroups As Variant
Dim strAmt As String, strResult As String, strGroupAmt As
String, strAnd As String
Dim intCents As Integer, intAmtLen As Integer, intGrp As
Integer

If lngAmt < 0 Then TextOrdinal = "NEGATIVE": Exit
Function
astrOrdOnes = Array(Null, "first", "second", "third",
"fourth", "fifth", "sixth", "seventh", "eighth", "ninth", _
"tenth", "eleventh", "twelfth",
"thirteenth", "fourteenth", "fifteenth", "sixteenth",
"seventeenth", "eighteenth", "nineteenth")
astrOrdTens = Array(Null, "", "twentieth", "thirtieth",
"fortieth", "fiftieth", "sixtieth", "seventieth",
"eightieth", "nintieth")
astrOnes = Array(Null, "one", "two", "three", "four",
"five", "six", "seven", "eight", "nine", _
"ten", "eleven", "twelve", "thirteen",
"fourteen", "fifteen", "sixteen", "seventeen", "eighteen",
"nineteen")
astrTens = Array(Null, "", "twenty", "thirty", "forty",
"fifty", "sixty", "seventy", "eighty", "ninty")
astrGroups = Array(Null, "thousand", "million",
"billion", "trillion") ' "quadrillion", "quintillion",
"sextillion", "septillion")

strAmt = Format$(lngAmt, " 0") ' Convert to string
without fractional part
intAmtLen = Len(strAmt)

If lngAmt = "0" Then
strResult = "zeroth "
Else
If lngAmt >= 100 Then strAnd = "and "
For intGrp = 0 To UBound(astrGroups)
strGroupAmt = Mid$(strAmt, intAmtLen - 3 *
intGrp - 2, 3)
If strGroupAmt = " " Then Exit For
If Val(strGroupAmt) > 0 Then
If strResult = "" Then ' Thousands,
millions, etc.
strResult = (astrGroups(intGrp) + "th
") & ""
Else
strResult = (astrGroups(intGrp) + " ") &
strResult
End If
If Val(Mid$(strGroupAmt, 2)) >= 20 Then
If strResult = "" Then
If Mid$(strGroupAmt, 3, 1) = "0"
Then
strResult = strAnd &
astrOrdTens(Mid$(strGroupAmt, 2, 1))
Else
strResult = strAnd &
((astrTens(Mid$(strGroupAmt, 2, 1)) & ("-" +
astrOrdOnes(Mid$(strGroupAmt, 3, 1)))) + " ")
End If
Else
strResult =
((astrTens(Mid$(strGroupAmt, 2, 1)) &
astrOnes(Mid$(strGroupAmt, 3, 1))) + " ") & strResult
End If
Else ' 0 through 19
If strResult = "" Then
strResult = (strAnd +
astrOrdOnes(Mid$(strGroupAmt, 2, 2)) + " ") & ""
Else
strResult =
(astrOnes(Mid$(strGroupAmt, 2, 2)) + " ") & strResult
End If
End If
If strResult = "" Then ' Hundreds
strResult =
(astrOnes(Val(Left$(strGroupAmt, 1))) + " hundredth ") & ""
Else
strResult =
(astrOnes(Val(Left$(strGroupAmt, 1))) + " hundred ") &
strResult
End If
End If
strAnd = ""
Next intGrp
End If

TextOrdinal = strResult
End Function
 
Back
Top