This function breaks numbers and converts them into words in Microsoft Excel. We will explain below the steps to create and apply this function as follows:
Open a new Excel file
Press alt + f11
Insert a new module and copy the following code into it:
Function NoToTxt(TheNo As Double, MyCur As String, MySubCur As String) As String
Dim MyArry1(0 To 9) As String
Dim MyArry2(0 To 9) As String
Dim MyArry3(0 To 9) As String
Dim Myno As String
Dim GetNo As String
Dim RdNo As String
Dim My100 As String
Dim My10 As String
Dim My1 As String
Dim My11 As String
Dim My12 As String
Dim My13 As String
Dim My14 As String
Dim My15 As String
Dim My16 As String
Dim My17 As String
Dim My18 As String
Dim My19 As String
Dim GetTxt As String
Dim Mybillion As String
Dim MyMillion As String
Dim MyThou As String
Dim MyHun As String
Dim MyFraction As String
Dim MyAnd As String
Dim I As Integer
Dim ReMark As String
If TheNo > 999999999999.99 Then Exit Function
If TheNo < 0 Then
TheNo = TheNo * -1
ReMark = "remain to you "
Else
ReMark = "only "
End If
If TheNo = 0 Then
NoToTxt = "Zero"
Exit Function
End If
MyAnd = " and "
MyArry1(0) = ""
MyArry1(1) = "one hundred"
MyArry1(2) = "two hundreds"
MyArry1(3) = "three hundreds"
MyArry1(4) = "four hundreds"
MyArry1(5) = "five hundreds"
MyArry1(6) = "six hundreds"
MyArry1(7) = "seven hundreds"
MyArry1(8) = "eight hundreds"
MyArry1(9) = "nine hundreds"
MyArry2(0) = ""
MyArry2(1) = "ten"
MyArry2(2) = "twenty"
MyArry2(3) = "thirty"
MyArry2(4) = "fourty"
MyArry2(5) = "fifty"
MyArry2(6) = "sixty"
MyArry2(7) = "seventy"
MyArry2(8) = "eighty"
MyArry2(9) = "ninety"
MyArry3(0) = ""
MyArry3(1) = "one"
MyArry3(2) = "two"
MyArry3(3) = "three"
MyArry3(4) = "four"
MyArry3(5) = "five"
MyArry3(6) = "six"
MyArry3(7) = "seven"
MyArry3(8) = "eight"
MyArry3(9) = "nine"
GetNo = Format(TheNo, "000000000000.00")
I = 0
Do While I < 15
If I < 12 Then
Myno = Mid$(GetNo, I + 1, 3)
Else
Myno = "0" + Mid$(GetNo, I + 2, 2)
End If
If (Mid$(Myno, 1, 3)) > 0 Then
RdNo = Mid$(Myno, 1, 1)
My100 = MyArry1(RdNo)
RdNo = Mid$(Myno, 3, 1)
My1 = MyArry3(RdNo)
RdNo = Mid$(Myno, 2, 1)
My10 = MyArry2(RdNo)
If Mid$(Myno, 2, 2) = 11 Then My11 = "eleven"
If Mid$(Myno, 2, 2) = 12 Then My12 = "twelve"
If Mid$(Myno, 2, 2) = 13 Then My13 = "thirteen"
If Mid$(Myno, 2, 2) = 14 Then My14 = "fourteen"
If Mid$(Myno, 2, 2) = 15 Then My15 = "fifteen"
If Mid$(Myno, 2, 2) = 16 Then My16 = "sixteen"
If Mid$(Myno, 2, 2) = 17 Then My17 = "seventeen"
If Mid$(Myno, 2, 2) = 18 Then My18 = "eighteen"
If Mid$(Myno, 2, 2) = 19 Then My19 = "nineteen"
If Mid$(Myno, 2, 2) = 10 Then My10 = "ten"
If ((Mid$(Myno, 1, 1)) > 0) And ((Mid$(Myno, 2, 2)) > 0) Then My100 = My100 + MyAnd
GetTxt = My100 + My10 + " " + My1
If ((Mid$(Myno, 3, 1)) = 1) And ((Mid$(Myno, 2, 1)) = 1) Then
GetTxt = My100 + My11
If ((Mid$(Myno, 1, 1)) = 0) Then GetTxt = My11
End If
If ((Mid$(Myno, 3, 1)) = 2) And ((Mid$(Myno, 2, 1)) = 1) Then
GetTxt = My100 + My12
If ((Mid$(Myno, 1, 1)) = 0) Then GetTxt = My12
End If
If ((Mid$(Myno, 3, 1)) = 3) And ((Mid$(Myno, 2, 1)) = 1) Then
GetTxt = My100 + My13
If ((Mid$(Myno, 1, 1)) = 0) Then GetTxt = My13
End If
If ((Mid$(Myno, 3, 1)) = 4) And ((Mid$(Myno, 2, 1)) = 1) Then
GetTxt = My100 + My14
If ((Mid$(Myno, 1, 1)) = 0) Then GetTxt = My14
End If
If ((Mid$(Myno, 3, 1)) = 5) And ((Mid$(Myno, 2, 1)) = 1) Then
GetTxt = My100 + My15
If ((Mid$(Myno, 1, 1)) = 0) Then GetTxt = My15
End If
If ((Mid$(Myno, 3, 1)) = 6) And ((Mid$(Myno, 2, 1)) = 1) Then
GetTxt = My100 + My16
If ((Mid$(Myno, 1, 1)) = 0) Then GetTxt = My16
End If
If ((Mid$(Myno, 3, 1)) = 7) And ((Mid$(Myno, 2, 1)) = 1) Then
GetTxt = My100 + My17
If ((Mid$(Myno, 1, 1)) = 0) Then GetTxt = My17
End If
If ((Mid$(Myno, 3, 1)) = 8) And ((Mid$(Myno, 2, 1)) = 1) Then
GetTxt = My100 + My18
If ((Mid$(Myno, 1, 1)) = 0) Then GetTxt = My18
End If
If ((Mid$(Myno, 3, 1)) = 9) And ((Mid$(Myno, 2, 1)) = 1) Then
GetTxt = My100 + My19
If ((Mid$(Myno, 1, 1)) = 0) Then GetTxt = My19
End If
If (I = 0) And (GetTxt <> "") Then
If ((Mid$(Myno, 1, 3)) > 10) Then
Mybillion = GetTxt + " billion"
Else
Mybillion = GetTxt + " billions"
If ((Mid$(Myno, 1, 3)) = 2) Then Mybillion = " billion"
If ((Mid$(Myno, 1, 3)) = 2) Then Mybillion = " two billions"
End If
End If
If (I = 3) And (GetTxt <> "") Then
If ((Mid$(Myno, 1, 3)) > 10) Then
MyMillion = GetTxt + " million"
Else
MyMillion = GetTxt + " millions"
If ((Mid$(Myno, 1, 3)) = 1) Then MyMillion = " million"
If ((Mid$(Myno, 1, 3)) = 2) Then MyMillion = " two millions"
End If
End If
If (I = 6) And (GetTxt <> "") Then
If ((Mid$(Myno, 1, 3)) > 10) Then
MyThou = GetTxt + " thousand"
Else
MyThou = GetTxt + " thousands"
If ((Mid$(Myno, 3, 1)) = 1) Then MyThou = " thousand"
If ((Mid$(Myno, 3, 1)) = 2) Then MyThou = " two thousands"
End If
End If
If (I = 9) And (GetTxt <> "") Then MyHun = GetTxt
If (I = 12) And (GetTxt <> "") Then MyFraction = GetTxt
End If
I = I + 3
Loop
If (Mybillion <> "") Then
If (MyMillion <> "") Or (MyThou <> "") Or (MyHun <> "") Then Mybillion = Mybillion + MyAnd
End If
If (MyMillion <> "") Then
If (MyThou <> "") Or (MyHun <> "") Then MyMillion = MyMillion + MyAnd
End If
If (MyThou <> "") Then
If (MyHun <> "") Then MyThou = MyThou + MyAnd
End If
If MyFraction <> "" Then
If (Mybillion <> "") Or (MyMillion <> "") Or (MyThou <> "") Or (MyHun <> "") Then
NoToTxt = ReMark + Mybillion + MyMillion + MyThou + MyHun + " " + MyCur + MyAnd + MyFraction + " " + MySubCur
Else
NoToTxt = ReMark + MyFraction + " " + MySubCur
End If
Else
NoToTxt = ReMark + Mybillion + MyMillion + MyThou + MyHun + " " + MyCur
End If
End Function
- Then save
- Then insert an equation into the cell where you want to apply the function and add the cell from which you want to convert numbers into words, in addition to the currency you want to convert to:
Downloading an example to implement the function >> 👇 👇 👇