In this Microsoft Excel video tutorial I explain how to convert numbers into their words equivalent. You can do this using a VBA custom function.

A version of this function is available on Microsoft’s website:

https://support.microsoft.com/en-us/office/convert-numbers-into-words-a0d166fb-e1ea-4090-95c8-69442cd55d98

I found this version of the function, although very good, had some spacing and wording issues; it also only catered for a dollars version of the number in words. In my version of the function I have included an additional argument which allows you to specify which currency you want to output the number in words as. I have included dollars and cents, pounds and pence and euros and cents. You can, however, add you own currency options to the function.

My version of the function also allows you to return a version of the number in words without currency being applied.

## Video Table of Contents

00:00 – Introduction

01:00 – Copy and paste the code into the Visual Basic Editor (use the code below)

02:14 – Setting up custom currency outputs

04:07 – Using the SpellNumber function

Option Explicit 'Main Function Function SpellNumber(ByVal MyNumber, Output) 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 dollar amount. 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 If Output = 0 Then Select Case Dollars Case "" Dollars = "" Case "One" Dollars = "" Case Else Dollars = Dollars End Select Select Case Cents Case "" Cents = "" Case "One" Cents = "Point One" Case Else Cents = " Point " & Cents End Select End If If Output = 1 Then 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 End If If Output = 2 Then Select Case Dollars Case "" Dollars = "No Pounds" Case "One" Dollars = "One Pound" Case Else Dollars = Dollars & " Pounds" End Select Select Case Cents Case "" Cents = " and No Pence" Case "One" Cents = " and One Pence" Case Else Cents = " and " & Cents & " Pence" End Select End If If Output = 3 Then Select Case Dollars Case "" Dollars = "No Euros" Case "One" Dollars = "One Euro" Case Else Dollars = Dollars & " Euros" End Select Select Case Cents Case "" Cents = " and No Cents" Case "One" Cents = " and One Cent" Case Else Cents = " and " & Cents & " Cents" End Select End If 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 MyNumber <= 99.99 Then If Mid(MyNumber, 2, 1) <> "0" Then Result = Result & GetTens(Mid(MyNumber, 2)) Else Result = Result & GetDigit(Mid(MyNumber, 3)) End If Else If Mid(MyNumber, 2, 1) <> "0" Then Result = Result & "and " & GetTens(Mid(MyNumber, 2)) Else Result = Result & "and " & GetDigit(Mid(MyNumber, 3)) End If End If GetHundreds = Trim(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 = Trim(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