Convert Numbers to Words in Excel Instantly: VBA Add-In Guide by Techbikash28

Copy the below code for Convert numbers to words in Excel in VBA

Function NumberToWords(ByVal MyNumber)
Dim Units As String
Dim Teens As String
Dim Tens As String
Dim Thousands As String
Dim Temp As String
Dim DecimalPlace As Integer
Dim Count As Integer

ReDim Place(9) As String
Place(2) = ” Thousand “
Place(3) = ” Million “
Place(4) = ” Billion “
Place(5) = ” Trillion “

‘ Convert MyNumber to string and trim white space.
MyNumber = Trim(CStr(MyNumber))

‘ Find Position of decimal place.
DecimalPlace = InStr(MyNumber, “.”)

‘ Convert Substring before decimal point.
If DecimalPlace > 0 Then
Temp = Left(MyNumber, DecimalPlace – 1)
Else
Temp = MyNumber
End If

Count = 1
Do While Temp <> “”
Hundreds = Right(Temp, 3)
If Len(Temp) > 3 Then
Temp = Left(Temp, Len(Temp) – 3)
Else
Temp = “”
End If
If Hundreds <> “” Then
TempStr = “”
If Val(Hundreds) <> 0 Then
Hundreds = Right(“000” & Hundreds, 3)
‘ Convert hundreds place.
If Mid(Hundreds, 1, 1) <> “0” Then
TempStr = GetDigit(Mid(Hundreds, 1, 1)) & ” Hundred “
End If
‘ Convert tens and ones place.
If Mid(Hundreds, 2, 1) <> “0” Then
TempStr = TempStr & GetTens(Mid(Hundreds, 2))
Else
TempStr = TempStr & GetDigit(Mid(Hundreds, 3))
End If
End If
NumberToWords = TempStr & Place(Count) & NumberToWords
End If
Count = Count + 1
Loop

‘ Convert Substring after decimal point.
If DecimalPlace > 0 Then
Temp = Mid(MyNumber, DecimalPlace + 1)
If Len(Temp) = 1 Then
Temp = Temp & “0”
End If
DecimalPlace = InStr(1, Temp, “/”)
If DecimalPlace = 0 Then
DecimalPlace = 3
End If
Temp = Mid(Temp, 1, DecimalPlace – 1)
NumberToWords = NumberToWords & ” and ” & Temp & “/100”
End If

End Function

Private Function GetDigit(Digit)
Select Case Val(Digit)
Case 1: GetDigit = “One”
Case 2: GetDigit = “Two”
Case 3: GetDigit = “Three”
Case 4: GetDigit = “Four”
Case 5: GetDigit = “Five”
Case 6: GetDigit = “Six”
Case 7: GetDigit = “Seven”
Case 8: GetDigit = “Eight”
Case 9: GetDigit = “Nine”
Case Else: GetDigit = “”
End Select
End Function

Private Function GetTens(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”
Case 17: Result = “Seventeen”
Case 18: Result = “Eighteen”
Case 19: Result = “Nineteen”
Case Else
End Select
Else
Select Case Val(Left(TensText, 1))
Case 2: Result = “Twenty”
Case 3: Result = “Thirty”
Case 4: Result = “Forty”
Case 5: Result = “Fifty”
Case 6: Result = “Sixty”
Case 7: Result = “Seventy”
Case 8: Result = “Eighty”
Case 9: Result = “Ninety”
Case Else
End Select
Result = Result & ” ” & GetDigit(Right(TensText, 1))
End If
GetTens = Result
End Function