Brief Introduction
There is no function in excel that returns word representation of a number. You need to write a custom function for the same. We also call it as User Defined Function.
We have written a sample code to convert numbers into words. This function works 4 digit numbers but it can be extended easily.
How to Use?
1. Open VBA editor. Create a module and paste the below code there.
2. In excel, use this function like this:
=n2s(32)
or
=n2s(A1) [if A1 holds a number]
CODE
Function n2s(num As Integer)
Dim x, diff As Integer
Dim first19names() As String
Dim first19numbers()
If num > 19 Then
If num <= 99 Then
x = 20
n2s = twodigit(num)
Else
If num <= 999 Then
n2s = threedigit(num)
Else
n2s = fourdigit(num)
End If
End If
Else
x = num
first19number = Array(1, 2, 3, 4, 5, 6, 7, 8, 9, 10, 11, 12, 13, 14, 15, 16, 17, 18, 19)
first19names = Split("One Two Three Four Five Six Seven Eight Nine Ten Eleven Twelve Thirteen Fourteen Fifteen Sixteen Seventeen Eighteen Nineteen", " ")
For i = 0 To 18
If x = first19number(i) Then
n2s = first19names(i)
End If
Next
End If
End Function
Function twodigit(num As Integer)
Dim numVal() As Variant
Dim numText() As String
Dim flrval, diff As Integer
numVal = Array(20, 30, 40, 50, 60, 70, 80, 90)
numText = Split("Twenty Thirty Forty Fifty Sixty Seventy Eighty Ninety", " ")
flrval = Application.WorksheetFunction.Floor(num, 10)
For i = 0 To 7
If CInt(numVal(i)) = flrval Then
diff = num - flrval
If diff > 0 Then
twodigit = numText(i) & " " & n2s(diff)
Else
twodigit = numText(i)
End If
Exit For
Else
twodigit = ""
End If
Next
End Function
Function threedigit(num As Integer)
Dim hndrd As String
hndrd = "Hundred"
If num Mod 100 = 0 Then
threedigit = n2s(num / 100) & " Hundred"
Else
threedigit = n2s(Application.WorksheetFunction.Floor(num / 100, 1)) & " Hundred " & n2s(num Mod 100)
End If
End Function
Function fourdigit(num As Integer)
Dim thsnd As String
thsnd = "Thousand"
If num Mod 1000 = 0 Then
fourdigit = n2s(num / 1000) & " Thousand"
Else
fourdigit = n2s(Application.WorksheetFunction.Floor(num / 1000, 1)) & " Thousand " & threedigit(num Mod 1000)
End If
End Function
