Convert numbers into words

Excel doesn’t have a default function that displays numbers as English words in a worksheet, but you can add this capability by pasting the following SpellNumber function code into a VBA (Visual Basic for Applications) module. This operate permits you to convert greenback and cent amounts to words with a formula, so 22.50 would read as Twenty-Two Dollars and Fifty Cents. This can be terribly helpful if you are victimization surpass as a example to print checks.

If you wish to convert numeric values to text format while not displaying them as words, use the TEXT function instead.

Create the SpellNumber operate to convert numbers to words

1.Use the keyboard route, Alt + F11 to open the Visual Basic Editor (VBE).

2.Click the Insert tab, and click Module.

 


3.Copy the following lines of code.


Note: You can download the function by given link.
________________________________________________________________

Option Explicit

'Main Function

Function SpellNumber(ByVal MyNumber)

Dim Dollars, Cents, Temp

Dim DecimalPlace, Count

ReDim Place(9) As String

Place(2) = " Thousand "

Place(3) = " Million "

Place(4) = " Billion "

Place(5) = " Trillion "

' String representation of amount.

MyNumber = Trim(Str(MyNumber))

' Position of decimal place 0 if none.

DecimalPlace = InStr(MyNumber, ".")

' Convert cents and set MyNumber to greenback quantity.

If DecimalPlace > 0 Then

Cents = GetTens(Left(Mid(MyNumber, DecimalPlace + 1) & _ "00", 2))

MyNumber = Trim(Left(MyNumber, DecimalPlace - 1))

End If

Count = 1

Do While MyNumber  ""

Temp = GetHundreds(Right(MyNumber, 3))

If Temp  "" Then Dollars = Temp & Place(Count) & Dollars

If Len(MyNumber) > 3 Then

MyNumber = Left(MyNumber, Len(MyNumber) - 3)

Else

MyNumber = ""

End If

Count = Count + 1

Loop

Select Case Dollars

Case ""

Dollars = "No Dollars"

Case "One"

Dollars = "One Dollar"

Case Else

Dollars = Dollars & " Dollars"

End Select

Select Case Cents

Case ""

Cents = " and No Cents"

Case "One"

Cents = " and One Cent"

Case Else

Cents = " and " & Cents & " Cents"

End Select

SpellNumber = Dollars & Cents

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 variety from ten to ninety nine into text.

Function GetTens(TensText)

Dim Result As String

Result = "" ' Null out the temporary operate worth.

If Val(Left(TensText, one)) = 1 Then ' If worth between 10-19...

Select CaseVal(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 CaseVal(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 variety from one to nine into text.

Function GetDigit(Digit)

Select CaseVal(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

________________________________________________________________

4.Paste the lines of code into the Module1 (Code) box.

5.Press Alt + Q to return to Excel. The SpellNumber function is now ready to use.

Note: This operate works just for this book. To use this operate in another book, you need to repeat the steps to repeat and paste the code in this book.

Use the SpellNumber function in individual cells

1.Type the formula =SpellNumber(A1) into the cell wherever you wish to show a written variety, where A1 is the cell containing the number you want to convert. You can additionally manually sort the worth like =SpellNumber(22.50).

2.Press Enter to confirm the formula.

Save your SpellNumber function workbook

Excel cannot save a book with macro functions within the normal macro-free book format (.xlsx). If you click File > Save. A VB project dialog box opens. Click No.

You can save your file as AN surpass Macro-Enabled book (.xlsm) to keep your file in its current format.

1.Click File > Save As.
2.Click the Save as sort computer menu, and choose Excel Macro-Enabled book.
3.Click Save.

Thanks for visit. Please Like & Subscribe my Blog and Youtube Channel.