How to Convert Number to Words in Excel?
Learn how to convert number to words in Excel with our step-by-step guide! In this video, we’ll show you a secret method to transform your numeric data into text effortlessly. This technique is perfect for anyone looking to enhance their Excel skills, whether you’re a beginner or an advanced user. You’ll discover a simple formula that can save you time and make your spreadsheets more readable. Don’t miss out on this game-changing Excel hack! Watch now and master this essential skill.
By the end of this video, you’ll have a comprehensive understanding of how to convert numbers to words in Excel. We also provide tips and tricks to ensure you get the best results every time. Subscribe to our channel for more amazing Excel tutorials and tips. Hit the bell icon to stay updated with our latest videos. Leave a comment if you have any questions or need further assistance. Let’s get started!
|
0 1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 71 72 73 74 75 76 77 78 79 80 81 82 83 84 85 86 87 88 89 90 91 92 93 94 95 96 97 98 99 100 101 102 103 104 105 106 107 108 109 110 111 112 113 |
'Main Function to Convert Number to Words Function NumToWords(ByVal MyNumber) Dim Units As String Dim SubUnits As String Dim TempStr As String Dim DecimalPlace As Integer Dim Count As Integer ReDim Place(9) As String Place(2) = " Thousand " Place(3) = " Million " Place(4) = " Billion " Place(5) = " Trillion " ' Convert MyNumber to string and trim white space MyNumber = Trim(Str(MyNumber)) ' Find position of decimal place 0 if none. DecimalPlace = InStr(MyNumber, ".") ' Convert SubUnits and set MyNumber to Units amount. If DecimalPlace > 0 Then SubUnits = GetTens(Left(Mid(MyNumber, DecimalPlace + 1) & "00", 2)) MyNumber = Trim(Left(MyNumber, DecimalPlace - 1)) End If Count = 1 Do While MyNumber <> "" TempStr = GetHundreds(Right(MyNumber, 3)) If TempStr <> "" Then Units = TempStr & Place(Count) & Units If Len(MyNumber) > 3 Then MyNumber = Left(MyNumber, Len(MyNumber) - 3) Else MyNumber = "" End If Count = Count + 1 Loop NumToWords = Application.Trim(Units & SubUnits) 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)) & " 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 ' 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 = "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 ' 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-by-Step Guide
- Open Excel and Enable Developer Tab:
- Go to
File>Options. - In the
Excel Optionswindow, selectCustomize Ribbon. - Check the
Developeroption on the right pane to enable the Developer tab.
- Go to
- Open the VBA Editor:
- Click on the
Developertab. - Select
Visual Basicto open the VBA editor.
- Click on the
- Insert a New Module:
- In the VBA editor, go to
Insert>Module. This will create a new module where you can write your custom function.
- In the VBA editor, go to
- Copy and Paste the VBA Code:
- Copy the following VBA code and paste it into the module:
|
0 1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 |
Function NumberToWords(ByVal MyNumber) Dim UnitsArray(0 To 19) As String Dim TensArray(0 To 9) As String Dim TempStr As String Dim DecimalPlace As Integer, Count As Integer Dim DecimalPart As String ' Define arrays for words UnitsArray = Array("Zero", "One", "Two", "Three", "Four", "Five", "Six", "Seven", "Eight", "Nine", _ "Ten", "Eleven", "Twelve", "Thirteen", "Fourteen", "Fifteen", "Sixteen", "Seventeen", "Eighteen", "Nineteen") TensArray = Array("", "", "Twenty", "Thirty", "Forty", "Fifty", "Sixty", "Seventy", "Eighty", "Ninety") ' Position of decimal place DecimalPlace = InStr(MyNumber, ".") If DecimalPlace > 0 Then DecimalPart = Mid(MyNumber, DecimalPlace + 1) MyNumber = Left(MyNumber, DecimalPlace - 1) End If Count = 1 Do While MyNumber <> "" Select Case Count Case 1: TempStr = ConvertHundreds(Right(MyNumber, 3)) Case 2: TempStr = ConvertHundreds(Right(MyNumber, 3)) & " Thousand " Case 3: TempStr = ConvertHundreds(Right(MyNumber, 3)) & " Million " Case 4: TempStr = ConvertHundreds(Right(MyNumber, 3)) & " Billion " End Select NumberToWords = TempStr & NumberToWords MyNumber = Left(MyNumber, Len(MyNumber) - 3) Count = Count + 1 Loop NumberToWords = Application.Trim(NumberToWords) If DecimalPart <> "" Then NumberToWords = NumberToWords & " and " & DecimalPart & "/100" End If End Function Private Function ConvertHundreds(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 = UnitsArray(Val(Mid(MyNumber, 1, 1))) & " Hundred " End If ' Convert the tens and units place. If Mid(MyNumber, 2, 1) = "1" Then ' If value between 10-19 Result = Result & UnitsArray(Val(Mid(MyNumber, 2, 2))) Else ' If value between 20-99 Result = Result & TensArray(Val(Mid(MyNumber, 2, 1))) Result = Result & " " & UnitsArray(Val(Mid(MyNumber, 3, 1))) End If ConvertHundreds = Application.Trim(Result) End Function |
5. Close the VBA Editor:
Close the VBA editor by clicking the X in the top-right corner or by going to File > Close and Return to Microsoft Excel.
6. Use the Custom Function in Excel:
Now you can use the NumberToWords function in your Excel worksheet.
For example, if you want to convert the number in cell A1 to words, you can type the following formula in another cell:
|
0 1 2 3 4 5 6 |
=NumberToWords(A1) |
This custom function will convert the number in cell A1 to its corresponding word representation. For example, if A1 contains 123, the function will return “One Hundred Twenty Three”. If A1 contains 123.45, it will return “One Hundred Twenty-Three and 45/100”.
