How to convert numeric value in words in Excel?

How to Convert Numeric Value in Words in Excel?

One common task in Excel is converting a numeric value into words. This can be useful for creating invoices, financial reports, or any document where you need to represent a number in words. While Excel doesn’t have a built-in function for this, you can achieve this by using a combination of formulas.

To convert a numeric value into words in Excel, follow these steps:

1. First, enter the numeric value you want to convert into a cell (let’s say it’s in cell A1).
2. Next, you’ll need to create a custom function to convert the number into words.
3. Press Alt + F11 to open the Visual Basic for Applications (VBA) editor.
4. Click on Insert > Module to insert a new module.
5. Copy and paste the following VBA code into the module:

“`vba
Function NumToWords(ByVal MyNumber)
Dim Units As String
Dim Tens As String
Dim Temp 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 English words
MyNumber = Trim(Str(MyNumber))
DecimalPlace = InStr(MyNumber, “.”)

If DecimalPlace > 0 Then
Temp = Left(Mid(MyNumber, DecimalPlace + 1) & “00”, 2)
MyNumber = Trim(Left(MyNumber, DecimalPlace – 1))
End If

Count = 1
Do While MyNumber <> “”
Select Case Count
Case 1
Temp = “”
Case 2
If Len(MyNumber) > 3 Then
Temp = Mid(MyNumber, Len(MyNumber) – 2, 2)
If Temp <> “00” Then
Temp = ” ” & Temp & Place(2)
Else
Temp = “”
End If
MyNumber = Left(MyNumber, Len(MyNumber) – 3)
End If
Case 3
If Len(MyNumber) > 2 Then
Temp = Mid(MyNumber, Len(MyNumber) – 1, 2)
If Temp <> “00” Then
Temp = ” ” & Temp & Place(3)
Else
Temp = “”
End If
End If
MyNumber = Left(MyNumber, Len(MyNumber) – 2)
Case 4
If Len(MyNumber) > 1 Then
Temp = Mid(MyNumber, Len(MyNumber), 2)
If Temp <> “00” Then
Temp = ” ” & Temp & Place(4)
Else
Temp = “”
End If
End If
MyNumber = Left(MyNumber, Len(MyNumber) – 1)
Case 5
Temp = MyNumber
MyNumber = “”
End Select

Select Case Len(Temp)
Case 1
Units = GetDigit(Asc(Temp))
Case 2
Tens = GetTens(Asc(Temp))
End Select

Select Case Count
Case 1
NumToWords = Units & Temp & NumToWords
Case 2
NumToWords = Tens & NumToWords
Case 3 To 5
NumToWords = Units & Temp & NumToWords
End Select

Count = Count + 1
Loop

If DecimalPlace > 0 Then
NumToWords = NumToWords & “and Cents”
If Len(Temp) = 1 Then
Units = GetDigit(Asc(Temp))
Else
Tens = GetTens(Asc(Temp))
End If
NumToWords = NumToWords & Tens & Units
End If
End Function

Private Function GetTens(TensText)
Dim Result As String

Result = “”
If Val(Left(TensText, 1)) = 1 Then
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
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(Asc(Right(TensText, 1)))
End If
GetTens = Result
End Function

Private 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. Close the VBA editor and return to your Excel workbook.
7. In the cell where you want to display the number in words, enter the following formula:
   =NumToWords(A1)

Now, the numeric value in cell A1 should be converted into words in the cell where you entered the formula.

FAQs about Converting Numeric Value in Words in Excel

1. Can I use this method to convert any number into words?

Yes, this method can convert any numeric value into words as long as it’s within the limitations of Excel.

2. Is there a limit to the size of the number that can be converted?

Excel has limitations on the size of numbers it can handle, so extremely large numbers may not convert accurately.

3. Can I customize the function to handle different currencies?

Yes, you can customize the function to include currency names or symbols before or after the converted value.

4. How can I ensure the accuracy of the conversion?

While the function is designed to be accurate, it’s always a good idea to double-check the converted value for any errors.

5. Can I format the converted value to match the rest of my document?

Yes, you can format the cell containing the converted value just like any other cell in Excel.

6. Does this method work with decimal numbers?

Yes, the function can handle decimal numbers and will include the decimal part in the conversion.

7. Can I use this function in multiple cells within the same worksheet?

Yes, you can use the NumToWords function in multiple cells to convert different numeric values.

8. How do I handle negative numbers with this function?

The function is not designed to handle negative numbers, so you may need to adjust the code if you want to convert negative values.

9. Can I convert numbers into words in a different language?

While the provided function is in English, you can customize it to work with other languages by translating the words and adjusting the code accordingly.

10. Is there a shorter way to convert numbers into words in Excel?

While there are other methods available, using a custom VBA function is one of the most reliable ways to convert numbers into words in Excel.

11. Can I share this function with others who use Excel?

Yes, you can share the VBA code with others so they can also use it to convert numeric values into words in their Excel workbooks.

12. Are there any limitations to using VBA functions in Excel?

There are some limitations to using VBA functions in Excel, such as compatibility issues with different versions of Excel and potential security concerns. It’s always a good idea to test the function thoroughly before using it in important documents.

Dive into the world of luxury with this video!