Converting Numbers into Words in Excel

Well, actually MS Excel doesn't 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. Let's do this - 

Applies to

• Excel 2003

• Excel 2007

• Excel 2010

• Excel 2013

• Excel 2016

Prepare Workbook for Conversion

1. Run MS Excel (I'm 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. That's 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.

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.

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 don't hit the enable button, then word converter function may not work.

Lots of works to do ha? Seems complicated? No problem. I'm 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!

pub

Subscribe to receive free email updates: