Does the cell have a number?
 

I want a formula that will tell me if a given cell has a number in it, so it
evaluates the type of data. I looked in the help files and couldn't figure
this out.

Thanks,
Craig

=ISNUMBER(a1)

"Fletcher" wrote:

I want a formula that will tell me if a given cell has a number in it, so it
evaluates the type of data. I looked in the help files and couldn't figure
this out.

Thanks,
Craig

I tried that, but if the cell has numbers and text, it replies back with
FALSE. I would like to be able to determine if any part of the cell contents
is a number, even if the string starts with, or contains letters.

Thanks!

"LanceB" wrote in message
...
=ISNUMBER(a1)

"Fletcher" wrote:

I want a formula that will tell me if a given cell has a number in it, so
it
evaluates the type of data. I looked in the help files and couldn't
figure
this out.

Thanks,
Craig

Fletcher

You could use a UDF

Function hasNumbers(myString As String) As Boolean
If myString Like "*[1234567890]*" Then
hasNumbers = True
Else
hasNumbers = False
End If
End Function

usage is: =hasNumbers(cellref)


Gord Dibben Excel MVP

On Tue, 25 Jan 2005 13:15:33 -0700, "Fletcher"
wrote:

I tried that, but if the cell has numbers and text, it replies back with
FALSE. I would like to be able to determine if any part of the cell contents
is a number, even if the string starts with, or contains letters.

Thanks!

"LanceB" wrote in message
...
=ISNUMBER(a1)

"Fletcher" wrote:

I want a formula that will tell me if a given cell has a number in it, so
it
evaluates the type of data. I looked in the help files and couldn't
figure
this out.

Thanks,
Craig

=SUMPRODUCT(--(ABS(CODE(MID(A1, ROW(INDIRECT("1:" & LEN(A1))), 1)) -
52.5)<=4.5))0

--
Rob van Gelder - http://www.vangelder.co.nz/excel


"Fletcher" wrote in message
...
I tried that, but if the cell has numbers and text, it replies back with
FALSE. I would like to be able to determine if any part of the cell
contents is a number, even if the string starts with, or contains letters.

Thanks!

"LanceB" wrote in message
...
=ISNUMBER(a1)

"Fletcher" wrote:

I want a formula that will tell me if a given cell has a number in it,
so it
evaluates the type of data. I looked in the help files and couldn't
figure
this out.

Thanks,
Craig

Would you mind explaining why this formula works?
Thanks,
tj

"Rob van Gelder" wrote:

=SUMPRODUCT(--(ABS(CODE(MID(A1, ROW(INDIRECT("1:" & LEN(A1))), 1)) -
52.5)<=4.5))0

--
Rob van Gelder - http://www.vangelder.co.nz/excel


"Fletcher" wrote in message
...
I tried that, but if the cell has numbers and text, it replies back with
FALSE. I would like to be able to determine if any part of the cell
contents is a number, even if the string starts with, or contains letters.

Thanks!

"LanceB" wrote in message
...
=ISNUMBER(a1)

"Fletcher" wrote:

I want a formula that will tell me if a given cell has a number in it,
so it
evaluates the type of data. I looked in the help files and couldn't
figure
this out.

Thanks,
Craig

MID(A1, ROW(INDIRECT("1:" & LEN(A1))), 1) allows the string to be split into
individual entries
CODE(..) returns the ascii code for each character - I'm looking for numbers
which are in the range 48 to 57 for "0" to "9"
Then subtract 52.5, which is midway between 48 and 57 48+(57-48)/2 where
(57-48)/2 = 4.5
ABS turns negative numbers to positive. So this means ascii codes for
numbers are from 0 to 4.5
Check to see whether any of the numbers are less than or equal to 4.5 which
returns a series of TRUE/FALSE
-- turns TRUE, FALSE to 1, 0
SUMPRODUCT adds the entries of an array.
0 means the count of characters which were identified as numbers


--
Rob van Gelder - http://www.vangelder.co.nz/excel


"tjtjjtjt" wrote in message
...
Would you mind explaining why this formula works?
Thanks,
tj

"Rob van Gelder" wrote:

=SUMPRODUCT(--(ABS(CODE(MID(A1, ROW(INDIRECT("1:" & LEN(A1))), 1)) -
52.5)<=4.5))0

--
Rob van Gelder - http://www.vangelder.co.nz/excel


"Fletcher" wrote in message
...
I tried that, but if the cell has numbers and text, it replies back with
FALSE. I would like to be able to determine if any part of the cell
contents is a number, even if the string starts with, or contains
letters.

Thanks!

"LanceB" wrote in message
...
=ISNUMBER(a1)

"Fletcher" wrote:

I want a formula that will tell me if a given cell has a number in
it,
so it
evaluates the type of data. I looked in the help files and couldn't
figure
this out.

Thanks,
Craig

Thank you. It's quite clever.

tj

"Rob van Gelder" wrote:

MID(A1, ROW(INDIRECT("1:" & LEN(A1))), 1) allows the string to be split into
individual entries
CODE(..) returns the ascii code for each character - I'm looking for numbers
which are in the range 48 to 57 for "0" to "9"
Then subtract 52.5, which is midway between 48 and 57 48+(57-48)/2 where
(57-48)/2 = 4.5
ABS turns negative numbers to positive. So this means ascii codes for
numbers are from 0 to 4.5
Check to see whether any of the numbers are less than or equal to 4.5 which
returns a series of TRUE/FALSE
-- turns TRUE, FALSE to 1, 0
SUMPRODUCT adds the entries of an array.
0 means the count of characters which were identified as numbers


--
Rob van Gelder - http://www.vangelder.co.nz/excel


"tjtjjtjt" wrote in message
...
Would you mind explaining why this formula works?
Thanks,
tj

"Rob van Gelder" wrote:

=SUMPRODUCT(--(ABS(CODE(MID(A1, ROW(INDIRECT("1:" & LEN(A1))), 1)) -
52.5)<=4.5))0

--
Rob van Gelder - http://www.vangelder.co.nz/excel


"Fletcher" wrote in message
...
I tried that, but if the cell has numbers and text, it replies back with
FALSE. I would like to be able to determine if any part of the cell
contents is a number, even if the string starts with, or contains
letters.

Thanks!

"LanceB" wrote in message
...
=ISNUMBER(a1)

"Fletcher" wrote:

I want a formula that will tell me if a given cell has a number in
it,
so it
evaluates the type of data. I looked in the help files and couldn't
figure
this out.

Thanks,
Craig