Spell out numbers into words for Cheques

DRSteele · Nov 2, 2023

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

C

D

E

3

32,567.84

4

5

84.00

6

incl "and"

Thirty Two Thousand Five Hundred and Sixty Seven

7

excl "and"

Thirty Two Thousand Five Hundred Sixty Seven

8

9

https://www.exceldemy.com/excel-convert-number-to-words/

Sheet31

Cell Formulas
Range	Formula
E5	E5	=MOD(C3,1)*100
D6	D6	=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"))
D7	D7	=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"))

Herakles · Nov 2, 2023

DRSteele said:
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
C D E
3 32,567.84
4
5 84.00
6 incl "and" Thirty Two Thousand Five Hundred and Sixty Seven
7 excl "and" Thirty Two Thousand Five Hundred Sixty Seven
8
9 https://www.exceldemy.com/excel-convert-number-to-words/
Sheet31
Cell Formulas
Range Formula
E5 E5 =MOD(C3,1)*100
D6 D6 =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"))
D7 D7 =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

DRSteele · Nov 2, 2023

Thanks for your reply. Sorry, I should have mentioned that I can't use VBA, only formulas.

kweaver · Nov 2, 2023

You might consider ASAP Utilities (personal use free) which has an option for various currencies?

Spell out numbers into words for Cheques

DRSteele

Well-known Member

Excel Facts

Herakles

Well-known Member

DRSteele

Well-known Member

kweaver

Well-known Member

Similar threads

Forum statistics

Share this page

Spell out numbers into words for Cheques

DRSteele

Well-known Member

Excel Facts

Herakles

Well-known Member

DRSteele

Well-known Member

kweaver

Well-known Member

Similar threads

Forum statistics

Share this page

We've detected that you are using an adblocker.

Which adblocker are you using?

Disable AdBlock

Disable AdBlock Plus

Disable uBlock Origin

Disable uBlock