Saturday, April 28, 2018

Converting Numbers into Words in Excel

Converting Numbers into Words in Excel




Convert Numeric Value to English Words by Excel


Well, actually MS Excel doesnt include any direct function to convert your numeric values into words. But with the help of Microsoft Visual Basic, you can input a module in your worksheet and convert numbers into English words. Lets do this - 



Applies to
  • Excel 2003
  • Excel 2007
  • Excel 2010
  • Excel 2013
  • Excel 2016


Prepare Workbook for Conversion
  1. Run MS Excel (Im using Excel 2016).
  2. Open a new/ blank workbook.
  3. Press Alt+F11 to open Microsoft Visual Basic.
  4. Go to the Insert Menu > Module > Copy and paste the following code in the blank box.
  5. Press Alt+Q to close and return to Microsoft Excel.
  6. Go to the file menu and save this workbook as Excel Macro-Enabled Workbook (*.xlsm). I.e. Word Converter.xlsm. Thats it!


Function words(fig, Optional point = "Point") As String    
Dim digit(14) As Integer    alpha = Array("", "One", "Two", "Three", "Four", "Five", "Six", "Seven", "Eight", "Nine", "Ten", "Eleven", "Twelve", "Thirteen", "Fourteen", "Fifteen", "Sixteen", "Seventeen", "Eighteen", "Nineteen", "Twenty", "Thirty", "Forty", "Fifty", "Sixty", "Seventy", "Eighty", "Ninety")    figi = Trim(StrReverse(Str(Int(Abs(fig)))))    For i = 1 To Len(figi)    digit(i) = Mid(figi, i, 1)    Next    For i = 2 To Len(figi) Step 3    If digit(i) = 1 Then    digit(i) = digit(i - 1) + 10: digit(i - 1) = 0    Else: If digit(i) > 1 Then digit(i) = digit(i) + 18    End If    Next    For i = 1 To Len(figi)    If (i Mod 3) = 0 And digit(i) > 0 Then words = "hundred " & words    If (i Mod 3) = 1 And digit(i) + digit(i + 1) + digit(i + 2) > 0 Then _    words = Choose(i / 3, "thousand ", "million ", "billion ") & words    words = Trim(alpha(digit(i)) & " " & words)    Next    If fig <> Int(fig) Then    figc = StrReverse(figi)    If figc = 0 Then figc = ""    figd = Trim(WorksheetFunction.Substitute(Str(Abs(fig)), figc & ".", ""))    words = Trim(words & " " & point)    For i = 1 To Len(figd)    If Val(Mid(figd, i, 1)) > 0 Then    words = words & " " & alpha(Mid(figd, i, 1))    Else: words = words & " Zero"    End If    Next    End If    If fig < 0 Then words = "Negative " & words    End Function



Run the Function

After going through the above options, your Word Converter.xlsm document is ready to convert numeric values into English words. Now follow the steps below:
  1. Open your Word Converter workbook.
  2. Type a digit in a cell. i.e. type 2124 in cell C6. 
  3. Put your cursor in a different cell i.e. C7.
  4. Type =words(C6) in cell C7. And you will get Two thousand one hundred twenty four.


Convert Number into Words in Excel


Look at the formula bar: =words(A2)


Or you can also use this method - 
  1. Type your digit and put your cursor in a different cell.
  2. Go to the Formula > Insert Function > Select a Category > User Defined.
  3. Now choose Words from Select a function.
  4. Choose your desired cell and hit OK.

Insert Function Dialogue Box



Warning Message!

When you close this document and reopen for the first time, Excel may warn you like - This document contains macro. And you should hit on the Enable Content button. If you dont hit the enable button, then word converter function may not work.



Lots of works to do ha? Seems complicated? No problem. Im sharing a sample workbook for you. Just download and do your work. :) 



Download Sample Workbook



Thanks for visiting us - enjoy your day! 




Stay with Marks PC Solution to get more interesting IT topics!





visit link download
Posted by at