Number to word

 

The number to word formula in Excel is a custom formula that can be created using a combination of built-in functions to convert a numeric value into its equivalent words in English.

Here is an example of the formula:

scss
=IF(A1<0,"Negative ", "") & ConvertNumberToWords(ABS(A1))

In this formula, "A1" refers to the cell containing the numeric value that needs to be converted to words.

The first part of the formula uses the "IF" function to check if the number is negative. If it is negative, the word "Negative" will be added to the output, and if it is positive, an empty string will be added.

The second part of the formula uses a user-defined function called "ConvertNumberToWords" to convert the numeric value to words. This function takes a numeric input and returns the equivalent words in English.

The "ConvertNumberToWords" function can be defined in a VBA module as follows:

vbnet
Function ConvertNumberToWords(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 "
    
    MyNumber = Trim(Str(MyNumber))
    
    DecimalPlace = InStr(MyNumber, ".")
    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
    
    ConvertNumberToWords = Dollars & "Dollars"
    
    If Cents <> "" Then
        ConvertNumberToWords = ConvertNumberToWords & " and " & Cents & " Cents"
    End If
End Function

Function GetHundreds(ByVal MyNumber)
    Dim Result As String
    
    If Val(MyNumber) = 0 Then Exit Function
    MyNumber = Right("000" & MyNumber, 3)
    
    If Mid(MyNumber, 1, 1) <> "0" Then
        Result = GetDigit(Mid(MyNumber, 1, 1)) & " Hundred "
    End If
    
    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

Function GetTens(ByVal TensText)
    Dim Result As String
    
    Result = ""
    
    If Val(Left(TensText, 1)) = 1 Then
        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"