Spell Out Numbers in Excel
July 21, 2022 - by Bill Jelen
Problem: I need to spell out numbers in Excel, like on the old check writer machines.
Strategy: Use the SpellNumber VBA function from Microsoft.
This function is not built into Excel. Microsoft publishes a Knowledge Base article with the code to make SpellNumber work. If you have never used VBA before, it can be somewhat intimidating the first time you want to copy a macro from the web into your Excel file.
If you have never used macros before, you need to change your macro security settings to allow macros to run. In Excel, press Alt+T, followed by the letters M and then S. You will be at the macro security settings. Initially, the settings are set to disable all macros without notification. You want to choose the second item, which is Disable all Macros With Notification.
-
1. Open a completely blank workbook.
2. Press Alt+F11 to open the Visual Basic Editor.
3. Once inside VBA, press Ctrl+R to display the Project Explorer. This is a small window that shows all open workbooks and add-ins.
4. In the Project Explorer, find the new file. Click on the file in Project Explorer. From the VBA menu, select Insert, Module.
5. Go to the Internet and search for SpellNumber Excel. The first result should be a Microsoft Knowledge Base article. In 2014, that article was number 213360, but the article is updated every few years, so don’t be surprised if the number is new.
6. Select all of the code in the article, from Option Explicit through End Function.
7. Switch back to Excel VBA and paste the code in an empty module.
8. Press Alt+Q to close VBA and return to Excel.
9. For a quick test, type
=SPELLNUMBER(123)in a cell and press Enter. You should get the words spelled out.
Gotcha: This function will only work in this workbook. You might be thinking that you could save it your personal macro workbook so it will work all the time, but then you will have to start typing the file name before the function. If you really need to use this function all the time in all workbooks on your computer, then the best solution is to make the function into an Add-In.
1. Use File Save As to save an XLSM version of the workbook first.
2. Use File Save As to save an XLAM version of the workbook. When you choose XLAM, Excel will offer to save in the Add-Ins folder.
3. When you save the XLAM version, the workbook becomes hidden, so it seems like it is not open anymore.
4. To make the Add-In a permanent part of your Excel, press Alt+T followed by I to display the Add-Ins dialog. Click Browse... and find your newly saved workbook. Click OK.
From this point forward, any time you have Excel open on your computer, you should be able to use =SPELLNUMBER.
Gotcha: When you send the workbook to others, they will need to have the add-in as well.
This article is an excerpt from Power Excel With MrExcel
Title photo by Volkan Olmez on Unsplash
