Numeric currency to words

I would like to use a user created function to read a cell containing a 
numeric currency value and put it into words, ie. $103.40 is One Hundred 
Dollars and Forty Cents. Any help? Thanks!
0
Utf
12/14/2009 8:45:02 PM
excel.programming 6508 articles. 2 followers. Follow

9 Replies
849 Views

Similar Articles

[PageSpeed] 16

Here is one way (you would want to use "Dollar" as the second argument to 
the function)...

Go into the VB Editor (Alt+F11) and add a Module to the Workbook 
(Insert/Module from the VBA menu) and then paste in all of the code 
appearing after my signature into the Module's code window. You can call the 
function from your work sheet like this =NumberAsText(A1) where A1 is 
assumed to hold the number you want to convert. There is an Optional 
argument you can use to format the results to your liking. Here are some 
examples to show the options available...

A1:  123.45

In B1:
=NumberAsText(A1)  ==>  One Hundred Twenty Three Point Four Five
=NumberAsText(A1,"And")  ==>  One Hundred and Twenty Three Point Four Five
=NumberAsText(A1,"Check")  ==>  One Hundred Twenty Three and 45/100
=NumberAsText(A1,"Dollar")  ==>  One Hundred Twenty Three Dollars and Forty 
Five Cents
=NumberAsText(A1,"CheckDollar")  ==> One Hundred Twenty Three Dollars and 
45/100

To summarize, using "And" adds the word "and" in front of the tens/units 
text; using "Check" formats any decimal values as would be written on a 
check; and using "Dollar" adds the words "Dollars" and "Cents" in their 
appropriate positions. The code is laid out in a reasonably straight forward 
manner, so if Dollars/Cents is not you native currency designation, you 
should be able to modify the program accordingly. In addition to the above, 
for all modes, the Plus and Minus sign can be used and will be reported back 
as a word; commas may be used to separate the numbers to the left of the 
decimal point but they will not be reported back by the routine and are 
permitted for the users convenience (however, if commas are used, they must 
be placed in their correct positions). And, finally,if I remember correctly, 
this function will work with a whole number part up to one less than a 
quintillion (you can have as many decimal places as desired), but remember 
to format large numbers as Text values... otherwise VB will convert large 
non-Text values to Doubles (which will destroy the conversion).

'******************** START OF 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
  '----------------------------------------
  '  Determine whether to apply special formatting.
  '  If nothing passed, return routine result
  '  converted only into its numeric equivalents,
  '  with no additional format text.
   sStyle = LCase(AND_or_CHECK_or_DOLLAR_or_CHECKDOLLAR)
  '  User passed "AND": "and" will be added
  '  between hundredths and tens of dollars,
  '  ie "Three Hundred and Forty Two"
   bUseAnd = sStyle = "and"
  '  User passed "DOLLAR": "dollar(s)" and "cents"
  '  appended to string,
  '  ie "Three Hundred and Forty Two Dollars"
   bUseDollars = sStyle = "dollar"
  '  User passed "CHECK" *or* "DOLLAR"
  '  If "check", cent amount returned as a fraction /100
  '  i.e. "Three Hundred Forty Two and 00/100"
  '  If "dollar" was passed, "dollar(s)" and "cents"
  '  Appended instead.
   bUseCheck = (sStyle = "check") Or (sStyle = "dollar")
   bUseCheckDollar = sStyle = "checkdollar"
  '----------------------------------------
  '  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
  '----------------------------------------
  '  Prepare to check for valid value in
   NumberIn = Trim$(NumberIn)
   If Not IsNumeric(NumberIn) Then
     '  Invalid entry - abort
      NumberAsText = "Error - Number improperly formed"
      Exit Function
   Else
     '  Decimal check
      DecimalPoint = InStr(NumberIn, ".")
      If DecimalPoint > 0 Then
        '  Split the fractional and primary numbers
         DecimalPart = Mid$(NumberIn, DecimalPoint + 1)
         WholePart = Left$(NumberIn, DecimalPoint - 1)
      Else
        '  Assume the decimal is the last char
         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) & "Quadrillion "
      TestValue = TestValue - (CardinalNumber * 1000000)
   End If
   If TestValue > 999 Then
     CardinalNumber = TestValue \ 1000
     tmp = tmp & HundredsTensUnits(CardinalNumber) & "Trillion "
     TestValue = TestValue - (CardinalNumber * 1000)
   End If
   If TestValue > 0 Then
      tmp = tmp & HundredsTensUnits(TestValue) & "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) & "Million "
      TestValue = TestValue - (CardinalNumber * 1000000)
   End If
   If TestValue > 999 Then
      CardinalNumber = TestValue \ 1000
      tmp = tmp & HundredsTensUnits(CardinalNumber) & "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)
   End If
  '  If in dollar mode, assure the text is the correct plurality
   If bUseDollars = True 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
  '  Note: the application in the IDE will stop
  '  at this line when first run if the IDE error
  '  mode is not set to "Break on Unhandled Errors"
  '  (Tools/Options/General/Error Trapping)
   On Error Resume Next
   IsBounded = IsNumeric(UBound(vntArray))
End Function

Private Function HundredsTensUnits(ByVal TestValue As Integer, _
                              Optional bUseAnd 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 = True 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
'******************** END OF CODE ***********************

-- 
Rick (MVP - Excel)


"Ryan" <Ryan@discussions.microsoft.com> wrote in message 
news:19A4E716-7C5B-4DE3-9EC7-B015748FBD7C@microsoft.com...
>I would like to use a user created function to read a cell containing a
> numeric currency value and put it into words, ie. $103.40 is One Hundred
> Dollars and Forty Cents. Any help? Thanks! 

0
Rick
12/14/2009 9:03:08 PM
hi
look here....
http://support.microsoft.com/?kbid=213360

regards
FSt1

"Ryan" wrote:

> I would like to use a user created function to read a cell containing a 
> numeric currency value and put it into words, ie. $103.40 is One Hundred 
> Dollars and Forty Cents. Any help? Thanks!
0
Utf
12/14/2009 9:09:01 PM
This gives me a "#NAME?" error, is there a security setting I need to change?

"FSt1" wrote:

> hi
> look here....
> http://support.microsoft.com/?kbid=213360
> 
> regards
> FSt1
> 
> "Ryan" wrote:
> 
> > I would like to use a user created function to read a cell containing a 
> > numeric currency value and put it into words, ie. $103.40 is One Hundred 
> > Dollars and Forty Cents. Any help? Thanks!
0
Utf
12/14/2009 9:23:01 PM
Did you put the code in a Module (Insert/Module from VB editor's menu bar)?

-- 
Rick (MVP - Excel)


"Ryan" <Ryan@discussions.microsoft.com> wrote in message 
news:67239A4E-CDB7-4441-A0F7-3F7FDCFC22C9@microsoft.com...
> This gives me a "#NAME?" error, is there a security setting I need to 
> change?
>
> "FSt1" wrote:
>
>> hi
>> look here....
>> http://support.microsoft.com/?kbid=213360
>>
>> regards
>> FSt1
>>
>> "Ryan" wrote:
>>
>> > I would like to use a user created function to read a cell containing a
>> > numeric currency value and put it into words, ie. $103.40 is One 
>> > Hundred
>> > Dollars and Forty Cents. Any help? Thanks! 

0
Rick
12/14/2009 9:33:38 PM
Hello,

You can use my UDF Spellnumber:
http://sulprobil.com/html/spellnumber.html

But Ricks code already does sufficient parameter checks, too.

BTW: http://support.microsoft.com/?kbid=213360 does NOT provide
sufficient parameter checks. Try some of the sample numbers on my
site...

Regards,
Bernd
0
Bernd
12/14/2009 10:31:11 PM
I'm still getting a "#NAME?" error should it be a class module or is there a 
security setting I need to change? Is there another step in setting up a user 
defined function?

"Rick Rothstein" wrote:

> Here is one way (you would want to use "Dollar" as the second argument to 
> the function)...
> 
> Go into the VB Editor (Alt+F11) and add a Module to the Workbook 
> (Insert/Module from the VBA menu) and then paste in all of the code 
> appearing after my signature into the Module's code window. You can call the 
> function from your work sheet like this =NumberAsText(A1) where A1 is 
> assumed to hold the number you want to convert. There is an Optional 
> argument you can use to format the results to your liking. Here are some 
> examples to show the options available...
> 
> A1:  123.45
> 
> In B1:
> =NumberAsText(A1)  ==>  One Hundred Twenty Three Point Four Five
> =NumberAsText(A1,"And")  ==>  One Hundred and Twenty Three Point Four Five
> =NumberAsText(A1,"Check")  ==>  One Hundred Twenty Three and 45/100
> =NumberAsText(A1,"Dollar")  ==>  One Hundred Twenty Three Dollars and Forty 
> Five Cents
> =NumberAsText(A1,"CheckDollar")  ==> One Hundred Twenty Three Dollars and 
> 45/100
> 
> To summarize, using "And" adds the word "and" in front of the tens/units 
> text; using "Check" formats any decimal values as would be written on a 
> check; and using "Dollar" adds the words "Dollars" and "Cents" in their 
> appropriate positions. The code is laid out in a reasonably straight forward 
> manner, so if Dollars/Cents is not you native currency designation, you 
> should be able to modify the program accordingly. In addition to the above, 
> for all modes, the Plus and Minus sign can be used and will be reported back 
> as a word; commas may be used to separate the numbers to the left of the 
> decimal point but they will not be reported back by the routine and are 
> permitted for the users convenience (however, if commas are used, they must 
> be placed in their correct positions). And, finally,if I remember correctly, 
> this function will work with a whole number part up to one less than a 
> quintillion (you can have as many decimal places as desired), but remember 
> to format large numbers as Text values... otherwise VB will convert large 
> non-Text values to Doubles (which will destroy the conversion).
> 
> '******************** START OF 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
>   '----------------------------------------
>   '  Determine whether to apply special formatting.
>   '  If nothing passed, return routine result
>   '  converted only into its numeric equivalents,
>   '  with no additional format text.
>    sStyle = LCase(AND_or_CHECK_or_DOLLAR_or_CHECKDOLLAR)
>   '  User passed "AND": "and" will be added
>   '  between hundredths and tens of dollars,
>   '  ie "Three Hundred and Forty Two"
>    bUseAnd = sStyle = "and"
>   '  User passed "DOLLAR": "dollar(s)" and "cents"
>   '  appended to string,
>   '  ie "Three Hundred and Forty Two Dollars"
>    bUseDollars = sStyle = "dollar"
>   '  User passed "CHECK" *or* "DOLLAR"
>   '  If "check", cent amount returned as a fraction /100
>   '  i.e. "Three Hundred Forty Two and 00/100"
>   '  If "dollar" was passed, "dollar(s)" and "cents"
>   '  Appended instead.
>    bUseCheck = (sStyle = "check") Or (sStyle = "dollar")
>    bUseCheckDollar = sStyle = "checkdollar"
>   '----------------------------------------
>   '  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
>   '----------------------------------------
>   '  Prepare to check for valid value in
>    NumberIn = Trim$(NumberIn)
>    If Not IsNumeric(NumberIn) Then
>      '  Invalid entry - abort
>       NumberAsText = "Error - Number improperly formed"
>       Exit Function
>    Else
>      '  Decimal check
>       DecimalPoint = InStr(NumberIn, ".")
>       If DecimalPoint > 0 Then
>         '  Split the fractional and primary numbers
>          DecimalPart = Mid$(NumberIn, DecimalPoint + 1)
>          WholePart = Left$(NumberIn, DecimalPoint - 1)
>       Else
>         '  Assume the decimal is the last char
>          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) & "Quadrillion "
>       TestValue = TestValue - (CardinalNumber * 1000000)
>    End If
>    If TestValue > 999 Then
>      CardinalNumber = TestValue \ 1000
>      tmp = tmp & HundredsTensUnits(CardinalNumber) & "Trillion "
>      TestValue = TestValue - (CardinalNumber * 1000)
>    End If
>    If TestValue > 0 Then
>       tmp = tmp & HundredsTensUnits(TestValue) & "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) & "Million "
>       TestValue = TestValue - (CardinalNumber * 1000000)
>    End If
>    If TestValue > 999 Then
>       CardinalNumber = TestValue \ 1000
>       tmp = tmp & HundredsTensUnits(CardinalNumber) & "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)
>    End If
>   '  If in dollar mode, assure the text is the correct plurality
>    If bUseDollars = True 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
>   '  Note: the application in the IDE will stop
>   '  at this line when first run if the IDE error
>   '  mode is not set to "Break on Unhandled Errors"
>   '  (Tools/Options/General/Error Trapping)
>    On Error Resume Next
>    IsBounded = IsNumeric(UBound(vntArray))
> End Function
> 
> Private Function HundredsTensUnits(ByVal TestValue As Integer, _
>                               Optional bUseAnd As Boolean) As String
>    Dim CardinalNumber As Integer
>    If TestValue > 99 Then
>       CardinalNumber = TestValue \ 100
>       HundredsTensUnits = sNumberText(CardinalNumber) & " Hundred "
0
Utf
12/15/2009 5:18:02 PM
Assuming your security is set up to run macros (either directly or after 
answering an "Enable Macros" question when you first bring up the workbook), 
then if you are getting a #NAME? error, my guess is you changed the spelling 
of the functions name when you called it on your worksheet; assuming, that 
is, that you simply copy/pasted all the code I gave you directly into a 
Module that you added by clicking Insert/Module from the VB editor's menu 
bar (a standard Module, **not** a Class Module, is where the code has to go 
in order to be used as a UDF).

-- 
Rick (MVP - Excel)


"Ryan" <Ryan@discussions.microsoft.com> wrote in message 
news:330A4AA3-A8DA-4182-9DDE-1BDE9F70871C@microsoft.com...
> I'm still getting a "#NAME?" error should it be a class module or is there 
> a
> security setting I need to change? Is there another step in setting up a 
> user
> defined function?
>
> "Rick Rothstein" wrote:
>
>> Here is one way (you would want to use "Dollar" as the second argument to
>> the function)...
>>
>> Go into the VB Editor (Alt+F11) and add a Module to the Workbook
>> (Insert/Module from the VBA menu) and then paste in all of the code
>> appearing after my signature into the Module's code window. You can call 
>> the
>> function from your work sheet like this =NumberAsText(A1) where A1 is
>> assumed to hold the number you want to convert. There is an Optional
>> argument you can use to format the results to your liking. Here are some
>> examples to show the options available...
>>
>> A1:  123.45
>>
>> In B1:
>> =NumberAsText(A1)  ==>  One Hundred Twenty Three Point Four Five
>> =NumberAsText(A1,"And")  ==>  One Hundred and Twenty Three Point Four 
>> Five
>> =NumberAsText(A1,"Check")  ==>  One Hundred Twenty Three and 45/100
>> =NumberAsText(A1,"Dollar")  ==>  One Hundred Twenty Three Dollars and 
>> Forty
>> Five Cents
>> =NumberAsText(A1,"CheckDollar")  ==> One Hundred Twenty Three Dollars and
>> 45/100
>>
>> To summarize, using "And" adds the word "and" in front of the tens/units
>> text; using "Check" formats any decimal values as would be written on a
>> check; and using "Dollar" adds the words "Dollars" and "Cents" in their
>> appropriate positions. The code is laid out in a reasonably straight 
>> forward
>> manner, so if Dollars/Cents is not you native currency designation, you
>> should be able to modify the program accordingly. In addition to the 
>> above,
>> for all modes, the Plus and Minus sign can be used and will be reported 
>> back
>> as a word; commas may be used to separate the numbers to the left of the
>> decimal point but they will not be reported back by the routine and are
>> permitted for the users convenience (however, if commas are used, they 
>> must
>> be placed in their correct positions). And, finally,if I remember 
>> correctly,
>> this function will work with a whole number part up to one less than a
>> quintillion (you can have as many decimal places as desired), but 
>> remember
>> to format large numbers as Text values... otherwise VB will convert large
>> non-Text values to Doubles (which will destroy the conversion).
>>
>> '******************** START OF 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
>>   '----------------------------------------
>>   '  Determine whether to apply special formatting.
>>   '  If nothing passed, return routine result
>>   '  converted only into its numeric equivalents,
>>   '  with no additional format text.
>>    sStyle = LCase(AND_or_CHECK_or_DOLLAR_or_CHECKDOLLAR)
>>   '  User passed "AND": "and" will be added
>>   '  between hundredths and tens of dollars,
>>   '  ie "Three Hundred and Forty Two"
>>    bUseAnd = sStyle = "and"
>>   '  User passed "DOLLAR": "dollar(s)" and "cents"
>>   '  appended to string,
>>   '  ie "Three Hundred and Forty Two Dollars"
>>    bUseDollars = sStyle = "dollar"
>>   '  User passed "CHECK" *or* "DOLLAR"
>>   '  If "check", cent amount returned as a fraction /100
>>   '  i.e. "Three Hundred Forty Two and 00/100"
>>   '  If "dollar" was passed, "dollar(s)" and "cents"
>>   '  Appended instead.
>>    bUseCheck = (sStyle = "check") Or (sStyle = "dollar")
>>    bUseCheckDollar = sStyle = "checkdollar"
>>   '----------------------------------------
>>   '  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
>>   '----------------------------------------
>>   '  Prepare to check for valid value in
>>    NumberIn = Trim$(NumberIn)
>>    If Not IsNumeric(NumberIn) Then
>>      '  Invalid entry - abort
>>       NumberAsText = "Error - Number improperly formed"
>>       Exit Function
>>    Else
>>      '  Decimal check
>>       DecimalPoint = InStr(NumberIn, ".")
>>       If DecimalPoint > 0 Then
>>         '  Split the fractional and primary numbers
>>          DecimalPart = Mid$(NumberIn, DecimalPoint + 1)
>>          WholePart = Left$(NumberIn, DecimalPoint - 1)
>>       Else
>>         '  Assume the decimal is the last char
>>          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) & "Quadrillion "
>>       TestValue = TestValue - (CardinalNumber * 1000000)
>>    End If
>>    If TestValue > 999 Then
>>      CardinalNumber = TestValue \ 1000
>>      tmp = tmp & HundredsTensUnits(CardinalNumber) & "Trillion "
>>      TestValue = TestValue - (CardinalNumber * 1000)
>>    End If
>>    If TestValue > 0 Then
>>       tmp = tmp & HundredsTensUnits(TestValue) & "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) & "Million "
>>       TestValue = TestValue - (CardinalNumber * 1000000)
>>    End If
>>    If TestValue > 999 Then
>>       CardinalNumber = TestValue \ 1000
>>       tmp = tmp & HundredsTensUnits(CardinalNumber) & "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)
>>    End If
>>   '  If in dollar mode, assure the text is the correct plurality
>>    If bUseDollars = True 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
>>   '  Note: the application in the IDE will stop
>>   '  at this line when first run if the IDE error
>>   '  mode is not set to "Break on Unhandled Errors"
>>   '  (Tools/Options/General/Error Trapping)
>>    On Error Resume Next
>>    IsBounded = IsNumeric(UBound(vntArray))
>> End Function
>>
>> Private Function HundredsTensUnits(ByVal TestValue As Integer, _
>>                               Optional bUseAnd As Boolean) As String
>>    Dim CardinalNumber As Integer
>>    If TestValue > 99 Then
>>       CardinalNumber = TestValue \ 100
>>       HundredsTensUnits = sNumberText(CardinalNumber) & " Hundred " 

0
Rick
12/15/2009 6:38:59 PM
I copied and pasted your code exactly (not including the "***") and I am 
selecting the function from the "incert function" menu. It is recognized as a 
"user defined function" however I am still getting the "#NAME?" error is 
there anyway I can send you the sheet so that you can take a look at it?

"Rick Rothstein" wrote:

> Assuming your security is set up to run macros (either directly or after 
> answering an "Enable Macros" question when you first bring up the workbook), 
> then if you are getting a #NAME? error, my guess is you changed the spelling 
> of the functions name when you called it on your worksheet; assuming, that 
> is, that you simply copy/pasted all the code I gave you directly into a 
> Module that you added by clicking Insert/Module from the VB editor's menu 
> bar (a standard Module, **not** a Class Module, is where the code has to go 
> in order to be used as a UDF).
> 
> -- 
> Rick (MVP - Excel)
> 
> 
> "Ryan" <Ryan@discussions.microsoft.com> wrote in message 
> news:330A4AA3-A8DA-4182-9DDE-1BDE9F70871C@microsoft.com...
> > I'm still getting a "#NAME?" error should it be a class module or is there 
> > a
> > security setting I need to change? Is there another step in setting up a 
> > user
> > defined function?
> >
> > "Rick Rothstein" wrote:
> >
> >> Here is one way (you would want to use "Dollar" as the second argument to
> >> the function)...
> >>
> >> Go into the VB Editor (Alt+F11) and add a Module to the Workbook
> >> (Insert/Module from the VBA menu) and then paste in all of the code
> >> appearing after my signature into the Module's code window. You can call 
> >> the
> >> function from your work sheet like this =NumberAsText(A1) where A1 is
> >> assumed to hold the number you want to convert. There is an Optional
> >> argument you can use to format the results to your liking. Here are some
> >> examples to show the options available...
> >>
> >> A1:  123.45
> >>
> >> In B1:
> >> =NumberAsText(A1)  ==>  One Hundred Twenty Three Point Four Five
> >> =NumberAsText(A1,"And")  ==>  One Hundred and Twenty Three Point Four 
> >> Five
> >> =NumberAsText(A1,"Check")  ==>  One Hundred Twenty Three and 45/100
> >> =NumberAsText(A1,"Dollar")  ==>  One Hundred Twenty Three Dollars and 
> >> Forty
> >> Five Cents
> >> =NumberAsText(A1,"CheckDollar")  ==> One Hundred Twenty Three Dollars and
> >> 45/100
> >>
> >> To summarize, using "And" adds the word "and" in front of the tens/units
> >> text; using "Check" formats any decimal values as would be written on a
> >> check; and using "Dollar" adds the words "Dollars" and "Cents" in their
> >> appropriate positions. The code is laid out in a reasonably straight 
> >> forward
> >> manner, so if Dollars/Cents is not you native currency designation, you
> >> should be able to modify the program accordingly. In addition to the 
> >> above,
> >> for all modes, the Plus and Minus sign can be used and will be reported 
> >> back
> >> as a word; commas may be used to separate the numbers to the left of the
> >> decimal point but they will not be reported back by the routine and are
> >> permitted for the users convenience (however, if commas are used, they 
> >> must
> >> be placed in their correct positions). And, finally,if I remember 
> >> correctly,
> >> this function will work with a whole number part up to one less than a
> >> quintillion (you can have as many decimal places as desired), but 
> >> remember
> >> to format large numbers as Text values... otherwise VB will convert large
> >> non-Text values to Doubles (which will destroy the conversion).
> >>
> >> '******************** START OF 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
> >>   '----------------------------------------
> >>   '  Determine whether to apply special formatting.
> >>   '  If nothing passed, return routine result
> >>   '  converted only into its numeric equivalents,
> >>   '  with no additional format text.
> >>    sStyle = LCase(AND_or_CHECK_or_DOLLAR_or_CHECKDOLLAR)
> >>   '  User passed "AND": "and" will be added
> >>   '  between hundredths and tens of dollars,
> >>   '  ie "Three Hundred and Forty Two"
> >>    bUseAnd = sStyle = "and"
> >>   '  User passed "DOLLAR": "dollar(s)" and "cents"
> >>   '  appended to string,
> >>   '  ie "Three Hundred and Forty Two Dollars"
> >>    bUseDollars = sStyle = "dollar"
> >>   '  User passed "CHECK" *or* "DOLLAR"
> >>   '  If "check", cent amount returned as a fraction /100
> >>   '  i.e. "Three Hundred Forty Two and 00/100"
> >>   '  If "dollar" was passed, "dollar(s)" and "cents"
> >>   '  Appended instead.
> >>    bUseCheck = (sStyle = "check") Or (sStyle = "dollar")
> >>    bUseCheckDollar = sStyle = "checkdollar"
> >>   '----------------------------------------
> >>   '  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
> >>   '----------------------------------------
> >>   '  Prepare to check for valid value in
> >>    NumberIn = Trim$(NumberIn)
> >>    If Not IsNumeric(NumberIn) Then
> >>      '  Invalid entry - abort
> >>       NumberAsText = "Error - Number improperly formed"
> >>       Exit Function
> >>    Else
> >>      '  Decimal check
> >>       DecimalPoint = InStr(NumberIn, ".")
> >>       If DecimalPoint > 0 Then
> >>         '  Split the fractional and primary numbers
> >>          DecimalPart = Mid$(NumberIn, DecimalPoint + 1)
> >>          WholePart = Left$(NumberIn, DecimalPoint - 1)
> >>       Else
> >>         '  Assume the decimal is the last char
> >>          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) & "Quadrillion "
> >>       TestValue = TestValue - (CardinalNumber * 1000000)
> >>    End If
> >>    If TestValue > 999 Then
> >>      CardinalNumber = TestValue \ 1000
> >>      tmp = tmp & HundredsTensUnits(CardinalNumber) & "Trillion "
> >>      TestValue = TestValue - (CardinalNumber * 1000)
> >>    End If
> >>    If TestValue > 0 Then
> >>       tmp = tmp & HundredsTensUnits(TestValue) & "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) & "Million "
> >>       TestValue = TestValue - (CardinalNumber * 1000000)
> >>    End If
> >>    If TestValue > 999 Then
> >>       CardinalNumber = TestValue \ 1000
> >>       tmp = tmp & HundredsTensUnits(CardinalNumber) & "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)
> >>    End If
> >>   '  If in dollar mode, assure the text is the correct plurality
> >>    If bUseDollars = True 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"
0
Utf
12/15/2009 7:35:03 PM
You can send it me using my address for this post... but remove the NOSPAM 
stuff first.

-- 
Rick (MVP - Excel)


"Ryan" <Ryan@discussions.microsoft.com> wrote in message 
news:C5000A1C-0379-4CC3-871F-3D42A1978117@microsoft.com...
>I copied and pasted your code exactly (not including the "***") and I am
> selecting the function from the "incert function" menu. It is recognized 
> as a
> "user defined function" however I am still getting the "#NAME?" error is
> there anyway I can send you the sheet so that you can take a look at it?
>
> "Rick Rothstein" wrote:
>
>> Assuming your security is set up to run macros (either directly or after
>> answering an "Enable Macros" question when you first bring up the 
>> workbook),
>> then if you are getting a #NAME? error, my guess is you changed the 
>> spelling
>> of the functions name when you called it on your worksheet; assuming, 
>> that
>> is, that you simply copy/pasted all the code I gave you directly into a
>> Module that you added by clicking Insert/Module from the VB editor's menu
>> bar (a standard Module, **not** a Class Module, is where the code has to 
>> go
>> in order to be used as a UDF).
>>
>> -- 
>> Rick (MVP - Excel)
>>
>>
>> "Ryan" <Ryan@discussions.microsoft.com> wrote in message
>> news:330A4AA3-A8DA-4182-9DDE-1BDE9F70871C@microsoft.com...
>> > I'm still getting a "#NAME?" error should it be a class module or is 
>> > there
>> > a
>> > security setting I need to change? Is there another step in setting up 
>> > a
>> > user
>> > defined function?
>> >
>> > "Rick Rothstein" wrote:
>> >
>> >> Here is one way (you would want to use "Dollar" as the second argument 
>> >> to
>> >> the function)...
>> >>
>> >> Go into the VB Editor (Alt+F11) and add a Module to the Workbook
>> >> (Insert/Module from the VBA menu) and then paste in all of the code
>> >> appearing after my signature into the Module's code window. You can 
>> >> call
>> >> the
>> >> function from your work sheet like this =NumberAsText(A1) where A1 is
>> >> assumed to hold the number you want to convert. There is an Optional
>> >> argument you can use to format the results to your liking. Here are 
>> >> some
>> >> examples to show the options available...
>> >>
>> >> A1:  123.45
>> >>
>> >> In B1:
>> >> =NumberAsText(A1)  ==>  One Hundred Twenty Three Point Four Five
>> >> =NumberAsText(A1,"And")  ==>  One Hundred and Twenty Three Point Four
>> >> Five
>> >> =NumberAsText(A1,"Check")  ==>  One Hundred Twenty Three and 45/100
>> >> =NumberAsText(A1,"Dollar")  ==>  One Hundred Twenty Three Dollars and
>> >> Forty
>> >> Five Cents
>> >> =NumberAsText(A1,"CheckDollar")  ==> One Hundred Twenty Three Dollars 
>> >> and
>> >> 45/100
>> >>
>> >> To summarize, using "And" adds the word "and" in front of the 
>> >> tens/units
>> >> text; using "Check" formats any decimal values as would be written on 
>> >> a
>> >> check; and using "Dollar" adds the words "Dollars" and "Cents" in 
>> >> their
>> >> appropriate positions. The code is laid out in a reasonably straight
>> >> forward
>> >> manner, so if Dollars/Cents is not you native currency designation, 
>> >> you
>> >> should be able to modify the program accordingly. In addition to the
>> >> above,
>> >> for all modes, the Plus and Minus sign can be used and will be 
>> >> reported
>> >> back
>> >> as a word; commas may be used to separate the numbers to the left of 
>> >> the
>> >> decimal point but they will not be reported back by the routine and 
>> >> are
>> >> permitted for the users convenience (however, if commas are used, they
>> >> must
>> >> be placed in their correct positions). And, finally,if I remember
>> >> correctly,
>> >> this function will work with a whole number part up to one less than a
>> >> quintillion (you can have as many decimal places as desired), but
>> >> remember
>> >> to format large numbers as Text values... otherwise VB will convert 
>> >> large
>> >> non-Text values to Doubles (which will destroy the conversion).
>> >>
>> >> '******************** START OF 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
>> >>   '----------------------------------------
>> >>   '  Determine whether to apply special formatting.
>> >>   '  If nothing passed, return routine result
>> >>   '  converted only into its numeric equivalents,
>> >>   '  with no additional format text.
>> >>    sStyle = LCase(AND_or_CHECK_or_DOLLAR_or_CHECKDOLLAR)
>> >>   '  User passed "AND": "and" will be added
>> >>   '  between hundredths and tens of dollars,
>> >>   '  ie "Three Hundred and Forty Two"
>> >>    bUseAnd = sStyle = "and"
>> >>   '  User passed "DOLLAR": "dollar(s)" and "cents"
>> >>   '  appended to string,
>> >>   '  ie "Three Hundred and Forty Two Dollars"
>> >>    bUseDollars = sStyle = "dollar"
>> >>   '  User passed "CHECK" *or* "DOLLAR"
>> >>   '  If "check", cent amount returned as a fraction /100
>> >>   '  i.e. "Three Hundred Forty Two and 00/100"
>> >>   '  If "dollar" was passed, "dollar(s)" and "cents"
>> >>   '  Appended instead.
>> >>    bUseCheck = (sStyle = "check") Or (sStyle = "dollar")
>> >>    bUseCheckDollar = sStyle = "checkdollar"
>> >>   '----------------------------------------
>> >>   '  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
>> >>   '----------------------------------------
>> >>   '  Prepare to check for valid value in
>> >>    NumberIn = Trim$(NumberIn)
>> >>    If Not IsNumeric(NumberIn) Then
>> >>      '  Invalid entry - abort
>> >>       NumberAsText = "Error - Number improperly formed"
>> >>       Exit Function
>> >>    Else
>> >>      '  Decimal check
>> >>       DecimalPoint = InStr(NumberIn, ".")
>> >>       If DecimalPoint > 0 Then
>> >>         '  Split the fractional and primary numbers
>> >>          DecimalPart = Mid$(NumberIn, DecimalPoint + 1)
>> >>          WholePart = Left$(NumberIn, DecimalPoint - 1)
>> >>       Else
>> >>         '  Assume the decimal is the last char
>> >>          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) & "Quadrillion "
>> >>       TestValue = TestValue - (CardinalNumber * 1000000)
>> >>    End If
>> >>    If TestValue > 999 Then
>> >>      CardinalNumber = TestValue \ 1000
>> >>      tmp = tmp & HundredsTensUnits(CardinalNumber) & "Trillion "
>> >>      TestValue = TestValue - (CardinalNumber * 1000)
>> >>    End If
>> >>    If TestValue > 0 Then
>> >>       tmp = tmp & HundredsTensUnits(TestValue) & "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) & "Million "
>> >>       TestValue = TestValue - (CardinalNumber * 1000000)
>> >>    End If
>> >>    If TestValue > 999 Then
>> >>       CardinalNumber = TestValue \ 1000
>> >>       tmp = tmp & HundredsTensUnits(CardinalNumber) & "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)
>> >>    End If
>> >>   '  If in dollar mode, assure the text is the correct plurality
>> >>    If bUseDollars = True 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" 

0
Rick
12/15/2009 7:45:35 PM
Reply:

Similar Artilces:

Numeric currency to words
I would like to use a user created function to read a cell containing a numeric currency value and put it into words, ie. $103.40 is One Hundred Dollars and Forty Cents. Any help? Thanks! Here is one way (you would want to use "Dollar" as the second argument to the function)... Go into the VB Editor (Alt+F11) and add a Module to the Workbook (Insert/Module from the VBA menu) and then paste in all of the code appearing after my signature into the Module's code window. You can call the function from your work sheet like this =NumberAsText(A1) where A1 is assumed ...

Database text converted to numeric
I Imported data from a SQL Database, it is numeric and alpha numeric but mostly numeric numbers (Serial numbers). I need to compare this list to another Excel list containing the same information but with some numbers missing. I pull this into Access and when I try and compare them the error is that the format has to be the same. I changed the format of the list I imported to Numeric (most of the numbers is only numbers) but now I have to press F2 and enter on every single number (database is only 227500 lines) to convert it to a Number. I thought it was because it still saw itself as a Dat...

word to excel
Version: 2008 Operating System: Mac OS X 10.4 (Tiger) can you take a word document (of mailing labels) and export to an excel sheet for data management. What's the best way to get these word labels into excel? On 9/4/08 12:27 PM, in article 59b58adf.-1@webcrossing.caR9absDaxw, "scanby@officeformac.com" <scanby@officeformac.com> wrote: > Version: 2008 > Operating System: Mac OS X 10.4 (Tiger) > > can you take a word document (of mailing labels) and export to an excel sheet > for data management. What's the best way to get these word labels into excel? ...

Money 2001
I have recently moved to Papua New Guinea and have set up my local accounts on Money 2001. I have added the local currency - PGK kina to the currencies but when I try to download my bank statements I get this error box message: Import Completed Your file import was completed, but 1 item could not be processed... File import X Your statement contained a default currency (PGK) which was unrelated to the currency of the account "Westpac PNG Cheque". Money does not support statements containing unrelated default currencies. I was dowloading the file in OFX format. QIF files work...

Access 2000... how to import a word document?
I am told Access will allow inport of a Word document by converting the document to Rich Text w/delimiter, then into Access. Does that not work with Access 2000? It depends what you mean by "import a Word document". If you have data in a table in a Word document, convert the table to text, paste the text into a new file, and save it as plain (not rich) text; then import the text file into Access. Alternatively, paste the table into Excel, save in Excel or CSV format, and import the resulting file into Access. Which of these works best depends on just what's in the data. ...

Random Conversion of alpha/numeric text with letter "e" to exponen
OS - w2003, office 2003, desktop XP Pro/ w2000 Pro My company uses a six digit alpha - numeric string for customer account numbers. a few are all numeric, most are in the format: ##X###, where # = numeric and X = an alpha character. A few accounts, 101 out of 360, that contain an "e" as the alpha character are converted to an exponential by Excel. the other 259 are not. These 101 accounts where the first accounts created using "e", the subsequent 259 accounts are not converted by Excel. Anyone have a clue as to what is going on and how I can stop it? Thanks, --...

text form to numeric ?
E.g A cell contain 1233- but the value is actually a numric value whereby the "-" behind is a negative sign, wat function do I use to convert it to a numeric form causing it to to become -1233 ? One way, assuming that the values are mixed (some with/without "-" sign) and that the values are in colA, which is formatted as text; IF(RIGHT(Ax,1)="-",LEFT(Ax,LEN(Ax)-1)*-1,VALUE(Ax)) -- Regards; Rob ------------------------------------------------------------------------ "crapit" <littlecramp@yahoo.com> wrote in message news:OSAgKf$1DHA.2324@TK2MS...

Word 2008 OpenType font kerning issue
I=92m having some serious font kerning (character spacing) issues, and it seems that the OpenType file format is the culprit. When I create a new document in Word 2008 using an OpenType font, everything looks fine at first. Even after I close the document and re- open it, everything still looks fine. The problem kicks in after I quit out of Word and try to reopen the doc. After Word launches and the doc opens, the kerning looks terrible. (I=92m not talking about imperfect kerning that would only be noticed by a type designer; it really does look awful.) This happens every single time...

sorting alpha numerics
I have a list of documents that I need to sort by title. Example AFI10-229 AFI10-245 AFI10-1101 After I sort, the list looks like this AFI10-1101 AFI10-229 AFI10-245 How do I make it take the entire alphanumeric into account? Let A2:A4 house the sample to sort. In B2 enter & copy down: =--REPLACE(A2,1,SEARCH("-",A2),"") Sort A2:B4, with column A in ascending order and column B in descendin order. Sproove Wrote: > I have a list of documents that I need to sort by title. Example > > AFI10-229 > AFI10-245 > AFI10-1101 > > After I sort, th...

Numeric -> Text conversion
I've got a date field, and have extracted the month using the MONTH function. I want to put the month into a text field, two digits in width. e.g. 07 for July, 09 for September, 12 for December. I don't want it 'formatted' so that it looks like two digits. I want the actual value to be two digits. Any quick solutions ?? Using Office 2000. Regards, Mike. =TEXT(monthnumber,"00") -- Best Regards Leo Heuser MVP Excel Followup to newsgroup only, please. "Mike Gorton" <micky@notthere.com> skrev i en meddelelse news:OBDPeA3SDHA.2280@TK2MSFTNGP12...

is it possible to modify a font I'm using in word or publisher
The particular font I'm using would look better (for the project) if it were further 'slanted' forward (even more so than itacicizing does for it). Is there any way to achieve this? You could use WordArt, you can skew WordArt. It probably isn't practical with a lot of text, but it will work with a bit of tweaking. -- Mary Sauer MSFT MVP http://office.microsoft.com/ http://msauer.mvps.org/ news://msnews.microsoft.com "SadieSue" <SadieSue@discussions.microsoft.com> wrote in message news:181F1157-A8BB-4F36-BFC8-55A0D18EA2C8@microsoft.com... > The particu...

Nested Styles in Word 2003?
Is it possible to create a nested style in Word 2003? An example would be to make the first sentence of a paragraph Body Text bold but the rest of the paragraph plain Body Text. I have used this type of style with Adobe InDesign but haven't found a way to make it work in Word. It's not a big issue for me as I have used character formatting over the Body Text style where appropriate but I am curious. Word can do this, but not with "nested styles" (there is no such concept in Word). Instead, there is a way to apply a style to only part of a paragraph. See http://...

convert alpha to a numeric value
I received data from a user where negative numbers are sent as : "31Neg" for a "-31". Can you suggest a way to create a real negative number. Thanks in advance for you help. You could use a macro Sub ConvAlpha() For Each c In ActiveSheet.UsedRange v = c.Value If Right(c, 3) = "Neg" Then c.Value = Left(v, Len(v) - 3) * -1 End If Next c End Sub Not very efficient though, so if the sheet is huge it will take time. -- Regards Ken....................... Microsoft MVP - Excel Sys Spec - Win XP Pro / XL 00/02/03 ...

Misspelled word in Puslisher 2003
Spell check is not catching 'colonge' (cologne, is correct). I checked the custom dictionary, but it is not part of the added words. Any ideas? -- Stephfunny In my version of Publisher 2003, this word is part of the autocorrect that automatically changes colonge to cologne. Are you getting a tag under the word? -- Mary Sauer MSFT MVP http://office.microsoft.com/ http://msauer.mvps.org/ news://msnews.microsoft.com "Stephfunny" <Stephfunny@discussions.microsoft.com> wrote in message news:DB4B9061-783A-4392-9C21-FDDF15786155@microsoft.com... > Spell check is n...

File Move-Drag and Drop Fails in Office 2007 Excel & Word
One one of our Windows XP SPK3 computers were running Office 2007. When ever your using Word or Excel and you use either the File Open or File Save commands and the Windowed Dialog Box appears with your file and folder listings it will not let you move or drag and drop a file from one location to another. Lets say you have 3 Sub Folders in My Documents ( Folder 1, Folder 2 and Folder 3 each containing files.) You notice while in the File / Open or File / Save Dialog Box window that their are several files which currently reside in the My Documents folder but should be in say ...

Create Multiple TOCs with Word 2007
I have used the RD field in the past (Word 2003) to create a TOC doc from several separate documents. I have tried this unsuccessfully with Word 2007. Is there a new technique or field that is used. I cannot combine the docs because they add up to over 400 pages and 35Mb and the merged file is unstable due to the extensive formatting required. I have figured it out. The dreaded syntax misspelling! The RD field does still work for Word 2007 much the same as in 2003. "Max86" wrote: > I have used the RD field in the past (Word 2003) to create a TOC doc from >...

I have Word 2003 but get offered Word 2007 updates
Why do I get offered updates by the Windows updater for Word 2007 when I have Word 2003? (I have a Powerpoint 2007 viewer but no other Office 2007 products.) Is it safe to install the updates? Paul wrote: > Why do I get offered updates by the Windows updater for Word 2007 > when I have Word 2003? > > (I have a Powerpoint 2007 viewer but no other Office 2007 products.) > > Is it safe to install the updates? That's good enough. You have some components of Office 2007. Probably components shared by all the office 2007 products. Give the exact KB art...

Importing Word Document
I'm not getting the "Import Word Document" dialog box with the Blank Sizes, it only comes up with the Classic design. I have tried opening publisher, clicking on Import Word Document (left side), then click on the blank size I want, create. All that comes up is 4 pages. If I then use the Import Word Document from File, it creates a new publication with what seems to be a default design. Any ideas? ...

Converting Numerical Values Into Text
This is a bit complicated but: I am trying to import numerical values to create a "Custom List". I order to do this I need to change the numerical values into text. have reformatted the cells using the "Text" option but the Custom Lis import facility does not recognise this change unless I have edite each cell in turn using the F2 and Return keys. Fine if there are onl a few rows of data but a nightmare if there are several hundred rows o data. Which there are. Does anyone know of a simpler, more efficient wa of "validating" the conversion from numerical values...

euro currency format
recently someone posted asking how to get the ms download for euro formatting. you can download it here. go to office97. excel. add ins. http://office.microsoft.com/home/default.aspx ...

Numeric formatting
We have developed a C++/MFC application that interfaces with a database application that returns numeric values and a format string in the style of Visual Basic's "Format" command. Is there any C++/Win32 support for applying such a format string to a numeric value? Any example format string would be: "$#,##0;($#,##0)" Any pointers or help are greatly appreciated. thanks Chris You will found this Link Intresting http://www.codeproject.com/editctrl/#Masked+and+Validating+controls -- With Regards Alok Gupta Visit me at http://alok.bizhat.com ...

How do I create Hyperlinks to My Documents in Word?
I am creating a document with setup instructions. There are a few cases where I would like to create a hyperlink to the user's My Documents folder (or a subfolder underneath it), so they can click on the link to open Windows Explorer to that folder. When I generate the Hyperlink on my computer, the address is generated as C:\Documents and Settings\<username>\My Documents. I would like to replace <username> with something that uses the current user's username. With .cmd files I'm able to user %username% or even %userprofile%. When I try that with the...

Pivot Table
I am producing a pivot table with prices in £'s and $'s but unable to get the currency symbol to display. Is this possible? -- Please help! EXCEL 2007 Yes. I have just tested this. Set up Pivot Table to be half in dollars and half in pounds. Saved file and closed it. Re-opened file. Pivot Table was still formatted half in dollars and half in pounds. I formatted in the, "normal" way:- - highlighed those cells I wished to format - Home group / Font / clicked on arrow in lower right hand corner / Format Cells launched / Number...

check decimal and if numeric
I have an unbound textbox, txtAmount. I want to make sure that user enters only two decimal places, and that the input is numeric. I tried using InputMask 99999999.99 which works for positive numbers, but that mask won't allow the minus sign (or plus sign) to be used. So, how can I allow input of a negative number? Currently, if TransactType is "Payment", it takes the value in Amount * -1 (if existing Amount > 0), so that effectively makes it a negative. But, a TransactType of "Adjustment" could be either negative or positive. I could make a TransactType of "...

Carriage Return Symbol Text Field Word Form VBA Problem inside Tab
I am trying to insert some text into a protected Word text field with carriage returns for new lines via VBA code. For example, I would like to enter something like the following into a text field called Text1: Test Line 1 Test Line 2 Test Line 3 So my code is: ActiveDocument.FormFields("Text1").Result = "Test Line 1" & Chr(13) & "Test Line 2" & Chr(13) & "Test Line 3" The text field is located inside of a table cell. The problem is that it is inserting the box symbol for chr(13) instead of a new line. So it looks l...