training courses in excel - convert number text

Public Schedule Face-to-Face & Online Instructor-Led Training - View dates & book

Forum home » Delegate support and help forum » Microsoft Excel Training and help » training courses in excel - Convert Number to Text

training courses in excel - Convert Number to Text

resolvedResolved · Low Priority · Version Standard

Chrissy has attended:
Excel Intermediate course

Convert Number to Text

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!

RE: 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

Attached files...

VBA coding for spellNumber.xls

RE: Convert Number to Text

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


 

Excel tip:

Changing Excel file and worksheet defaults

The appearance of any new Excel files or any new worksheets that are inserted into a file are controlled by two template files, Book.xlt and Sheet.xlt.

By opening, modifying and saving these templates you can change the default settings for all new files and/or all newly inserted sheets.

Use Book.xlt to make change to defaults for new workbooks; and Sheet.xlt to change defaults for sheets.

If you can't find either of these files on your computer, you can create and save them yourself.

You can do this simply by creating a new workbook with the setting you want to use as your defaults; then saving them with the appropriate name in the XLStart folder. If you are using Office 2003, this is usually found in C:\Program Files\Microsoft Office\Office11\XLStart.

View all Excel hints and tips


Server loaded in 0.09 secs.