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

- Value: This is the number to convert, it could be a Cell value or entered as a number
- show currency: This is a Boolean value, enter True to show the currency, False not to show the currency
- 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.

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.

Excel Data |

Excel Templates |

Excel Camera Tool |

Excel Summarise Data |

Joining Cells |

Nesting Functions |

Excel CHOOSE Function |

Excel VLOOKUP |

Excel Invoice |

Excel CashBook |