Convert numbers to words

kelly mort

kelly mort

Well-known Member
Joined
Apr 10, 2017
Messages
2,169
Office Version
  1. 2016
Platform
  1. Windows
Hello,
I am stucked here again:

Can I ever convert numbers to words with a formula?


Say $12345 I will want to get twelve thousand three hundred and forty five. Thanks
Kelly
 
Sort by date Sort by votes
MARK858 said:
Said I would have missed something... corrected below...

Sheet2

BCDE
1196326893564.01 One Hundred And Ninety-Six Billion Three Hundred And Twenty-Six Million Eight Hundred And Ninety-Three Thousand Five Hundred And Sixty-Four Point One

<colgroup><col style="font-weight:bold; width:30px; "><col style="width:110px;"><col style="width:64px;"><col style="width:64px;"><col style="width:517px;"></colgroup><tbody>
</tbody>

Spreadsheet Formulas
CellFormula
E1=IF(OR(LEN(FLOOR(B1,1))=13,FLOOR(B1,1)<=0),"Out of range",PROPER(SUBSTITUTE(CONCATENATE(CHOOSE(MID(TEXT(INT(B1),REPT(0,12)),1,1)+1,"","one hundred And ","two hundred And ","three hundred And ","four hundred And ","five hundred And ","six hundred And ","seven hundred And ","eight hundred And ","nine hundred And "),CHOOSE(MID(TEXT(INT(B1),REPT(0,12)),2,1)+1,"",CHOOSE(MID(TEXT(INT(B1),REPT(0,12)),3,1)+1,"ten","eleven","twelve","thirteen","fourteen","fifteen","sixteen","seventeen","eighteen","nineteen"),"twenty","thirty","forty","fifty","sixty","seventy","eighty","ninety"),IF(VALUE(MID(TEXT(INT(B1),REPT(0,12)),2,1))>1,CHOOSE(MID(TEXT(INT(B1),REPT(0,12)),3,1)+1,"","-one","-two","-three","-four","-five","-six","-seven","-eight","-nine"),IF(VALUE(MID(TEXT(INT(B1),REPT(0,12)),2,1))=0,CHOOSE(MID(TEXT(INT(B1),REPT(0,12)),3,1)+1,"","one","two","three","four","five","six","seven","eight","nine"),"")),IF(B1>=10^9," billion ",""),CHOOSE(MID(TEXT(INT(B1),REPT(0,12)),4,1)+1,"","one hundred And ","two hundred And ","three hundred And ","four hundred And ","five hundred And ","six hundred And ","seven hundred And ","eight hundred And ","nine hundred And "),CHOOSE(MID(TEXT(INT(B1),REPT(0,12)),5,1)+1,"",CHOOSE(MID(TEXT(INT(B1),REPT(0,12)),6,1)+1,"ten","eleven","twelve","thirteen","fourteen","fifteen","sixteen","seventeen","eighteen","nineteen"),"twenty","thirty","forty","fifty","sixty","seventy","eighty","ninety"),IF(VALUE(MID(TEXT(INT(B1),REPT(0,12)),5,1))>1,CHOOSE(MID(TEXT(INT(B1),REPT(0,12)),6,1)+1,"","-one","-two","-three","-four","-five","-six","-seven","-eight","-nine"),IF(VALUE(MID(TEXT(INT(B1),REPT(0,12)),5,1))=0,CHOOSE(MID(TEXT(INT(B1),REPT(0,12)),6,1)+1,"","one","two","three","four","five","six","seven","eight","nine"),"")),IF(VALUE(MID(TEXT(INT(B1),REPT(0,12)),4,3))>0," million ",""),CHOOSE(MID(TEXT(INT(B1),REPT(0,12)),7,1)+1,"","one hundred And ","two hundred And ","three hundred And ","four hundred And ","five hundred And ","six hundred And ","seven hundred And ","eight hundred And ","nine hundred And "),CHOOSE(MID(TEXT(INT(B1),REPT(0,12)),8,1)+1,"",CHOOSE(MID(TEXT(INT(B1),REPT(0,12)),9,1)+1,"ten","eleven","twelve","thirteen","fourteen","fifteen","sixteen","seventeen","eighteen","nineteen"),"twenty","thirty","forty","fifty","sixty","seventy","eighty","ninety"),IF(VALUE(MID(TEXT(INT(B1),REPT(0,12)),8,1))>1,CHOOSE(MID(TEXT(INT(B1),REPT(0,12)),9,1)+1,"","-one","-two","-three","-four","-five","-six","-seven","-eight","-nine"),IF(VALUE(MID(TEXT(INT(B1),REPT(0,12)),8,1))=0,CHOOSE(MID(TEXT(INT(B1),REPT(0,12)),9,1)+1,"","one","two","three","four","five","six","seven","eight","nine"),"")),IF(VALUE(MID(TEXT(INT(B1),REPT(0,12)),7,3))," thousand ",""),CHOOSE(MID(TEXT(INT(B1),REPT(0,12)),10,1)+1,"","one hundred And ","two hundred And ","three hundred And ","four hundred And ","five hundred And ","six hundred And ","seven hundred And ","eight hundred And ","nine hundred And "),CHOOSE(MID(TEXT(INT(B1),REPT(0,12)),11,1)+1,"",CHOOSE(MID(TEXT(INT(B1),REPT(0,12)),12,1)+1,"ten","eleven","twelve","thirteen","fourteen","fifteen","sixteen","seventeen","eighteen","nineteen"),"twenty","thirty","forty","fifty","sixty","seventy","eighty","ninety"),IF(VALUE(MID(TEXT(INT(B1),REPT(0,12)),11,1))>1,CHOOSE(MID(TEXT(INT(B1),REPT(0,12)),12,1)+1,"","-one","-two","-three","-four","-five","-six","-seven","-eight","-nine"),IF(VALUE(MID(TEXT(INT(B1),REPT(0,12)),11,1))=0,CHOOSE(MID(TEXT(INT(B1),REPT(0,12)),12,1)+1,"","one","two","three","four","five","six","seven","eight","nine"),"")))," "," "))&IF(ISERROR(FIND(".",B1,1)),""," Point "&PROPER(IF(LEN(LEFT(TRIM(MID(SUBSTITUTE(Sheet2!B1,".",REPT(" ",255)),255,200)),2))=1,CHOOSE(1*LEFT(TRIM(MID(SUBSTITUTE(Sheet2!B1,".",REPT(" ",255)),255,200)),2),"ten","twenty","thirty","forty","fifty","sixty","seventy","eighty","ninety"),"")&CONCATENATE(CHOOSE(MID(TEXT(INT(LEFT(TRIM(MID(SUBSTITUTE(Sheet2!B1,".",REPT(" ",255)),255,200)),2)),REPT(0,12)),11,1)+1,"",CHOOSE(MID(TEXT(INT(LEFT(TRIM(MID(SUBSTITUTE(Sheet2!B1,".",REPT(" ",255)),255,200)),2)),REPT(0,12)),12,1)+1,"ten","eleven","twelve","thirteen","fourteen","fifteen","sixteen","seventeen","eighteen","nineteen"),"twenty","thirty","forty","fifty","sixty","seventy","eighty","ninety"),IF(VALUE(MID(TEXT(INT(LEFT(TRIM(MID(SUBSTITUTE(Sheet2!B1,".",REPT(" ",255)),255,200)),2)),REPT(0,12)),11,1))>1,CHOOSE(MID(TEXT(INT(LEFT(TRIM(MID(SUBSTITUTE(Sheet2!B1,".",REPT(" ",255)),255,200)),2)),REPT(0,12)),12,1)+1,"","-one","-two","-three","-four","-five","-six","-seven","-eight","-nine"),IF(LEFT(TRIM(MID(SUBSTITUTE(Sheet2!B1,".",REPT(" ",255)),255,200)),2)="01","one",IF(LEFT(TRIM(MID(SUBSTITUTE(Sheet2!B1,".",REPT(" ",255)),255,200)),1)="0",CHOOSE(MID(TEXT(INT(LEFT(TRIM(MID(SUBSTITUTE(Sheet2!B1,".",REPT(" ",255)),255,200)),2)),REPT(0,12)),12,1)+1,"","one","two","three","four","five","six","seven","eight","nine"),"")))))))

<tbody>
</tbody>

<tbody>
</tbody>


Excel tables to the web >> Excel Jeanie HTML 4
Click to expand...

Okay thanks a lot but that is saying point one instead of point zero one. I have not tested it yet though I am referring from your post.

Thanks again
Kelly
 
Upvote 0

Excel Facts

How to find 2nd largest value in a column?
MAX finds the largest value. =LARGE(A:A,2) will find the second largest. =SMALL(A:A,3) will find the third smallest
kelly mort said:
Okay thanks a lot but that is saying point one instead of point zero one. I have not tested it yet though I am referring from your post.

Thanks again
Kelly
Click to expand...

I am sure you could have worked that one out for yourself if you read the formula, as I have stated I am not making any more amendments to the formula now.

Excel Workbook
BCDE
1196326893564.01One Hundred And Ninety-Six Billion Three Hundred And Twenty-Six Million Eight Hundred And Ninety-Three Thousand Five Hundred And Sixty-Four Point Zero One
Sheet2
 
Last edited:
Upvote 0
kelly mort said:
I mean I wanna get say Cedis display when I used the "Dollar" with the function. I tried to modify it but no success.
Click to expand...
Did you mean to type "Credis" (as you did) or "Credit" (which seems more likely to me)? If "Credit, should that always be singular (never a trailing "s")?

Also, is whichever word that was supposed to be, did you want it to replace the word "Dollars" or concatenate onto the back of it like this "Dollars Credit" or did you want to leave the text my function returns as is and add the word "Credit" at the very end of the text? In other words, how should this display with the "Credis" or "Credit" option (please post exactly what you want it to look like; that is, with or without the word "dollars" as well as with the word "Credis" or "Credit" as appropriate)...

1234.56
 
Upvote 0
Rick Rothstein said:
Did you mean to type "Credis" (as you did) or "Credit" (which seems more likely to me)? If "Credit, should that always be singular (never a trailing "s")?

Also, is whichever word that was supposed to be, did you want it to replace the word "Dollars" or concatenate onto the back of it like this "Dollars Credit" or did you want to leave the text my function returns as is and add the word "Credit" at the very end of the text? In other words, how should this display with the "Credis" or "Credit" option (please post exactly what you want it to look like; that is, with or without the word "dollars" as well as with the word "Credis" or "Credit" as appropriate)...

1234.56
Click to expand...

I want it to replace the dollars. Even with this one I think I have found a way out. I used the "And" Then concatenate it to the end.

The only issue now is that when I use the "And" with the function, for numbers in millions, one "and" does not appear.

Regards
Kelly
 
Upvote 0
kelly mort said:
I want it to replace the dollars. Even with this one I think I have found a way out. I used the "And" Then concatenate it to the end.
Click to expand...
Okay, well I still don't know whether the word is "Credis" or "Credit" or even "Credits" (that was why I asked you to show me what you wanted for 1234.56, but you opted not to do so). So, you left me guessing.

Below is my function revised to allow you to specify a keyword for forcing it to add "and" between hundreds and what follows (like you asked for) for each grouping (thousands, millions, billions, etc.). That keyword is "FullAnd"... you can couple it with any of the other keywords. So, if you wanted "and" everywhere along with the dollar display, you would specify the optional argument as "Dollars,FullAnd"; in other words...

=NumberAsText(SomeValue, "Dollars,FullAnd")

You would do a similar coupling with any of the other optional arguments. Until you tell me differently regarding the word "Credis", "Credit" or "Credits", I will simply suggest that you wrap the above formula with a SUBSTITUTE function call to replace "Dollar" with "Credis" or "Credit" (the formula will automatically add an "s" to the end if the number is more than one. For example, assuming your word "Credis"...

=SUBSTITUTE(NumberAsText(SomeValue, "Dollars,FullAnd"),"Dollar","Credis")

Here is the function...
Code: 
Private sNumberText() As String

Public Function NumberAsText(NumberIn As Variant, Optional _
                AND_or_CHECK_or_DOLLAR_or_CHECKDOLLAR As String) As String
   Dim cnt As Long
   Dim DecimalPoint As Long
   Dim CardinalNumber As Long
   Dim CommaAdjuster As Long
   Dim TestValue As Long
   Dim CurrValue As Currency
   Dim CentsString As String
   Dim NumberSign As String
   Dim WholePart As String
   Dim BigWholePart As String
   Dim DecimalPart As String
   Dim tmp As String
   Dim sStyle As String
   Dim bUseAnd As Boolean
   Dim bUseCheck As Boolean
   Dim bUseDollars As Boolean
   Dim bUseCheckDollar As Boolean
  '----------------------------------------
  '  Begin setting conditions for formatting
  '----------------------------------------
   sStyle = LCase(AND_or_CHECK_or_DOLLAR_or_CHECKDOLLAR)
   bUseFullAnd = sStyle Like "*fulland*"
   bUseAnd = sStyle = "and"
   bUseCheckDollar = (sStyle Like "*checkdollar*")
   If Not bUseCheckDollar Then
     bUseDollars = sStyle Like "*dollar*"
     bUseCheck = (sStyle Like "*check*") ' Or (sStyle = "dollar")
   End If
  '----------------------------------------
  '  Check/create array. If this is the first
  '  time using this routine, create the text
  '  strings that will be used.
  '----------------------------------------
   If Not IsBounded(sNumberText) Then
      Call BuildArray(sNumberText)
   End If
  '----------------------------------------
  '  Begin validating the number, and breaking
  '  into constituent parts
  '----------------------------------------
   NumberIn = Trim$(NumberIn)
   If Not IsNumeric(NumberIn) Then
      NumberAsText = "Error - Number improperly formed"
      Exit Function
   Else
      DecimalPoint = InStr(NumberIn, ".")
      If DecimalPoint > 0 Then
         DecimalPart = Mid$(NumberIn, DecimalPoint + 1)
         WholePart = Left$(NumberIn, DecimalPoint - 1)
      Else
         DecimalPoint = Len(NumberIn) + 1
         WholePart = NumberIn
      End If
      If InStr(NumberIn, ",,") Or _
         InStr(NumberIn, ",.") Or _
         InStr(NumberIn, ".,") Or _
         InStr(DecimalPart, ",") Then
         NumberAsText = "Error - Improper use of commas"
         Exit Function
      ElseIf InStr(NumberIn, ",") Then
         CommaAdjuster = 0
         WholePart = ""
         For cnt = DecimalPoint - 1 To 1 Step -1
            If Not Mid$(NumberIn, cnt, 1) Like "[,]" Then
               WholePart = Mid$(NumberIn, cnt, 1) & WholePart
            Else
               CommaAdjuster = CommaAdjuster + 1
               If (DecimalPoint - cnt - CommaAdjuster) Mod 3 Then
                  NumberAsText = "Error - Improper use of commas"
                  Exit Function
               End If
            End If
         Next
      End If
   End If
   If Left$(WholePart, 1) Like "[+-]" Then
      NumberSign = IIf(Left$(WholePart, 1) = "-", "Minus ", "Plus ")
      WholePart = Mid$(WholePart, 2)
   End If
  '----------------------------------------
  '  Begin code to assure decimal portion of
  '  check value is not inadvertently rounded
  '----------------------------------------
   If bUseCheck = True Then
      CurrValue = CCur(Val("." & DecimalPart))
      DecimalPart = Mid$(Format$(CurrValue, "0.00"), 3, 2)
      If CurrValue >= 0.995 Then
         If WholePart = String$(Len(WholePart), "9") Then
            WholePart = "1" & String$(Len(WholePart), "0")
         Else
            For cnt = Len(WholePart) To 1 Step -1
              If Mid$(WholePart, cnt, 1) = "9" Then
                 Mid$(WholePart, cnt, 1) = "0"
              Else
                 Mid$(WholePart, cnt, 1) = _
                            CStr(Val(Mid$(WholePart, cnt, 1)) + 1)
                 Exit For
              End If
            Next
         End If
      End If
   End If
  '----------------------------------------
  '  Final prep step - this assures number
  '  within range of formatting code below
  '----------------------------------------
   If Len(WholePart) > 9 Then
      BigWholePart = Left$(WholePart, Len(WholePart) - 9)
      WholePart = Right$(WholePart, 9)
   End If
   If Len(BigWholePart) > 9 Then
      NumberAsText = "Error - Number too large"
      Exit Function
   ElseIf Not WholePart Like String$(Len(WholePart), "#") Or _
         (Not BigWholePart Like String$(Len(BigWholePart), "#") _
          And Len(BigWholePart) > 0) Then
      NumberAsText = "Error - Number improperly formed"
      Exit Function
   End If
  '----------------------------------------
  '  Begin creating the output string
  '----------------------------------------
  '  Very Large values
   TestValue = Val(BigWholePart)
   If TestValue > 999999 Then
      CardinalNumber = TestValue \ 1000000
      tmp = HundredsTensUnits(CardinalNumber, , bUseFullAnd) & "Quadrillion "
      TestValue = TestValue - (CardinalNumber * 1000000)
   End If
   If TestValue > 999 Then
     CardinalNumber = TestValue \ 1000
     tmp = tmp & HundredsTensUnits(CardinalNumber, , bUseFullAnd) & "Trillion "
     TestValue = TestValue - (CardinalNumber * 1000)
   End If
   If TestValue > 0 Then
      tmp = tmp & HundredsTensUnits(TestValue, , bUseFullAnd) & "Billion "
   End If
  '  Lesser values
   TestValue = Val(WholePart)
   If TestValue = 0 And BigWholePart = "" Then tmp = "Zero "
   If TestValue > 999999 Then
      CardinalNumber = TestValue \ 1000000
      tmp = tmp & HundredsTensUnits(CardinalNumber, , bUseFullAnd) & "Million "
      TestValue = TestValue - (CardinalNumber * 1000000)
   End If
   If TestValue > 999 Then
      CardinalNumber = TestValue \ 1000
      tmp = tmp & HundredsTensUnits(CardinalNumber, , bUseFullAnd) & "Thousand "
      TestValue = TestValue - (CardinalNumber * 1000)
   End If
   If TestValue > 0 Then
      If Val(WholePart) < 99 And BigWholePart = "" Then bUseAnd = False
      tmp = tmp & HundredsTensUnits(TestValue, bUseAnd, bUseFullAnd)
   End If
  '  If in dollar mode, assure the text is the correct plurality
   If bUseDollars = True And Not bUseCheckDollar Then
      CentsString = HundredsTensUnits(DecimalPart)
      If tmp = "One " Then
         tmp = tmp & "Dollar"
      Else
         tmp = tmp & "Dollars"
      End If
      If Len(CentsString) > 0 Then
         tmp = tmp & " and " & CentsString
         If CentsString = "One " Then
            tmp = tmp & "Cent"
         Else
            tmp = tmp & "Cents"
         End If
      End If
   ElseIf bUseCheck = True Then
      tmp = tmp & "and " & Left$(DecimalPart & "00", 2)
      tmp = tmp & "/100"
   ElseIf bUseCheckDollar = True Then
      If tmp = "One " Then
         tmp = tmp & "Dollar"
      Else
         tmp = tmp & "Dollars"
      End If
      tmp = tmp & " and " & Left$(DecimalPart & "00", 2)
      tmp = tmp & "/100"
   Else
      If Len(DecimalPart) > 0 Then
        tmp = tmp & "Point"
        For cnt = 1 To Len(DecimalPart)
          tmp = tmp & " " & sNumberText(Mid$(DecimalPart, cnt, 1))
        Next
      End If
   End If
  '  Done!
   NumberAsText = NumberSign & tmp
End Function

Private Sub BuildArray(sNumberText() As String)
   ReDim sNumberText(0 To 27) As String
   sNumberText(0) = "Zero"
   sNumberText(1) = "One"
   sNumberText(2) = "Two"
   sNumberText(3) = "Three"
   sNumberText(4) = "Four"
   sNumberText(5) = "Five"
   sNumberText(6) = "Six"
   sNumberText(7) = "Seven"
   sNumberText(8) = "Eight"
   sNumberText(9) = "Nine"
   sNumberText(10) = "Ten"
   sNumberText(11) = "Eleven"
   sNumberText(12) = "Twelve"
   sNumberText(13) = "Thirteen"
   sNumberText(14) = "Fourteen"
   sNumberText(15) = "Fifteen"
   sNumberText(16) = "Sixteen"
   sNumberText(17) = "Seventeen"
   sNumberText(18) = "Eighteen"
   sNumberText(19) = "Nineteen"
   sNumberText(20) = "Twenty"
   sNumberText(21) = "Thirty"
   sNumberText(22) = "Forty"
   sNumberText(23) = "Fifty"
   sNumberText(24) = "Sixty"
   sNumberText(25) = "Seventy"
   sNumberText(26) = "Eighty"
   sNumberText(27) = "Ninety"
End Sub

Private Function IsBounded(vntArray As Variant) As Boolean
   On Error Resume Next
   IsBounded = IsNumeric(UBound(vntArray))
End Function

Private Function HundredsTensUnits(ByVal TestValue As Integer, _
                              Optional ByVal bUseAnd As Boolean, _
                              Optional ByVal bUseFullAnd As Boolean) As String
   Dim CardinalNumber As Integer
   If TestValue > 99 Then
      CardinalNumber = TestValue \ 100
      HundredsTensUnits = sNumberText(CardinalNumber) & " Hundred "
      TestValue = TestValue - (CardinalNumber * 100)
   End If
   If bUseAnd Or bUseFullAnd Then
      If TestValue Then HundredsTensUnits = HundredsTensUnits & "and "
   End If
   If TestValue > 20 Then
      CardinalNumber = TestValue \ 10
      HundredsTensUnits = HundredsTensUnits & _
                          sNumberText(CardinalNumber + 18) & " "
      TestValue = TestValue - (CardinalNumber * 10)
   End If
   If TestValue > 0 Then
      HundredsTensUnits = HundredsTensUnits & _
                          sNumberText(TestValue) & " "
   End If
End Function
 
Last edited:
Upvote 0
Rick Rothstein said:
Okay, well I still don't know whether the word is "Credis" or "Credit" or even "Credits" (that was why I asked you to show me what you wanted for 1234.56, but you opted not to do so). So, you left me guessing.

Below is my function revised to allow you to specify a keyword for forcing it to add "and" between hundreds and what follows (like you asked for) for each grouping (thousands, millions, billions, etc.). That keyword is "FullAnd"... you can couple it with any of the other keywords. So, if you wanted "and" everywhere along with the dollar display, you would specify the optional argument as "Dollars,FullAnd"; in other words...

=NumberAsText(SomeValue, "Dollars,FullAnd")

You would do a similar coupling with any of the other optional arguments. Until you tell me differently regarding the word "Credis", "Credit" or "Credits", I will simply suggest that you wrap the above formula with a SUBSTITUTE function call to replace "Dollar" with "Credis" or "Credit" (the formula will automatically add an "s" to the end if the number is more than one. For example, assuming your word "Credis"...

=SUBSTITUTE(NumberAsText(SomeValue, "Dollars,FullAnd"),"Dollar","Credis")

Here is the function...
Code: 
Private sNumberText() As String

Public Function NumberAsText(NumberIn As Variant, Optional _
                AND_or_CHECK_or_DOLLAR_or_CHECKDOLLAR As String) As String
   Dim cnt As Long
   Dim DecimalPoint As Long
   Dim CardinalNumber As Long
   Dim CommaAdjuster As Long
   Dim TestValue As Long
   Dim CurrValue As Currency
   Dim CentsString As String
   Dim NumberSign As String
   Dim WholePart As String
   Dim BigWholePart As String
   Dim DecimalPart As String
   Dim tmp As String
   Dim sStyle As String
   Dim bUseAnd As Boolean
   Dim bUseCheck As Boolean
   Dim bUseDollars As Boolean
   Dim bUseCheckDollar As Boolean
  '----------------------------------------
  '  Begin setting conditions for formatting
  '----------------------------------------
   sStyle = LCase(AND_or_CHECK_or_DOLLAR_or_CHECKDOLLAR)
   bUseFullAnd = sStyle Like "*fulland*"
   bUseAnd = sStyle = "and"
   bUseCheckDollar = (sStyle Like "*checkdollar*")
   If Not bUseCheckDollar Then
     bUseDollars = sStyle Like "*dollar*"
     bUseCheck = (sStyle Like "*check*") ' Or (sStyle = "dollar")
   End If
  '----------------------------------------
  '  Check/create array. If this is the first
  '  time using this routine, create the text
  '  strings that will be used.
  '----------------------------------------
   If Not IsBounded(sNumberText) Then
      Call BuildArray(sNumberText)
   End If
  '----------------------------------------
  '  Begin validating the number, and breaking
  '  into constituent parts
  '----------------------------------------
   NumberIn = Trim$(NumberIn)
   If Not IsNumeric(NumberIn) Then
      NumberAsText = "Error - Number improperly formed"
      Exit Function
   Else
      DecimalPoint = InStr(NumberIn, ".")
      If DecimalPoint > 0 Then
         DecimalPart = Mid$(NumberIn, DecimalPoint + 1)
         WholePart = Left$(NumberIn, DecimalPoint - 1)
      Else
         DecimalPoint = Len(NumberIn) + 1
         WholePart = NumberIn
      End If
      If InStr(NumberIn, ",,") Or _
         InStr(NumberIn, ",.") Or _
         InStr(NumberIn, ".,") Or _
         InStr(DecimalPart, ",") Then
         NumberAsText = "Error - Improper use of commas"
         Exit Function
      ElseIf InStr(NumberIn, ",") Then
         CommaAdjuster = 0
         WholePart = ""
         For cnt = DecimalPoint - 1 To 1 Step -1
            If Not Mid$(NumberIn, cnt, 1) Like "[,]" Then
               WholePart = Mid$(NumberIn, cnt, 1) & WholePart
            Else
               CommaAdjuster = CommaAdjuster + 1
               If (DecimalPoint - cnt - CommaAdjuster) Mod 3 Then
                  NumberAsText = "Error - Improper use of commas"
                  Exit Function
               End If
            End If
         Next
      End If
   End If
   If Left$(WholePart, 1) Like "[+-]" Then
      NumberSign = IIf(Left$(WholePart, 1) = "-", "Minus ", "Plus ")
      WholePart = Mid$(WholePart, 2)
   End If
  '----------------------------------------
  '  Begin code to assure decimal portion of
  '  check value is not inadvertently rounded
  '----------------------------------------
   If bUseCheck = True Then
      CurrValue = CCur(Val("." & DecimalPart))
      DecimalPart = Mid$(Format$(CurrValue, "0.00"), 3, 2)
      If CurrValue >= 0.995 Then
         If WholePart = String$(Len(WholePart), "9") Then
            WholePart = "1" & String$(Len(WholePart), "0")
         Else
            For cnt = Len(WholePart) To 1 Step -1
              If Mid$(WholePart, cnt, 1) = "9" Then
                 Mid$(WholePart, cnt, 1) = "0"
              Else
                 Mid$(WholePart, cnt, 1) = _
                            CStr(Val(Mid$(WholePart, cnt, 1)) + 1)
                 Exit For
              End If
            Next
         End If
      End If
   End If
  '----------------------------------------
  '  Final prep step - this assures number
  '  within range of formatting code below
  '----------------------------------------
   If Len(WholePart) > 9 Then
      BigWholePart = Left$(WholePart, Len(WholePart) - 9)
      WholePart = Right$(WholePart, 9)
   End If
   If Len(BigWholePart) > 9 Then
      NumberAsText = "Error - Number too large"
      Exit Function
   ElseIf Not WholePart Like String$(Len(WholePart), "#") Or _
         (Not BigWholePart Like String$(Len(BigWholePart), "#") _
          And Len(BigWholePart) > 0) Then
      NumberAsText = "Error - Number improperly formed"
      Exit Function
   End If
  '----------------------------------------
  '  Begin creating the output string
  '----------------------------------------
  '  Very Large values
   TestValue = Val(BigWholePart)
   If TestValue > 999999 Then
      CardinalNumber = TestValue \ 1000000
      tmp = HundredsTensUnits(CardinalNumber, , bUseFullAnd) & "Quadrillion "
      TestValue = TestValue - (CardinalNumber * 1000000)
   End If
   If TestValue > 999 Then
     CardinalNumber = TestValue \ 1000
     tmp = tmp & HundredsTensUnits(CardinalNumber, , bUseFullAnd) & "Trillion "
     TestValue = TestValue - (CardinalNumber * 1000)
   End If
   If TestValue > 0 Then
      tmp = tmp & HundredsTensUnits(TestValue, , bUseFullAnd) & "Billion "
   End If
  '  Lesser values
   TestValue = Val(WholePart)
   If TestValue = 0 And BigWholePart = "" Then tmp = "Zero "
   If TestValue > 999999 Then
      CardinalNumber = TestValue \ 1000000
      tmp = tmp & HundredsTensUnits(CardinalNumber, , bUseFullAnd) & "Million "
      TestValue = TestValue - (CardinalNumber * 1000000)
   End If
   If TestValue > 999 Then
      CardinalNumber = TestValue \ 1000
      tmp = tmp & HundredsTensUnits(CardinalNumber, , bUseFullAnd) & "Thousand "
      TestValue = TestValue - (CardinalNumber * 1000)
   End If
   If TestValue > 0 Then
      If Val(WholePart) < 99 And BigWholePart = "" Then bUseAnd = False
      tmp = tmp & HundredsTensUnits(TestValue, bUseAnd, bUseFullAnd)
   End If
  '  If in dollar mode, assure the text is the correct plurality
   If bUseDollars = True And Not bUseCheckDollar Then
      CentsString = HundredsTensUnits(DecimalPart)
      If tmp = "One " Then
         tmp = tmp & "Dollar"
      Else
         tmp = tmp & "Dollars"
      End If
      If Len(CentsString) > 0 Then
         tmp = tmp & " and " & CentsString
         If CentsString = "One " Then
            tmp = tmp & "Cent"
         Else
            tmp = tmp & "Cents"
         End If
      End If
   ElseIf bUseCheck = True Then
      tmp = tmp & "and " & Left$(DecimalPart & "00", 2)
      tmp = tmp & "/100"
   ElseIf bUseCheckDollar = True Then
      If tmp = "One " Then
         tmp = tmp & "Dollar"
      Else
         tmp = tmp & "Dollars"
      End If
      tmp = tmp & " and " & Left$(DecimalPart & "00", 2)
      tmp = tmp & "/100"
   Else
      If Len(DecimalPart) > 0 Then
        tmp = tmp & "Point"
        For cnt = 1 To Len(DecimalPart)
          tmp = tmp & " " & sNumberText(Mid$(DecimalPart, cnt, 1))
        Next
      End If
   End If
  '  Done!
   NumberAsText = NumberSign & tmp
End Function

Private Sub BuildArray(sNumberText() As String)
   ReDim sNumberText(0 To 27) As String
   sNumberText(0) = "Zero"
   sNumberText(1) = "One"
   sNumberText(2) = "Two"
   sNumberText(3) = "Three"
   sNumberText(4) = "Four"
   sNumberText(5) = "Five"
   sNumberText(6) = "Six"
   sNumberText(7) = "Seven"
   sNumberText(8) = "Eight"
   sNumberText(9) = "Nine"
   sNumberText(10) = "Ten"
   sNumberText(11) = "Eleven"
   sNumberText(12) = "Twelve"
   sNumberText(13) = "Thirteen"
   sNumberText(14) = "Fourteen"
   sNumberText(15) = "Fifteen"
   sNumberText(16) = "Sixteen"
   sNumberText(17) = "Seventeen"
   sNumberText(18) = "Eighteen"
   sNumberText(19) = "Nineteen"
   sNumberText(20) = "Twenty"
   sNumberText(21) = "Thirty"
   sNumberText(22) = "Forty"
   sNumberText(23) = "Fifty"
   sNumberText(24) = "Sixty"
   sNumberText(25) = "Seventy"
   sNumberText(26) = "Eighty"
   sNumberText(27) = "Ninety"
End Sub

Private Function IsBounded(vntArray As Variant) As Boolean
   On Error Resume Next
   IsBounded = IsNumeric(UBound(vntArray))
End Function

Private Function HundredsTensUnits(ByVal TestValue As Integer, _
                              Optional ByVal bUseAnd As Boolean, _
                              Optional ByVal bUseFullAnd As Boolean) As String
   Dim CardinalNumber As Integer
   If TestValue > 99 Then
      CardinalNumber = TestValue \ 100
      HundredsTensUnits = sNumberText(CardinalNumber) & " Hundred "
      TestValue = TestValue - (CardinalNumber * 100)
   End If
   If bUseAnd Or bUseFullAnd Then
      If TestValue Then HundredsTensUnits = HundredsTensUnits & "and "
   End If
   If TestValue > 20 Then
      CardinalNumber = TestValue \ 10
      HundredsTensUnits = HundredsTensUnits & _
                          sNumberText(CardinalNumber + 18) & " "
      TestValue = TestValue - (CardinalNumber * 10)
   End If
   If TestValue > 0 Then
      HundredsTensUnits = HundredsTensUnits & _
                          sNumberText(TestValue) & " "
   End If
End Function
Click to expand...

Hello Rick,
Sorry I did not answer that question as needed. The thing is Cedis a Ghanaian currency. We have cedis and pesewas. The new function worked great. I used the substitute and it worked like a charm.


One issue is I can't substitute for the pesewas. It still shows cents.


Very sorry to keep you guessing.
Regards
Kelly
 
Upvote 0
Kelly try amending the red bits below

Code: 
      If Len(CentsString) > 0 Then
         tmp = tmp & " and " & CentsString
         If CentsString = "One " Then
            tmp = tmp & "[COLOR="#FF0000"]Cent[/COLOR]"
         Else
            tmp = tmp & "[COLOR="#FF0000"]Cents[/COLOR]"
         End If
      End If
 
Upvote 0
MARK858 said:
Kelly try amending the red bits below

Code: 
      If Len(CentsString) > 0 Then
         tmp = tmp & " and " & CentsString
         If CentsString = "One " Then
            tmp = tmp & "[COLOR="#FF0000"]Cent[/COLOR]"
         Else
            tmp = tmp & "[COLOR="#FF0000"]Cents[/COLOR]"
         End If
      End If
Click to expand...

Oh okay!
Great!!!

But this did not work for me last time!!!
Thank you for all you have done for me on this post.

And everyone of you Rick and all those who read this post.

Now I think I am cool to move on.

Nice forum to be

Glad I found here
Kelly
 
Upvote 0
Hello there:
Sorry to wake this post again.
I just observed some issue with the FullAnd keyword. When used , the function always places an and in front of the word or words. So far from all tests I have performed. It works good only if the number value is from one hundred million upwards. And also is it easy to show commas where necessary?

I will be glad someone here can help fix this for me.

Regards
Kelly
 
Last edited:
Upvote 0
You must log in or register to reply here.

Similar threads

DRSteele
  • Question
Spell out numbers into words for Cheques
Replies
3
Views
266
kweaver
kweaver
JohnZ1156
  • Solved
Formula to convert $$$ numbers to text.
Replies
5
Views
583
Joe4
Joe4
D
  • Question
Converting a grouped number and letter to a number
Replies
1
Views
139
arthurbr
arthurbr
B
  • Question
convert amount into words like check writing
Replies
2
Views
270
BABU Baru
B
dcofer
  • Solved
Locating Words With Specific Letters in a Column
Replies
5
Views
185
dcofer
dcofer

Forum statistics

Threads
1,215,398
Messages
6,124,694
Members
449,179
Latest member
kfhw720

Share this page

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