In this article I will demonstrate two rapid and cost-free methods to transform numerical values into English words within Excel 2019, 2016, 2013, and other editions.

Microsoft Excel is an excellent tool for performing various calculations. Initially designed to manage vast datasets, it also facilitates the swift creation of accounting records like invoices, assessments, or balance sheets.

In formal payment documents, it's essential to duplicate numeric values with their corresponding word forms. Typed numbers are harder to tamper with compared to handwritten ones. A fraudster might alter 8000 to appear as 3000, whereas changing "three" into "eight" is nearly undetectable.

Thus, what you require is not merely converting numbers to words in Excel (for instance, 123.45 to "one hundred and twenty-three, forty-five"), but also expressing amounts like $29.95 as "twenty-nine dollars and ninety-nine cents," or pounds and pence for GBP, euros and eurocents for EUR, etc.

Even the most recent versions of Excel lack a built-in feature for spelling out numbers, let alone older versions. Yet, this is when Excel truly excels. You can always enhance its functionality using formulas in all their combinations, VBA macros, or third-party add-ins.

Below you’ll discover two approaches to convert figures into words

Note. If you're searching for number-to-text conversion, meaning you want Excel to treat your number as text, that’s a slightly different matter. For this purpose, you can use the TEXT function or other methods outlined in How to change numbers to text in Excel.

Using the SpellNumber VBA Macro to Convert Numbers to Words

As I previously noted, Microsoft did not incorporate a tool for this task. However, upon realizing how many users required it, they developed and published the specialized VBA macro on their website. This macro performs what its name, SpellNumber, implies. All other macros I encountered are derived from the Microsoft code.

You can locate the macro referred to as the "spellnumber formula." However, it is not a formula but a macro function, more specifically, an Excel User Defined Function (UDF).

The spellnumber option can express dollars and cents. If you require a different currency, you can substitute "dollar" and "cent" with the name of your preferred currency.

If you’re not familiar with VBA, below you will find a copy of the code. If you still prefer not to or don’t have time to work through this, feel free to use this solution.

1. Open the workbook where you wish to spell out the numbers.

2. Press Alt F11 to open the Visual Basic editor window.

3. If multiple books are open, ensure the desired workbook is active by checking the list of projects in the upper left corner of the editor (one of the workbook elements is highlighted with blue).

4. In the editor menu go to Insert-> Module.

5. You should see a window named YourBook - Module1. Select all of the code in the frame below and paste it into this window.

   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) = " Million " Place(4) = " Billion " Place(5) = " Trillion " MyNumber = Trim(Str(MyNumber)) DecimalPlace = InStr(MyNumber, ".") 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 = "No Dollars" Case "One" Dollars = "One Dollar" Case Else Dollars = Dollars & " Dollars" End Select Select Case Cents Case "" Cents = " and No Cents" Case "One" Cents = " and One Cent" Case Else Cents = " and " & Cents & " Cents" End Select SpellNumber = Dollars & Cents End Function 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)) & " Hundred " 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 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 = "Eleven" 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 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

6. Press Ctrl S to save the updated workbook. You will need to resave your workbook. When you attempt to save the workbook with a macro, you'll receive the message "The following features cannot be saved in macro-free workbook"

   

   Click No. When you encounter a new dialog, choose the Save as option. In the field "Save as type" pick the option "Excel macro-enabled workbook".

   

Applying the SpellNumber macro in your worksheets

Now you can utilize the function SpellNumber in your Excel documents. Enter =SpellNumber(A2) into the

The above is the detailed content of How to convert number to words in Excel. For more information, please follow other related articles on the PHP Chinese website!