how do i change an integer to text equivalent (1 to "one", etc)   

-- 
seeking help

date: Mon, 11 Aug 2008 10:58:05 -0700   author:   vjp

RE: how do i change an integer to text equivalent (1 to "one", etc)   

To: VJp,

If not needed, you can skip "SpellCurrency" and go to "SpellNumber".

'
' SpellCurrency
' Accepts a string of digits with or without commas & with or without a 
dollar sign
'
Function SpellCurrency(ByVal sNum As String) As String
    Dim sDollars As String, sCents As String
    Dim iPos As Integer
    sNum = Replace(sNum, "$", "")
    sNum = Replace(sNum, ",", "")
    iPos = InStr(1, sNum, ".")
    If iPos > 0 Then
        sDollars = Left(sNum, iPos - 1)
        If Len(sNum) > iPos Then sCents = Right(sNum, Len(sNum) - iPos)
        If Len(sCents) > 2 Then sCents = Left(sCents, 2)
    Else
        sDollars = sNum
    End If
    sDollars = SpellNumber(sDollars)
    If Len(sCents) > 0 Then sCents = SpellNumber(sCents)
    If Len(sCents) > 0 Then
        If sCents = "One" Then
            sCents = "One Cent"
        Else
            sCents = sCents & " Cents"
        End If
    Else
        sCents = "No Cents"
    End If
' Rule: When writing out large numbers of five or more digits before the 
decimal point,
' use a comma where the comma would appear in the figure format. Use the 
word and only
' where the decimal point appears in the figure format.
    sDollars = Replace(sDollars, " and ", ", ")
    sDollars = Replace(sDollars, "Hundred,", "Hundred")
    SpellCurrency = sDollars & " Dollars and " & sCents
End Function

'
' SpellNumber converts numbers from 1 to 999,999,999,999,999 into words
' Accepts a string of digits with or without commas
' The function Convert1To999 is called.
'
Function SpellNumber(ByVal sNum As String) As String
    Const sList As String = ", Trillion, Billion, Million, Thousand,"
    Dim sStr As String, sReturn As String, iLen As Integer
    Dim iIndex As Integer, iPos As Integer
    Dim v As Variant
'The Variant array 'v' will have six elements; elements 0 & 5 will be empty
'The other elements are preceeded by a space character.
    v = Split(sList, ",")
    sNum = Replace(sNum, ",", "")
    iLen = Len(sNum)
'Two zeros are added to the beginning of the sNum string,
'since this string is processed in groups of three characters.
    sNum = "00" & sNum
    For iIndex = 1 To 5
        iPos = (5 - iIndex) * 3
        If iLen > iPos Then
            iPos = Len(sNum) - (iPos + 2)
            sStr = Convert1To999(Mid(sNum, iPos, 3))
            If Len(sStr) > 0 Then
                If Len(sReturn) > 0 Then sReturn = sReturn & " and "
                sReturn = sReturn & sStr & v(iIndex)
            End If
        End If
    Next iIndex
    SpellNumber = sReturn
End Function
'
' Convert1To999 converts numbers from 1 to 999 into words
' The function ConvertDigit is called.
'
Private Function Convert1To999(ByVal sNum As String) As String
    Const sDigits As String = "Zero One Two Three Four Five Six Seven Eight 
Nine"
    Const sTeens As String = "Ten Eleven Twelve Thirteen Fourteen Fifteen 
Sixteen Seventeen Eighteen Nineteen"
    Const sDecades As String = "Zero Ten Twenty Thirty Forty Fifty Sixty 
Seventy Eighty Ninety"
    Dim sStr As String, sStr2 As String
    Dim iNum As Integer
    Dim v As Variant
      
    Select Case Len(sNum)
' converts numbers 1 to 9
    Case Is = 1
        iNum = ConvertDigit(sNum)
        If iNum > 0 Then
            v = Split(sDigits)
            Convert1To999 = v(iNum)
        End If
' converts numbers 10 to 99
    Case Is = 2
        iNum = CInt(sNum)
        If iNum > 9 Then
            If iNum < 20 Then
                v = Split(sTeens)
                Convert1To999 = v(ConvertDigit(Right(sNum, 1)))
            Else
                sStr = Convert1To999(Right(sNum, 1))
                If Len(sStr) > 0 Then sStr = " " & sStr
                v = Split(sDecades)
                Convert1To999 = v(ConvertDigit(Left(sNum, 1))) & sStr
            End If
        Else
            Convert1To999 = Convert1To999(Right(sNum, 1))
        End If
' converts numbers 100 to 999
    Case Is = 3
        iNum = CInt(sNum)
        If iNum > 99 Then
            v = Split(sDigits)
            sStr = v(ConvertDigit(Left(sNum, 1))) & " Hundred"
        End If
        sStr2 = Convert1To999(Right(sNum, 2))
        If Len(sStr) > 0 And Len(sStr2) > 0 Then
            Convert1To999 = sStr & " and " & sStr2
        Else
            Convert1To999 = sStr & sStr2
        End If
    End Select
End Function

' ConvertDigit converts a digit-string to number (0-9)
'
Private Function ConvertDigit(ByVal sChar As String) As Byte
    ConvertDigit = Asc(sChar) - Asc("0")
End Function

Steven Craig Miller

"vjp" wrote:

> 
> -- 
> seeking help

date: Mon, 11 Aug 2008 11:56:48 -0700   author:   StevenM stevencraigmiller(at)comcast(dot)net

Re: how do i change an integer to text equivalent (1 to "one", etc)   

Hi vjp,

You could put the value into a formula field with a Cardtext switch. For example, using Insert|Field, insert '= 1 \*Cardtext' into 
the field code box.

-- 
Cheers
macropod
[MVP - Microsoft Word]


"vjp"  wrote in message news:2C81ADA9-4675-441A-A2BF-65A268ADC1E8@microsoft.com...
>
> -- 
> seeking help

date: Tue, 12 Aug 2008 07:09:34 +1000   author:   macropod lid