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")

  1. Value: This is the number to convert, it could be a Cell value or entered as a number
  2. show currency: This is a Boolean value, enter True to show the currency, False not to show the currency
  3. Currency: This is a String Variable to be displayed. Enter your choice of currency - "pound","Euro", "US Dollar",etc. Again you could have this recorded in a cell.

Here’s the code


Copy the UDF code and place it in a Standard Code Module.

Here's how to use it in your codeOption Explicit

 Sub test()

      MsgBox Application.WorksheetFunction.Proper(NumToText(125, True, "US Dollars"))

End Sub





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.


                      =numtotext(100.5,FALSE)=PROPER(numtotext(125,TRUE,"pound"))


                    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


=numtotext(600.47,TRUE,"dollar")


This text will be displayed “six hundred dollars and forty seven cents”


When used with Excel's PROPER Function the effect is much neater


=PROPER(numtotext(E50,G50,I50))


The formula result is now formatted to use Upper Case to start each word:

One Hundred And Fifty Nine Dollars And Twenty Seven Cents



Download a free workbook containing the code and examples of it’s use.





Display numbers as words in Excel.

 © Roy Cox 2005 to 2013

Our Privacy & Cookie Policy