If you have a column of numeric values in a worksheet, and now you need to convent them to their equivalent English words (as following screenshot shown). Here I will talk about a method to change the numbers to English words quickly and easily.
Convert Numbers To English Words With User Defined Function
There are no features or formulas can help you to solve this task directly in Excel, but you can create a User Defined Function to finish it. Please do with following steps:
1. Hold down the ALT + F11 keys, and it opens the Microsoft Visual Basic for Applications Window.
2. Click Insert > Module, and paste the following code in the Module Window.
Function NumberstoWords(ByVal pNumber)
'Updateby20140220
Dim Rupees
arr = Array("", "", " Thousand ", " Million ", " Billion ", " Trillion ")
pNumber = Trim(Str(pNumber))
xDecimal = InStr(pNumber, ".")
If xDecimal > 0 Then
pNumber = Trim(Left(pNumber, xDecimal - 1))
End If
xIndex = 1
Do While pNumber <> ""
xHundred = ""
xValue = Right(pNumber, 3)
If Val(xValue) <> 0 Then
xValue = Right("000" & xValue, 3)
If Mid(xValue, 1, 1) <> "0" Then
xHundred = GetDigit(Mid(xValue, 1, 1)) & " Hundred "
End If
If Mid(xValue, 2, 1) <> "0" Then
xHundred = xHundred & GetTens(Mid(xValue, 2))
Else
xHundred = xHundred & GetDigit(Mid(xValue, 3))
End If
End If
If xHundred <> "" Then
Rupees = xHundred & arr(xIndex) & Rupees
End If
If Len(pNumber) > 3 Then
pNumber = Left(pNumber, Len(pNumber) - 3)
Else
pNumber = ""
End If
xIndex = xIndex + 1
Loop
NumberstoWords = Rupees
End Function
Function GetTens(pTens)
Dim Result As String
Result = ""
If Val(Left(pTens, 1)) = 1 Then
Select Case Val(pTens)
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(pTens, 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(pTens, 1))
End If
GetTens = Result
End Function
Function GetDigit(pDigit)
Select Case Val(pDigit)
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
3. Save and close this code and go back the worksheet, in a blank cell, enter this formula =NumberstoWords(A2)( A2 is the cell you want to convert the number to English word), see screenshot:
4. Then press Enter key, and select cell C2 then drag the fill handle to the range that you want to contain this formula. All the numeric values have been converted their corresponding English words.
Note: When you need to copy these results to other cells, please copy and paste them as Values.
Link: https://www.extendoffice.com/documents/excel/1446-excel-convert-change-numbers-to-words.html
