Microsoft Excel is one of the most powerful tools with which it will be possible to manage and control large amounts of data of various types (numbers, dates, text, etc.), thanks to its hundreds of functions, formulas and integrated tools which allow you to have the total control over each data entered in the active spreadsheets. One of the tasks that can be recurrent or useful in Excel, both 2016 and 2019, is the conversion of numbers into letters. This is especially useful if we are in an accounting or finance area, as this helps to generate values ​​directly in letters, which is common when checking checks, for example..
Although Microsoft Excel 2016 and the new edition 2019 are full of functions, Excel does not have an integrated function that is responsible for displaying numbers as words in a spreadsheet , which can be a problem or failure for many users. To achieve this conversion, we must create a SpellNumber function code in a VBA module (Visual Basic for Applications) which will automate this action.
TechnoWikis will explain in detail how to do this in Microsoft Excel 2019 but the same process applies to Excel 2016..
Create the SpellNumber function to convert numbers into words in Microsoft Excel 2019, 2016
Step 1
To carry out this process we must access the Visual Basic Editor (VBE) window which is accessed using the following keys.
+ F11 Alt + F11
Step 2
Another alternative method is to use the Programmer menu, if you do not have it (remember that it is disabled by default), we must go to the "File / Options" menu and then go to the "Customize the ribbon" section:
Step 3
There we go to the right side and we will activate the “Programmer†box and click on OK, remember that with this menu it will be possible:
- Run previously recorded macros.
- Create applications for use with Microsoft Office programs.
-
- Use form controls in Microsoft Excel.
- Work with ShapeSheet in Microsoft Visio.
- Create new shapes and symbol galleries in Microsoft Visio.
Step 4
Once we access the Visual Basic Editor window, we go to the “Insert†menu and there we select the “Module†option:
Step 5
In the new window displayed we will paste the following content:
Option Explicit 'Main Function Function SpellNumber (ByVal MyNumber) Dim Dollars, Cents, Temp Dim DecimalPlace, Count ReDim Place (9) As String Place (2) = "Thousand" Place (3) = "Millon" Place (4) = "Billon" Place (5) = "Trillon" 'String representation of amount. MyNumber = Trim (Str (MyNumber)) 'Position of decimal place 0 if none. DecimalPlace = InStr (MyNumber, ".") 'Convert cents and set MyNumber to dollar amount. If DecimalPlace> 0 Then Cents = GetTens (Left (Mid (MyNumber, DecimalPlace + 1) & "00", 2)) MyNumber = Trim (Left (MyNumber, DecimalPlace - 1)) End if Count = 1 Do While MyNumber <> "" Temp = GetHundreds (Right (MyNumber, 3)) If Temp <> "" Then Dollars = Temp & Place (Count) & Dollars If Len (MyNumber)> 3 Then MyNumber = Left (MyNumber, Len (MyNumber) - 3) Else MyNumber = "" End if Count = Count + 1 Loop Select Case Dollars Case "" Dollars = "There are no dollars" Case "One" Dollars = "One Dollar" Case Else Dollars = Dollars & "Dollars" End select Select Case Cents Case "" Cents = "Without cents" Case "One" Cents = "And a penny" Case Else Cents = "and" & Cents & "Cents" End select SpellNumber = Dollars & Cents End function 'Converts a number from 100-999 into text Function GetHundreds (ByVal MyNumber) Dim Result As String If Val (MyNumber) = 0 Then Exit Function MyNumber = Right ("000" & MyNumber, 3) 'Convert the hundreds place. If Mid (MyNumber, 1, 1) <> "0" Then Result = GetDigit (Mid (MyNumber, 1, 1)) & "Mil" End if 'Convert the tens and ones place. If Mid (MyNumber, 2, 1) <> "0" Then Result = Result & GetTens (Mid (MyNumber, 2)) Else Result = Result & GetDigit (Mid (MyNumber, 3)) End if GetHundreds = Result End function 'Converts a number from 10 to 99 into text. Function GetTens (TensText) Dim Result As String Result = "" 'Null out the temporary function value. If Val (Left (TensText, 1)) = 1 Then 'If value between 10-19 ... Select Case Val (TensText) Case 10: Result = "Ten" Case 11: Result = "Once" Case 12: Result = "Twelve" Case 13: Result = "Thirteen" Case 14: Result = "Fourteen" Case 15: Result = "Fifteen" Case 16: Result = "Sixteen" Case 17: Result = "Seventeen" Case 18: Result = "Eighteen" Case 19: Result = "Nineteen" Case Else End select Else 'If value between 20-99 ... Select Case Val (Left (TensText, 1)) Case 2: Result = "Twenty" Case 3: Result = "Thirty" Case 4: Result = "Forty" Case 5: Result = "Fifty" Case 6: Result = "Sixty" Case 7: Result = "Seventy" Case 8: Result = "eighty" Case 9: Result = "Ninety" Case Else End select Result = Result & GetDigit (Right (TensText, 1)) 'Retrieve ones place. End if GetTens = Result End function 'Converts a number from 1 to 9 into text. Function GetDigit (Digit) Select Case Val (Digit) Case 1: GetDigit = "One" Case 2: GetDigit = "Two" Case 3: GetDigit = "Three" Case 4: GetDigit = "Four" Case 5: GetDigit = "Five" Case 6: GetDigit = "Six" Case 7: GetDigit = "Seven" Case 8: GetDigit = "Eight" Case 9: GetDigit = "Nine" Case Else: GetDigit = "" End select End function 
Step 6
Once we have inserted this script in the Excel editor, we proceed to save it for future tasks, for this we go to the “File / Save how†menu and in the window we select the option “Excel workbook enabled for macros†and assign a name the same. Click on Save and we will be ready to use this formula created.
Step 7
Once this is done, we can use two methods to convert numbers into letters in Excel 2019 or 2016, these are:
Conversion Method 1
Enter the number in the desired cell and in another cell use the formula
= SpellNumber (Cell)
Conversion Method 2
Enter the formula with the desired value directly into a cell, for example
= SpellNumber (18.45)
Step 7
The result will be the conversion of our numbers to letters:
Note
Script values ​​can be edited according to our currency or data preferences.
Thus, it will be possible to create this script to convert number data into text in Excel 2016 or 2019.