training courses in excel - Convert Number to Text

Dear Chrissy

Thank you for attending PowerPoint Introduction.

This question was very interesting as well as challenging for me.

I did some research and found the solution to it. Unfortunately In Excel there is no built in Function to do this kind of Function.

I am not sure if you have done any advanced course or not but when we cover Macros we introduce a bit of VBA where we learn how to create our own custom functions.

I have uploaded an Excel file to demonstrate how this function works.

Not going into to much technicalities I would request you to do the following I have attached a file containing the custom macro:

Step 1: Open the file that you wish to perform the function.
Step 2: Choose Tools > Macros> Visual Basic Editor
Step 3: Choose Insert > Module
Step 4: Copy and paste the following:

Function SpellNumber(ByVal n As Double, _
Optional ByVal useword As Boolean = True, _
Optional ByVal ccy As String = "Dollars", _
Optional ByVal cents As String = "", _
Optional ByVal join As String = " And", _
Optional ByVal fraction As Boolean = False) As String
Dim myLength As Long
Dim i As Long
Dim myNum As Long
Dim Remainder As Long

SpellNumber = ""
Remainder = Round(100 * (n - Int(n)), 0)

myLength = Int(Application.Log10(n) / 3)

For i = myLength To 0 Step -1
myNum = Int(n / 10 ^ (i * 3))
n = n - myNum * 10 ^ (i * 3)
If myNum > 0 Then
SpellNumber = SpellNumber & MakeWord(Int(myNum)) & _
Choose(i + 1, "", " thousand ", " million ", " billion ", " trillion")
End If
Next i
SpellNumber = SpellNumber & IIf(useword, " " & ccy, "") & _
IIf(Remainder > 0, join & " " & Format(Remainder, "00"), " Only") & _
IIf(fraction, "/100", "") & " " & cents
SpellNumber = Application.Proper(Trim(SpellNumber))

End Function

Function MakeWord(ByVal inValue As Long) As String
Dim unitWord, tenWord
Dim n As Long
Dim unit As Long, ten As Long, hund As Long

unitWord = Array("", "one", "two", "three", "four", _
"five", "six", "seven", "eight", _
"nine", "ten", "eleven", "twelve", _
"thirteen", "fourteen", "fifteen", _
"sixteen", "seventeen", "eighteen", "nineteen")
tenWord = Array("", "ten", "twenty", "thirty", "forty", _
"fifty", "sixty", "seventy", "eighty", "ninety")
MakeWord = ""
n = inValue
If n = 0 Then MakeWord = "zero"
hund = n \ 100
If hund > 0 Then MakeWord = MakeWord & MakeWord(Int(hund)) & " hundred "
n = n - hund * 100
If n < 20 Then
ten = n
MakeWord = MakeWord & unitWord(ten) & " "
Else
ten = n \ 10
MakeWord = MakeWord & tenWord(ten) & " "
unit = n - ten * 10
MakeWord = Trim(MakeWord & unitWord(unit))
End If
MakeWord = Application.Proper(Trim(MakeWord))

End Function

Step 5: Press the Save button and close the Visual Basic Editor window.
Step 6: Insert a new blank column next to the column where the numbers are.
Step 7: Click the fx button on the formula Bar
Step 8: From the Select a category list click on the drop down list and choose user defined
Step 9: Select SpellNumber Function
Step 10: In the N Box select the cell which contains the number for that cell.
Step 11: In the UseWord box type FALSE so that it doesn