Spell out numbers into words for Cheques

DRSteele

Well-known Member
Joined
Mar 31, 2015
Messages
2,654
Office Version
  1. 365
Platform
  1. Windows
Can someone please help me?

I wish to spell out the numbers in a cell into words that you would use on bank cheques. I have been using these formulas, but there has just got to be a better way, perhaps using the new functions in M365.

MrExcel posts20.xlsx
CDE
332,567.84
4
584.00
6incl "and" Thirty Two Thousand Five Hundred and Sixty Seven
7excl "and" Thirty Two Thousand Five Hundred Sixty Seven
8
9https://www.exceldemy.com/excel-convert-number-to-words/
Sheet31
Cell Formulas
RangeFormula
E5E5=MOD(C3,1)*100
D6D6=CHOOSE(LEFT(TEXT(C3,"000000000.00"))+1,,"One","Two","Three","Four","Five","Six","Seven","Eight","Nine")&IF(--LEFT(TEXT(C3,"000000000.00"))=0,,IF(AND(--MID(TEXT(C3,"000000000.00"),2,1)=0,--MID(TEXT(C3,"000000000.00"),3,1)=0)," Hundred"," Hundred and "))&CHOOSE(MID(TEXT(C3,"000000000.00"),2,1)+1,,,"Twenty ","Thirty ","Forty ","Fifty ","Sixty ","Seventy ","Eighty ","Ninety ")&IF(--MID(TEXT(C3,"000000000.00"),2,1)<>1,CHOOSE(MID(TEXT(C3,"000000000.00"),3,1)+1,,"One","Two","Three","Four","Five","Six","Seven","Eight","Nine"),CHOOSE(MID(TEXT(C3,"000000000.00"),3,1)+1,"Ten","Eleven","Twelve","Thirteen","Fourteen","Fifteen","Sixteen","Seventeen","Eighteen","Nineteen"))&IF((--LEFT(TEXT(C3,"000000000.00"))+MID(TEXT(C3,"000000000.00"),2,1)+MID(TEXT(C3,"000000000.00"),3,1))=0,,IF(AND((--MID(TEXT(C3,"000000000.00"),4,1)+MID(TEXT(C3,"000000000.00"),5,1)+MID(TEXT(C3,"000000000.00"),6,1)+MID(TEXT(C3,"000000000.00"),7,1))=0,(--MID(TEXT(C3,"000000000.00"),8,1)+RIGHT(TEXT(C3,"000000000.00")))>0)," Million and "," Million "))&CHOOSE(MID(TEXT(C3,"000000000.00"),4,1)+1,,"One","Two","Three","Four","Five","Six","Seven","Eight","Nine")&IF(--MID(TEXT(C3,"000000000.00"),4,1)=0,,IF(AND(--MID(TEXT(C3,"000000000.00"),5,1)=0,--MID(TEXT(C3,"000000000.00"),6,1)=0)," Hundred"," Hundred and"))&CHOOSE(MID(TEXT(C3,"000000000.00"),5,1)+1,,," Twenty"," Thirty"," Forty"," Fifty"," Sixty"," Seventy"," Eighty"," Ninety")&IF(--MID(TEXT(C3,"000000000.00"),5,1)<>1,CHOOSE(MID(TEXT(C3,"000000000.00"),6,1)+1,," One"," Two"," Three"," Four"," Five"," Six"," Seven"," Eight"," Nine"),CHOOSE(MID(TEXT(C3,"000000000.00"),6,1)+1," Ten"," Eleven"," Twelve"," Thirteen"," Fourteen"," Fifteen"," Sixteen"," Seventeen"," Eighteen"," Nineteen"))&IF((--MID(TEXT(C3,"000000000.00"),4,1)+MID(TEXT(C3,"000000000.00"),5,1)+MID(TEXT(C3,"000000000.00"),6,1))=0,,IF(OR((--MID(TEXT(C3,"000000000.00"),7,1)+MID(TEXT(C3,"000000000.00"),8,1)+MID(TEXT(C3,"000000000.00"),9,1))=0,--MID(TEXT(C3,"000000000.00"),7,1)<>0)," Thousand "," Thousand and "))&CHOOSE(MID(TEXT(C3,"000000000.00"),7,1)+1,,"One","Two","Three","Four","Five","Six","Seven","Eight","Nine")&IF(--MID(TEXT(C3,"000000000.00"),7,1)=0,,IF(AND(--MID(TEXT(C3,"000000000.00"),8,1)=0,--MID(TEXT(C3,"000000000.00"),9,1)=0)," Hundred "," Hundred and "))&CHOOSE(MID(TEXT(C3,"000000000.00"),8,1)+1,,,"Twenty ","Thirty ","Forty ","Fifty ","Sixty ","Seventy ","Eighty ","Ninety ")&IF(--MID(TEXT(C3,"000000000.00"),8,1)<>1,CHOOSE(MID(TEXT(C3,"000000000.00"),9,1)+1,,"One","Two","Three","Four","Five","Six","Seven","Eight","Nine"),CHOOSE(MID(TEXT(C3,"000000000.00"),9,1)+1,"Ten","Eleven","Twelve","Thirteen","Fourteen","Fifteen","Sixteen","Seventeen","Eighteen","Nineteen"))
D7D7=CHOOSE(LEFT(TEXT(C3,"000000000.00"))+1,,"One","Two","Three","Four","Five","Six","Seven","Eight","Nine")&IF(--LEFT(TEXT(C3,"000000000.00"))=0,,IF(AND(--MID(TEXT(C3,"000000000.00"),2,1)=0,--MID(TEXT(C3,"000000000.00"),3,1)=0)," Hundred"," Hundred "))&CHOOSE(MID(TEXT(C3,"000000000.00"),2,1)+1,,,"Twenty ","Thirty ","Forty ","Fifty ","Sixty ","Seventy ","Eighty ","Ninety ")&IF(--MID(TEXT(C3,"000000000.00"),2,1)<>1,CHOOSE(MID(TEXT(C3,"000000000.00"),3,1)+1,,"One","Two","Three","Four","Five","Six","Seven","Eight","Nine"),CHOOSE(MID(TEXT(C3,"000000000.00"),3,1)+1,"Ten","Eleven","Twelve","Thirteen","Fourteen","Fifteen","Sixteen","Seventeen","Eighteen","Nineteen"))&IF((--LEFT(TEXT(C3,"000000000.00"))+MID(TEXT(C3,"000000000.00"),2,1)+MID(TEXT(C3,"000000000.00"),3,1))=0,,IF(AND((--MID(TEXT(C3,"000000000.00"),4,1)+MID(TEXT(C3,"000000000.00"),5,1)+MID(TEXT(C3,"000000000.00"),6,1)+MID(TEXT(C3,"000000000.00"),7,1))=0,(--MID(TEXT(C3,"000000000.00"),8,1)+RIGHT(TEXT(C3,"000000000.00")))>0)," Million "," Million "))&CHOOSE(MID(TEXT(C3,"000000000.00"),4,1)+1,,"One","Two","Three","Four","Five","Six","Seven","Eight","Nine")&IF(--MID(TEXT(C3,"000000000.00"),4,1)=0,,IF(AND(--MID(TEXT(C3,"000000000.00"),5,1)=0,--MID(TEXT(C3,"000000000.00"),6,1)=0)," Hundred"," Hundred"))&CHOOSE(MID(TEXT(C3,"000000000.00"),5,1)+1,,," Twenty"," Thirty"," Forty"," Fifty"," Sixty"," Seventy"," Eighty"," Ninety")&IF(--MID(TEXT(C3,"000000000.00"),5,1)<>1,CHOOSE(MID(TEXT(C3,"000000000.00"),6,1)+1,," One"," Two"," Three"," Four"," Five"," Six"," Seven"," Eight"," Nine"),CHOOSE(MID(TEXT(C3,"000000000.00"),6,1)+1," Ten"," Eleven"," Twelve"," Thirteen"," Fourteen"," Fifteen"," Sixteen"," Seventeen"," Eighteen"," Nineteen"))&IF((--MID(TEXT(C3,"000000000.00"),4,1)+MID(TEXT(C3,"000000000.00"),5,1)+MID(TEXT(C3,"000000000.00"),6,1))=0,,IF(OR((--MID(TEXT(C3,"000000000.00"),7,1)+MID(TEXT(C3,"000000000.00"),8,1)+MID(TEXT(C3,"000000000.00"),9,1))=0,--MID(TEXT(C3,"000000000.00"),7,1)<>0)," Thousand "," Thousand "))&CHOOSE(MID(TEXT(C3,"000000000.00"),7,1)+1,,"One","Two","Three","Four","Five","Six","Seven","Eight","Nine")&IF(--MID(TEXT(C3,"000000000.00"),7,1)=0,,IF(AND(--MID(TEXT(C3,"000000000.00"),8,1)=0,--MID(TEXT(C3,"000000000.00"),9,1)=0)," Hundred "," Hundred "))&CHOOSE(MID(TEXT(C3,"000000000.00"),8,1)+1,,,"Twenty ","Thirty ","Forty ","Fifty ","Sixty ","Seventy ","Eighty ","Ninety ")&IF(--MID(TEXT(C3,"000000000.00"),8,1)<>1,CHOOSE(MID(TEXT(C3,"000000000.00"),9,1)+1,,"One","Two","Three","Four","Five","Six","Seven","Eight","Nine"),CHOOSE(MID(TEXT(C3,"000000000.00"),9,1)+1,"Ten","Eleven","Twelve","Thirteen","Fourteen","Fifteen","Sixteen","Seventeen","Eighteen","Nineteen"))
 

Excel Facts

Will the fill handle fill 1, 2, 3?
Yes! Type 1 in a cell. Hold down Ctrl while you drag the fill handle.
Can someone please help me?

I wish to spell out the numbers in a cell into words that you would use on bank cheques. I have been using these formulas, but there has just got to be a better way, perhaps using the new functions in M365.

MrExcel posts20.xlsx
CDE
332,567.84
4
584.00
6incl "and" Thirty Two Thousand Five Hundred and Sixty Seven
7excl "and" Thirty Two Thousand Five Hundred Sixty Seven
8
9https://www.exceldemy.com/excel-convert-number-to-words/
Sheet31
Cell Formulas
RangeFormula
E5E5=MOD(C3,1)*100
D6D6=CHOOSE(LEFT(TEXT(C3,"000000000.00"))+1,,"One","Two","Three","Four","Five","Six","Seven","Eight","Nine")&IF(--LEFT(TEXT(C3,"000000000.00"))=0,,IF(AND(--MID(TEXT(C3,"000000000.00"),2,1)=0,--MID(TEXT(C3,"000000000.00"),3,1)=0)," Hundred"," Hundred and "))&CHOOSE(MID(TEXT(C3,"000000000.00"),2,1)+1,,,"Twenty ","Thirty ","Forty ","Fifty ","Sixty ","Seventy ","Eighty ","Ninety ")&IF(--MID(TEXT(C3,"000000000.00"),2,1)<>1,CHOOSE(MID(TEXT(C3,"000000000.00"),3,1)+1,,"One","Two","Three","Four","Five","Six","Seven","Eight","Nine"),CHOOSE(MID(TEXT(C3,"000000000.00"),3,1)+1,"Ten","Eleven","Twelve","Thirteen","Fourteen","Fifteen","Sixteen","Seventeen","Eighteen","Nineteen"))&IF((--LEFT(TEXT(C3,"000000000.00"))+MID(TEXT(C3,"000000000.00"),2,1)+MID(TEXT(C3,"000000000.00"),3,1))=0,,IF(AND((--MID(TEXT(C3,"000000000.00"),4,1)+MID(TEXT(C3,"000000000.00"),5,1)+MID(TEXT(C3,"000000000.00"),6,1)+MID(TEXT(C3,"000000000.00"),7,1))=0,(--MID(TEXT(C3,"000000000.00"),8,1)+RIGHT(TEXT(C3,"000000000.00")))>0)," Million and "," Million "))&CHOOSE(MID(TEXT(C3,"000000000.00"),4,1)+1,,"One","Two","Three","Four","Five","Six","Seven","Eight","Nine")&IF(--MID(TEXT(C3,"000000000.00"),4,1)=0,,IF(AND(--MID(TEXT(C3,"000000000.00"),5,1)=0,--MID(TEXT(C3,"000000000.00"),6,1)=0)," Hundred"," Hundred and"))&CHOOSE(MID(TEXT(C3,"000000000.00"),5,1)+1,,," Twenty"," Thirty"," Forty"," Fifty"," Sixty"," Seventy"," Eighty"," Ninety")&IF(--MID(TEXT(C3,"000000000.00"),5,1)<>1,CHOOSE(MID(TEXT(C3,"000000000.00"),6,1)+1,," One"," Two"," Three"," Four"," Five"," Six"," Seven"," Eight"," Nine"),CHOOSE(MID(TEXT(C3,"000000000.00"),6,1)+1," Ten"," Eleven"," Twelve"," Thirteen"," Fourteen"," Fifteen"," Sixteen"," Seventeen"," Eighteen"," Nineteen"))&IF((--MID(TEXT(C3,"000000000.00"),4,1)+MID(TEXT(C3,"000000000.00"),5,1)+MID(TEXT(C3,"000000000.00"),6,1))=0,,IF(OR((--MID(TEXT(C3,"000000000.00"),7,1)+MID(TEXT(C3,"000000000.00"),8,1)+MID(TEXT(C3,"000000000.00"),9,1))=0,--MID(TEXT(C3,"000000000.00"),7,1)<>0)," Thousand "," Thousand and "))&CHOOSE(MID(TEXT(C3,"000000000.00"),7,1)+1,,"One","Two","Three","Four","Five","Six","Seven","Eight","Nine")&IF(--MID(TEXT(C3,"000000000.00"),7,1)=0,,IF(AND(--MID(TEXT(C3,"000000000.00"),8,1)=0,--MID(TEXT(C3,"000000000.00"),9,1)=0)," Hundred "," Hundred and "))&CHOOSE(MID(TEXT(C3,"000000000.00"),8,1)+1,,,"Twenty ","Thirty ","Forty ","Fifty ","Sixty ","Seventy ","Eighty ","Ninety ")&IF(--MID(TEXT(C3,"000000000.00"),8,1)<>1,CHOOSE(MID(TEXT(C3,"000000000.00"),9,1)+1,,"One","Two","Three","Four","Five","Six","Seven","Eight","Nine"),CHOOSE(MID(TEXT(C3,"000000000.00"),9,1)+1,"Ten","Eleven","Twelve","Thirteen","Fourteen","Fifteen","Sixteen","Seventeen","Eighteen","Nineteen"))
D7D7=CHOOSE(LEFT(TEXT(C3,"000000000.00"))+1,,"One","Two","Three","Four","Five","Six","Seven","Eight","Nine")&IF(--LEFT(TEXT(C3,"000000000.00"))=0,,IF(AND(--MID(TEXT(C3,"000000000.00"),2,1)=0,--MID(TEXT(C3,"000000000.00"),3,1)=0)," Hundred"," Hundred "))&CHOOSE(MID(TEXT(C3,"000000000.00"),2,1)+1,,,"Twenty ","Thirty ","Forty ","Fifty ","Sixty ","Seventy ","Eighty ","Ninety ")&IF(--MID(TEXT(C3,"000000000.00"),2,1)<>1,CHOOSE(MID(TEXT(C3,"000000000.00"),3,1)+1,,"One","Two","Three","Four","Five","Six","Seven","Eight","Nine"),CHOOSE(MID(TEXT(C3,"000000000.00"),3,1)+1,"Ten","Eleven","Twelve","Thirteen","Fourteen","Fifteen","Sixteen","Seventeen","Eighteen","Nineteen"))&IF((--LEFT(TEXT(C3,"000000000.00"))+MID(TEXT(C3,"000000000.00"),2,1)+MID(TEXT(C3,"000000000.00"),3,1))=0,,IF(AND((--MID(TEXT(C3,"000000000.00"),4,1)+MID(TEXT(C3,"000000000.00"),5,1)+MID(TEXT(C3,"000000000.00"),6,1)+MID(TEXT(C3,"000000000.00"),7,1))=0,(--MID(TEXT(C3,"000000000.00"),8,1)+RIGHT(TEXT(C3,"000000000.00")))>0)," Million "," Million "))&CHOOSE(MID(TEXT(C3,"000000000.00"),4,1)+1,,"One","Two","Three","Four","Five","Six","Seven","Eight","Nine")&IF(--MID(TEXT(C3,"000000000.00"),4,1)=0,,IF(AND(--MID(TEXT(C3,"000000000.00"),5,1)=0,--MID(TEXT(C3,"000000000.00"),6,1)=0)," Hundred"," Hundred"))&CHOOSE(MID(TEXT(C3,"000000000.00"),5,1)+1,,," Twenty"," Thirty"," Forty"," Fifty"," Sixty"," Seventy"," Eighty"," Ninety")&IF(--MID(TEXT(C3,"000000000.00"),5,1)<>1,CHOOSE(MID(TEXT(C3,"000000000.00"),6,1)+1,," One"," Two"," Three"," Four"," Five"," Six"," Seven"," Eight"," Nine"),CHOOSE(MID(TEXT(C3,"000000000.00"),6,1)+1," Ten"," Eleven"," Twelve"," Thirteen"," Fourteen"," Fifteen"," Sixteen"," Seventeen"," Eighteen"," Nineteen"))&IF((--MID(TEXT(C3,"000000000.00"),4,1)+MID(TEXT(C3,"000000000.00"),5,1)+MID(TEXT(C3,"000000000.00"),6,1))=0,,IF(OR((--MID(TEXT(C3,"000000000.00"),7,1)+MID(TEXT(C3,"000000000.00"),8,1)+MID(TEXT(C3,"000000000.00"),9,1))=0,--MID(TEXT(C3,"000000000.00"),7,1)<>0)," Thousand "," Thousand "))&CHOOSE(MID(TEXT(C3,"000000000.00"),7,1)+1,,"One","Two","Three","Four","Five","Six","Seven","Eight","Nine")&IF(--MID(TEXT(C3,"000000000.00"),7,1)=0,,IF(AND(--MID(TEXT(C3,"000000000.00"),8,1)=0,--MID(TEXT(C3,"000000000.00"),9,1)=0)," Hundred "," Hundred "))&CHOOSE(MID(TEXT(C3,"000000000.00"),8,1)+1,,,"Twenty ","Thirty ","Forty ","Fifty ","Sixty ","Seventy ","Eighty ","Ninety ")&IF(--MID(TEXT(C3,"000000000.00"),8,1)<>1,CHOOSE(MID(TEXT(C3,"000000000.00"),9,1)+1,,"One","Two","Three","Four","Five","Six","Seven","Eight","Nine"),CHOOSE(MID(TEXT(C3,"000000000.00"),9,1)+1,"Ten","Eleven","Twelve","Thirteen","Fourteen","Fifteen","Sixteen","Seventeen","Eighteen","Nineteen"))

This was taken from How to convert number to words in Excel and amended to enable different currencies to be used.

Call example:

fncSpellNumber(198.87, "Pound", "Pence")

VBA Code:
Public Function fncSpellNumber(ByVal MyNumber, strUnit As String, strHundredths As String)
Dim Units, Hundredths, Temp
Dim DecimalPlace, Count

      ReDim Place(9) As String
      Place(2) = " Thousand "
      Place(3) = " Million "
      Place(4) = " Billion "
      Place(5) = " Trillion "

      MyNumber = Trim(str(MyNumber))
      DecimalPlace = InStr(MyNumber, ".")
      If DecimalPlace > 0 Then
          Hundredths = 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 Units = Temp & Place(Count) & Units
          If Len(MyNumber) > 3 Then
              MyNumber = Left(MyNumber, Len(MyNumber) - 3)
          Else
              MyNumber = ""
          End If
          Count = Count + 1
      Loop
      Select Case Units
          Case ""
              Units = "No " & strUnit & "s"
          Case "One"
              Units = "One " & strUnit
           Case Else
              Units = Units & " " & strUnit & "s"
      End Select
      Select Case Hundredths
          Case ""
              Hundredths = " and No " & strHundredths
          Case "One"
              Hundredths = " and One " & strHundredths
                Case Else
              Hundredths = " and " & Hundredths & " " & strHundredths
      End Select
      fncSpellNumber = Units & Hundredths
  End Function

  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

  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&hellip;
          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&hellip;
          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

  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
 
Upvote 0
Thanks for your reply. Sorry, I should have mentioned that I can't use VBA, only formulas.
 
Upvote 0
You might consider ASAP Utilities (personal use free) which has an option for various currencies?
 
Upvote 0

Forum statistics

Threads
1,223,893
Messages
6,175,246
Members
452,623
Latest member
cliftonhandyman

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top