NEED YOUR HELP AS EARLY AS POSSIBLE

[nomirajput]

Dear users

I need your help... i need to know any function which can convert any numeric figure in alphabets.. i mean if i write 25 in a cell then get the result of "Twenty Five" in the adjacent cell...
please do reply as soon as possible

 

[NBVC]

I am not a VBA programmer,

but I found this one a while age and used it.

Just paste this in a new VB module

and use the NumToStr() function in your spreadsheet, ie =NumToStr(500) would return "five hundred"

Private Function NumToStr(ByVal intNum As Long) As String
    Dim strOut As String        'Output string
    Dim strTemp As String       'Temporary storage string
    Dim iPart As Long           'Length of number in decimal
    Dim bHundred As Boolean     'After hundred, need 'and' for tens (one hundred AND twenty one)
    Dim bThousand As Boolean    'After thousand, need 'and' for tens (one thousand AND twenty one)
    Dim i As Long               'Loop counter
        
    iPart = Len(CStr(intNum))
    
    For i = 1 To iPart
        
        Select Case (iPart - i) Mod 3
        'Billions, millions, and thousands
        Case 0
            If strOut = "" Then strOut = GetUnits((intNum \ (10 ^ (iPart - i))) Mod 10)
            
            If Right$(strOut, 1) <> "," Then
                Select Case (iPart - i)
                Case 3
                    bThousand = True
                    strOut = strOut & " thousand,"
                Case 6
                    strOut = strOut & " million,"
                Case 9
                    strOut = strOut & " billion,"
                End Select
            End If
            bHundred = False
            
        'Tens, but we also use this to do units as well (for numbers like eleven)
        Case 1
            strTemp = GetTens((intNum \ (10 ^ (iPart - i - 1))) Mod 100)
            If (LenB(strTemp) > 0) Then
                If bThousand Or bHundred Then
                    strTemp = " and " & strTemp
                Else
                    strTemp = " " & strTemp
                End If
            End If
            strOut = strOut & strTemp
        
        'Hundreds
        Case 2
            strTemp = GetUnits((intNum \ (10 ^ (iPart - i))) Mod 10)
            If LenB(strTemp) > 0 Then
                strOut = strOut & " " & strTemp & " hundred"
                bHundred = True
            End If
        End Select
        
    Next i
    
    If Right$(strOut, 1) = "," Then strOut = Left$(strOut, Len(strOut) - 1)
    
    NumToStr = strOut
End Function

Private Function GetTens(ByVal theNum As Long) As String
    Select Case theNum
    Case Is < 10
        GetTens = GetUnits(theNum)
    Case 10
        GetTens = "ten"
    Case 11
        GetTens = "eleven"
    Case 12
        GetTens = "twelve"
    Case 13
        GetTens = "thirteen"
    Case 15
        GetTens = "fifteen"
    Case 18
        GetTens = "eighteen"
    Case 14, 16, 17, 19
        GetTens = GetUnits(theNum Mod 10) & "teen"
    Case 20 To 29
        GetTens = "twenty " & GetUnits(theNum Mod 10)
    Case 30 To 39
        GetTens = "thirty " & GetUnits(theNum Mod 10)
    Case 40 To 49
        GetTens = "forty " & GetUnits(theNum Mod 10)
    Case 50 To 59
        GetTens = "fifty " & GetUnits(theNum Mod 10)
    Case 80 To 89
        GetTens = "eighty " & GetUnits(theNum Mod 10)
    Case 60 To 79, 90 To 99
        GetTens = GetUnits(theNum \ 10) & "ty " & GetUnits(theNum Mod 10)
    End Select
End Function
Private Function GetUnits(ByVal theNum As Long) As String
    Select Case theNum
    Case 1
        GetUnits = "one"
    Case 2
        GetUnits = "two"
    Case 3
        GetUnits = "three"
    Case 4
        GetUnits = "four"
    Case 5
        GetUnits = "five"
    Case 6
        GetUnits = "six"
    Case 7
        GetUnits = "seven"
    Case 8
        GetUnits = "eight"
    Case 9
        GetUnits = "nine"
    End Select
End Function