Formula to recognize text only in a cell

In cells A1:A3 I have:
(as text)

Cell Values Formula Needed
0100 01029250 FALSE
0100 01029304 FALSE
0100 REHAB01 TRUE

I need a formula in Cells B1:B3
to Recognize is a cells has characters A-Z

I can't quite figure out here, under pressure...

Can someone assist?

Maybe

=NOT(ISNUMBER(1*SUBSTITUTE(A1," ","")))



"Jim May" wrote:

> In cells A1:A3 I have:
> (as text)
>
> Cell Values Formula Needed
> 0100 01029250 FALSE
> 0100 01029304 FALSE
> 0100 REHAB01 TRUE
>
> I need a formula in Cells B1:B3
> to Recognize is a cells has characters A-Z
>
> I can't quite figure out here, under pressure...
>
> Can someone assist?
>

Brilliant!!
Tks,
Jim

"Duke Carey" wrote:

> Maybe
>
> =NOT(ISNUMBER(1*SUBSTITUTE(A1," ","")))
>
>
>
> "Jim May" wrote:
>
> > In cells A1:A3 I have:
> > (as text)
> >
> > Cell Values Formula Needed
> > 0100 01029250 FALSE
> > 0100 01029304 FALSE
> > 0100 REHAB01 TRUE
> >
> > I need a formula in Cells B1:B3
> > to Recognize is a cells has characters A-Z
> >
> > I can't quite figure out here, under pressure...
> >
> > Can someone assist?
> >

Hi Duke,
It works but it doesn't make sense to me, can you break it down..

---
HTH,
David McRitchie, Microsoft MVP - Excel [site changed Nov. 2001]
My Excel Pages: http://www.mvps.org/dmcritchie/excel/excel.htm
Search Page: http://www.mvps.org/dmcritchie/excel/search.htm

"Jim May" <JimMay@discussions.microsoft.com> wrote in message news:AB5636E5-D363-4E50-8437-0F080637BCC8@microsoft.com...
> Brilliant!!
> Tks,
> Jim
>
> "Duke Carey" wrote:
>
> > Maybe
> >
> > =NOT(ISNUMBER(1*SUBSTITUTE(A1," ","")))
> >
> >
> >
> > "Jim May" wrote:
> >
> > > In cells A1:A3 I have:
> > > (as text)
> > >
> > > Cell Values Formula Needed
> > > 0100 01029250 FALSE
> > > 0100 01029304 FALSE
> > > 0100 REHAB01 TRUE
> > >
> > > I need a formula in Cells B1:B3
> > > to Recognize is a cells has characters A-Z
> > >
> > > I can't quite figure out here, under pressure...
> > >
> > > Can someone assist?
> > >

On Tue, 7 Mar 2006 09:04:24 -0800, Duke Carey
<DukeCarey@discussions.microsoft.com> wrote:

>Maybe
>
>=NOT(ISNUMBER(1*SUBSTITUTE(A1," ","")))
>
>
>
>"Jim May" wrote:
>
>> In cells A1:A3 I have:
>> (as text)
>>
>> Cell Values Formula Needed
>> 0100 01029250 FALSE
>> 0100 01029304 FALSE
>> 0100 REHAB01 TRUE
>>
>> I need a formula in Cells B1:B3
>> to Recognize is a cells has characters A-Z
>>
>> I can't quite figure out here, under pressure...
>>
>> Can someone assist?
>>

Although it may be what he wanted, it's not quite what he asked for.

He asked for a function that would "Recognize is a cells has characters A-Z"

Your function will give a TRUE result for many other non-numeric characters
than the set A-Z.
--ron

I guess I didn't test that i.e. 123#
in fact I looked at the wrong column in my test., or I would have seen it
was incorrect for an empty cell, just the same I'm
still trying to figure out the formula anyway.


=NOT(ISNUMBER(1*SUBSTITUTE(A1," ","")))


Ron Rosenfeld, wrote...
> Although it may be what he wanted, it's not quite what he asked for.
> He asked for a function that would "Recognize is a cells has characters A-Z"
> Your function will give a TRUE result for many other non-numeric characters
> than the set A-Z.

Hey Guys,,
Yeah, I studied the
=NOT(ISNUMBER(1*SUBSTITUTE(A1," ","")))
and decided that basically, the cell strigng was first reviewed for any
**Spaces** (Which all my cells
qualify for),, but further ALL spaces are replaced by
"" (Nothing),, thereby - in the case of those cells with
only numbers AND spaces gets you only numbers TIMES 1 - changes THIS
Cell-Type to a NUMERIC;
All other types DO NOT QUALIFY..

Thanks,

Jim May


"David McRitchie" <dmcritchie_xlmvp@verizon.net> wrote in message
news:eV3TdmjQGHA.5280@TK2MSFTNGP12.phx.gbl...
>I guess I didn't test that i.e. 123#
> in fact I looked at the wrong column in my test., or I would have seen it
> was incorrect for an empty cell, just the same I'm
> still trying to figure out the formula anyway.
>
>
> =NOT(ISNUMBER(1*SUBSTITUTE(A1," ","")))
>
>
> Ron Rosenfeld, wrote...
>> Although it may be what he wanted, it's not quite what he asked for.
>> He asked for a function that would "Recognize is a cells has characters
>> A-Z"
>> Your function will give a TRUE result for many other non-numeric
>> characters
>> than the set A-Z.
>
>

and 1* to convert a string with digits to a number or an error.
Somehow I was convinced it was doing more.

"Jim May" <jmay@cox.net> wrote in message news:fxoPf.234343$oG.193208@dukeread02...
> Hey Guys,,
> Yeah, I studied the
> =NOT(ISNUMBER(1*SUBSTITUTE(A1," ","")))
> and decided that basically, the cell strigng was first reviewed for any
> **Spaces** (Which all my cells
> qualify for),, but further ALL spaces are replaced by
> "" (Nothing),, thereby - in the case of those cells with
> only numbers AND spaces gets you only numbers TIMES 1 - changes THIS
> Cell-Type to a NUMERIC;
> All other types DO NOT QUALIFY..
>
> Thanks,
>
> Jim May
>
>
> "David McRitchie" <dmcritchie_xlmvp@verizon.net> wrote in message
> news:eV3TdmjQGHA.5280@TK2MSFTNGP12.phx.gbl...
> >I guess I didn't test that i.e. 123#
> > in fact I looked at the wrong column in my test., or I would have seen it
> > was incorrect for an empty cell, just the same I'm
> > still trying to figure out the formula anyway.
> >
> >
> > =NOT(ISNUMBER(1*SUBSTITUTE(A1," ","")))
> >
> >
> > Ron Rosenfeld, wrote...
> >> Although it may be what he wanted, it's not quite what he asked for.
> >> He asked for a function that would "Recognize is a cells has characters
> >> A-Z"
> >> Your function will give a TRUE result for many other non-numeric
> >> characters
> >> than the set A-Z.
> >
> >
>
>

One way.........

=IF(ISERR(MID(A1,6,1)*1),"true","false")

Vaya con Dios,
Chuck, CABGx3


"Jim May" wrote:

> In cells A1:A3 I have:
> (as text)
>
> Cell Values Formula Needed
> 0100 01029250 FALSE
> 0100 01029304 FALSE
> 0100 REHAB01 TRUE
>
> I need a formula in Cells B1:B3
> to Recognize is a cells has characters A-Z
>
> I can't quite figure out here, under pressure...
>
> Can someone assist?
>

Hi Jim, (to find if a cell has any alpha character within)
Excel is rather lacking in having a TRANSLATE instruction to
change characters to other characters, so I would create a
User Defined Function (UDF).

Outstanding !!!
Tks,
Jim

"David McRitchie" wrote:

> Hi Jim, (to find if a cell has any alpha character within)
> Excel is rather lacking in having a TRANSLATE instruction to
> change characters to other characters, so I would create a
> User Defined Function (UDF).
> .
> Function Has_alpha(cell As String) As Boolean
> Dim x As String, i As Long
> For i = 1 To Len(cell)
> If UCase(Mid(cell, i, 1)) >= "A" And _
> UCase(Mid(cell, i, 1)) <= "Z" Then
> Has_alpha = True
> Exit Function
> End If
> Next i
> Has_alpha = False
> End Function
>
> To install see
> http://www.mvps.org/dmcritchie/excel/getstarted.htm
>
> to use
> =personal.xls!Has_alpha(A1)
> =Has_alpha(A1)
>
> --
> ---
> HTH,
> David McRitchie, Microsoft MVP - Excel [site changed Nov. 2001]
> My Excel Pages: http://www.mvps.org/dmcritchie/excel/excel.htm
> Search Page: http://www.mvps.org/dmcritchie/excel/search.htm
> >
> > "Jim May" wrote:
> >
> > > In cells A1:A3 I have:
> > > (as text)
> > >
> > > Cell Values Formula Needed
>
> > >
> > > I need a formula in Cells B1:B3
> > > to Recognize is a cells has characters A-Z
> > >
> > > I can't quite figure out here, under pressure...
> > >
> > > Can someone assist?
> > >
>
>
>

On Tue, 7 Mar 2006 08:52:54 -0800, Jim May <JimMay@discussions.microsoft.com>
wrote:

>In cells A1:A3 I have:
>(as text)
>
> Cell Values Formula Needed
>0100 01029250 FALSE
>0100 01029304 FALSE
> 0100 REHAB01 TRUE
>
>I need a formula in Cells B1:B3
>to Recognize is a cells has characters A-Z
>
>I can't quite figure out here, under pressure...
>
>Can someone assist?

1. Download and install Longre's free morefunc.xll add-in from
http://xcell05.free.fr

2. Use this formula:

=REGEX.COMP(A1,"[A-Z]")


--ron