Page 1 of 2 12 LastLast
Results 1 to 15 of 17
  1. #1
    Platinum Lounger
    Join Date
    Nov 2001
    Location
    Melbourne, Victoria, Australia
    Posts
    5,016
    Thanks
    0
    Thanked 0 Times in 0 Posts

    So what's numeric? (2000)

    I think this might have been discussed a while back, but I can't locate it, sorry. I was using the IsNumeric function on a range of cells, and XL picked up the empty ones as being numeric. I got round this odd interpretation by adding a <> "" condition to what I was doing, but I'm wondering what else might be considered to be (potentially/ conceivably/ vaguely) numeric according to this function. Dates for instance?

    Alan

  2. #2
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: So what's numeric? (2000)

    Dates and times are numbers in Excel (a date is stored as the number of days since December 31, 1899)
    Percentages, fractions and currency are obviously numeric too (they are just formatted numbers).
    Cells containing a formula that results in a numeric value (including dates) count as numeric too.

  3. #3
    Platinum Lounger
    Join Date
    Nov 2001
    Location
    Melbourne, Victoria, Australia
    Posts
    5,016
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: So what's numeric? (2000)

    Thanks Hans. I figured as much. So how about other forms of "blank" cells? The empty cell is numeric - what about those containing, say, the single control character chr(10) or some spaces? And is there a more specific function to identify an actual number - something containing only digits, decimal point and leading minus sign? Or is it necessary to write one?

    This conveniently leads into another question I have <img src=/S/grin.gif border=0 alt=grin width=15 height=15>. I'm pulling cells from a column and treating them as integers (hence my original query). In order to do some number manipulation on them, I've had to cast them as ints using cInt(). I'd expect the .Cells().Value I'm using to return a string (or variant?) but I'm wondering if I can do something with the format of the column (a number format rather than General) so that .Value will be a number. Somehow I doubt this, since it's still possible to enter anything at all into said column. Any thoughts on this?

    thanks

    Alan

  4. #4
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: So what's numeric? (2000)

    Alan, I don't know if I understand what you're asking, but if you want to make sure that users can only enter numeric valies in a column, you can set Data | Validation.

    Excel (and VBA) are very flexible in interpreting what's thrown at them. Enter 43q in a cell and it will be left-aligned, indicating it's text; change it to 432 and it will be right-aligned, since Excel recognizes a valid number. VBA converts values to the most appopriate type silently: it will run

    Dim intNum As Integer
    intNum = "99.4"
    MsgBox intNum

    without batting an eyelash - the string "99.4" will be converted to a number 99.4 and that will be forced into the integer value 99. This is both a strength and a weakness - it is very flexible, but may have unexpected results.

  5. #5
    Super Moderator
    Join Date
    May 2002
    Location
    Canberra, Australian Capital Territory, Australia
    Posts
    5,054
    Thanks
    2
    Thanked 417 Times in 346 Posts

    Re: So what's numeric? (2000)

    Hi Alan,
    <hr>is there a more specific function to identify an actual number - something containing only digits, decimal point and leading minus sign?<hr>
    You could try a invoking combination of the worksheet functions ISNUMBER(address) and CELL("format",address), which together will tell you whether a cell has a non-zero numeric value and the way the cell is formatted. The latter can differentiate between numbers of varying precision and time/dates etc.

    Cheers
    Cheers,

    Paul Edstein
    [MS MVP - Word]

  6. #6
    3 Star Lounger Jim Cone's Avatar
    Join Date
    Feb 2002
    Location
    Portland, Oregon, USA
    Posts
    238
    Thanks
    0
    Thanked 3 Times in 3 Posts

    Re: So what's numeric? (2000)

    Alan,

    What you have described is the "Val" function.
    It recognizes only the period (.) as a decimal delimiter.
    $ and comma (,) are not recognized as part of a number.
    Val also removes spaces, tabs and linefeed characters prior to processing.
    It stops reading at the first non-numeric character.
    It returns zero (0) if no numeric value is found.

    I couldn't write a program without it.
    An example from my "Side by Side" program...

    StartCount =CLng(Abs(Val(strVariable)))

    The above eliminates decimal fractions, minus numbers and text.

    Regards,
    Jim Cone
    San Francisco, CA

  7. #7
    Platinum Lounger
    Join Date
    Nov 2001
    Location
    Melbourne, Victoria, Australia
    Posts
    5,016
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: So what's numeric? (2000)

    Looks like I've failed to explain myself properly again. <img src=/S/blush.gif border=0 alt=blush width=15 height=15> I have a column of what should be all either ints or empty cells.
    I sometimes pass one of these values to a function that expects an integer. Before I do so, I want to check that it
    really is an integer (not a blank cell or some erroneous entry), then cast it to integer using
    cInt (which seems necessary to do for the function to work).
    I was trying the function IsNumeric to this end, but it returns True for an empty cell.
    What I really want I suppose is an IsInteger function.
    I did manage to hunt down this code, which might do the trick with some mods to exclude negative ints:

    <pre>The IsNumeric function returns some odd values.
    For example, IsNumeric("10-") is true even though you
    probably don't want to consider "10-" to be a valid integer.
    This function tests to see whether a value is an integer.

    Thanks to Hisham Nassef

    Private Function IsInteger(str As String) As Boolean
    Dim i As Byte
    Dim CharAscii As Integer
    Dim Count As Byte

    For i = 1 To Len(str)
    CharAscii = Asc(Mid(str, i, 1))
    If (CharAscii > 47 And CharAscii < 58) Then
    Count = Count + 1
    Else
    If i = 1 Then
    If CharAscii <> 43 And CharAscii <> 45 Then
    IsInteger = False
    Exit For
    End If
    Else
    IsInteger = False
    Exit For
    End If
    Count = Count + 1
    End If
    Next

    If Count = Len(str) Then
    If Val(str) > 32767 Or Val(str) < -32768 Then
    IsInteger = False
    Else
    IsInteger = True
    End If
    End If
    End Function

    Private Sub Command1_Click()
    lblIsNumeric.Caption = Format(IsNumeric(Text1.Text))
    lblIsInteger.Caption = Format(IsInteger(Text1.Text))
    End Sub
    </pre>


    The identification of an empty cell as potentially numeric led me to wonder what else VB considers to be numeric.
    Hope that explains things better.

    Alan

  8. #8
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: So what's numeric? (2000)

    The function you post will still return TRUE for an empty cell. Does this do what you want?

    Function IsIntegerŪ
    If r = "" Then
    IsInteger = False
    ElseIf IsNumericŪ Then
    If r = IntŪ Then
    IsInteger = (r < 32768 And r > -32769)
    End If
    Else
    IsInteger = False
    End If
    End Function

    If you don't negative integers to return TRUE, change (r < 32768 And r > -32769) to (r < 32768 And r > -1) and if the numbers should be positive, you shouldn't have worried so much, because empty cells would fail the > 0 test.

  9. #9
    Platinum Lounger
    Join Date
    Nov 2001
    Location
    Melbourne, Victoria, Australia
    Posts
    5,016
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: So what's numeric? (2000)

    OK, thanks again Hans. I think I must have exhausted this by now, and that the function you posted should do the trick. I am interested only in positive integers, but I'm not sure what you mean by "empty cells would fail the > 0 test". The
    r = IntŪ
    expression is also what was eluding me.

    cheers

    Alan

  10. #10
    Uranium Lounger
    Join Date
    Jan 2001
    Location
    South Carolina, USA
    Posts
    7,295
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: So what's numeric? (2000)

    If you only want positive integers greater than zero, then change Hans' function to:

    <pre>Function IsIntegerŪ
    If r = "" Then
    IsInteger = False
    ElseIf IsNumericŪ Then
    If r = IntŪ Then
    IsInteger = r >0
    End If
    Else
    IsInteger = False
    End If
    End Function
    </pre>

    Legare Coleman

  11. #11
    Platinum Lounger
    Join Date
    Nov 2001
    Location
    Melbourne, Victoria, Australia
    Posts
    5,016
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: So what's numeric? (2000)

    Thanks Macropod. I don't think I explained my requirements very well - <post#=289662>post 289662</post#> is a better take on what I was trying to do. The query about IsNumeric() really arose out of my (still) amazement that an empty cell is considered to be numeric.

    Alan

  12. #12
    Uranium Lounger
    Join Date
    Jan 2001
    Location
    South Carolina, USA
    Posts
    7,295
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: So what's numeric? (2000)

    On a worksheet make sure that A1 is empty. In any other cell enter the formula =A1. If you don't have "View zero values" turned off in your options, then that cell should display Zero. The reason that IsNumeric returns True for empty cells is because that is what they are in a formula.
    Legare Coleman

  13. #13
    Platinum Lounger
    Join Date
    Nov 2001
    Location
    Melbourne, Victoria, Australia
    Posts
    5,016
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: So what's numeric? (2000)

    Thanks Jim. Your function is interesting, but not what I was after. I don't want to extract the numeric portion of the cell contents, but rather make sure it's a positive integer... otherwise post an alert or skip over it if the cell is empty. I found what I was after - <post#=289662>post 289662</post#> and what Hans was able to suggest.

    cheers

    Alan

  14. #14
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: So what's numeric? (2000)

    Legare already proposed a modification.

    An empty cell passes the IsNumeric test, but its value evaluates to 0, so if you test for > 0, it DOESN'T pass the test.

  15. #15
    Platinum Lounger
    Join Date
    Nov 2001
    Location
    Melbourne, Victoria, Australia
    Posts
    5,016
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: So what's numeric? (2000)

    Thanks Legare. I think my whole take on the IsNumeric() function was based on a rather more "basic" interpretation of what ought to be considered a number. To me, it must be something containing only an optional leading

Page 1 of 2 12 LastLast

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •