You
can make your Excel works easier when dealing with conversion from numbers or
amount to words. All you have to do is type the number on one cell and use the
SpellNumber function by creating a new module on your Excel workbook. In this
case you are now error free rather than encoding the amount in words manually.
The code below is created specifically for Philippines Peso conversion though
you can easily edit it according likes and format needs. The format below is
useful in creating check / cheque design for manual printing.

Check format for printing via amount Excel number to words |

## How to Convert Amount or Number in Pesos to Words using Excel

- Open Microsoft Excel 97/2003/2010/2013.
- Press ALT+F11 to open the Visual Basic Editor.
- Click on the Insert, click Module.
- Copy and paste the code below into the module sheet

*Option Explicit*

*'Main Function*

*Function SpellNumber(ByVal MyNumber)*

*Dim Pesos, 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 dollar amount.*

*If DecimalPlace > 0 Then*

*Cents = GetTens2(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 Pesos = Temp & Place(Count) & Pesos*

*If Len(MyNumber) > 3 Then*

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

*Else*

*MyNumber = ""*

*End If*

*Count = Count + 1*

*Loop*

*Select Case Pesos*

*Case ""*

*Pesos = "No Pesos"*

*Case "One"*

*Pesos = "One Peso"*

*Case Else*

*Pesos = Pesos & ""*

*End Select*

*Select Case Cents*

*Case ""*

*Cents = " Pesos Only "*

*Case "One"*

*Cents = " and One Cent"*

*Case Else*

*Cents = " & " & Cents & "/100 Pesos Only"*

*End Select*

*SpellNumber = Pesos & 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 a number from 10 to 99 into text.*

*Function GetTens(TensText)*

*Dim Result As String*

*Result = "" ' Null out the temporary function value.*

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

*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 ' If value between 20-99...*

*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 _*

*(Right(TensText, 1)) ' Retrieve ones place.*

*End If*

*GetTens = Result*

*End Function*

*' Converts a number from 1 to 9 into text.*

*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*

*' Converts a number from 10 to 99 into text.*

*Function GetTens2(TensText)*

*Dim Result As String*

*Result = "" ' Null out the temporary function value.*

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

*Select Case Val(TensText)*

*Case 10: Result = "10"*

*Case 11: Result = "11"*

*Case 12: Result = "12"*

*Case 13: Result = "13"*

*Case 14: Result = "14"*

*Case 15: Result = "15"*

*Case 16: Result = "16"*

*Case 17: Result = "17"*

*Case 18: Result = "18"*

*Case 19: Result = "19"*

*Case Else*

*End Select*

*Else ' If value between 20-99...*

*Select Case Val(Left(TensText, 1))*

*Case 2: Result = "2"*

*Case 3: Result = "3"*

*Case 4: Result = "4"*

*Case 5: Result = "5"*

*Case 6: Result = "6"*

*Case 7: Result = "7"*

*Case 8: Result = "8"*

*Case 9: Result = "9"*

*Case Else*

*End Select*

*Result = Result & GetDigit2 _*

*(Right(TensText, 1)) ' Retrieve ones place.*

*End If*

*GetTens2 = Result*

*End Function*

*' Converts a number from 1 to 9 into text.*

*Function GetDigit2(Digit)*

*Select Case Val(Digit)*

*Case 0: GetDigit2 = "0"*

*Case 1: GetDigit2 = "1"*

*Case 2: GetDigit2 = "2"*

*Case 3: GetDigit2 = "3"*

*Case 4: GetDigit2 = "4"*

*Case 5: GetDigit2 = "5"*

*Case 6: GetDigit2 = "6"*

*Case 7: GetDigit2 = "7"*

*Case 8: GetDigit2 = "8"*

*Case 9: GetDigit2 = "9"*

*Case Else: GetDigit2 = ""*

*End Select*

*End Function*

You
can also edit code if you’re knowledgeable in Visual Basic coding. Original
code is available publicly in Microsoft
website.

Thank you so much! Such a big help :)

ReplyDeleteTnx a lot. Kudos to the author!

ReplyDeleteCode has been set in VBA. How to do the SpellNumber function?

ReplyDelete