MAKING EXCEL WORK FOR YOU
There is no inbuilt Excel Function to convert numbers to text, however there are many versions of a User Defined Function to convert a numeric value to Text. This is one that I adapted to allow the user to specify a Currency to display.
The syntax for the NumToText function is:
NumToText( value, show currency, "currency")
Copy the UDF code and place it in a Standard Code Module.
Here's how to use it in your codeOption Explicit
MsgBox Application.WorksheetFunction.Proper(NumToText(125, True, "US Dollars"))
To use it in a worksheet simply enter it as any other Excel Formula: In the following example we use the use the Function to write the value 100.50 as text. The show_currency parameter is set to false so no currency is used.
The formula will return one hundred point fifty
In the next example we want to display the currency sign. We set the value to 600.47, show_currency is True & the currency to display is Dollars.
The formula entered to the cell will be
This text will be displayed “six hundred dollars and forty seven cents”
When used with Excel's PROPER Function the effect is much neater
The formula result is now formatted to use Upper Case to start each word:
Download a free workbook containing the code and examples of it’s use.
Display numbers as words in Excel.
© Roy Cox 2005 to 2013
|The Personal WorkBook|
|Excel Sheets in VBA|
|Excel UserInterfaceOnly <New>|
|Excel Macro Recorder|
|Excel Camera Tool|
|Excel Summarise Data|
|Excel CHOOSE Function|