# Thread: So what's numeric? (2000)

1. ## 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. ## 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. ## 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. ## 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. ## 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

6. ## 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. ## 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. ## 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. ## 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. ## 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>

11. ## 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. ## 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.

13. ## 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. ## 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. ## 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 Last

#### Posting Permissions

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