Can anyone tell me if there's a formula to convert numbers to text for invoicing spreadsheets?
Ie. 1463.30 Would read (in another cell) 'One thousand four hundred sixty three pounds and thirty pence'.
Hope someone knows the answer to this!

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’t display Text “Dollar”
Step 12: Press OK
Step 13: You can simply copy and paste the Function to the rest of the cells by using the fill handle (the black cross at the bottom right hand corner of the active cell)

Tip: If you have the numbers as currencies and want the text to be display Pound and pence then in the SpellNumber Function please do the following:

In the N select the cell where the number is
In the UseWord box type in True so the Excel recognises that it is going to be currency
In the ccy box type “Pounds”
In the cents box type “pence”
In the Join box type “ and”

NB: In the join box there is an intentional spacebar used so that there is a space used in the appropriate places.

Hope this helps.

If this posting has helped in answering your query then I would request you to mark the posting as Resolved. If, however, it hasn’t and you need further clarification then please press the reply button and ask for further clarification with your specific question. In case you have a related question then please as the question as a separate posting.

Kindest Regards

Rajeev Rawat
MOS Master Instructor 2000 and 2003

Attached files...

VBA coding for spellNumber.xls

Thanks so much, that worked and will save me lots of time!
Chrissy