Re: Does the cell have a number?

From: Rob van Gelder (newsgroups_at_nojunkmail-vangelder.co.nz)
Date: 01/25/05 

Date: Wed, 26 Jan 2005 12:07:49 +1300

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" <tjtjjtjt@discussions.microsoft.com> wrote in message 
news:2402D4B7-57FE-4BE5-8E82-4C2C3093F0E2@microsoft.com...
> 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" <iliketodirtbike@hotmail.com> wrote in message
>> news:%23aTwgqxAFHA.1396@tk2msftngp13.phx.gbl...
>> >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" <LanceB@discussions.microsoft.com> wrote in message
>> > news:C5E70B95-2750-4277-A960-4349773A3630@microsoft.com...
>> >> =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
>> >>>
>> >>>
>> >>>
>> >
>> >
>>
>>
>>
Quantcast