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?

Formula to recognize text only in a cell
 

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?

Formula to recognize text only in a cell
 

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?

Formula to recognize text only in a cell
 

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).

Formula to recognize text only in a cell
 

On Tue, 7 Mar 2006 08:52:54 -0800, 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?

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

Formula to recognize text only in a cell
 

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?

Formula to recognize text only in a cell
 

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?

Formula to recognize text only in a cell
 

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" wrote in message ...
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?

Formula to recognize text only in a cell
 

On Tue, 7 Mar 2006 09:04:24 -0800, 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?


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

Formula to recognize text only in a cell
 

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.

Formula to recognize text only in a cell
 

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" wrote in message
...
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.

Formula to recognize text only in a cell
 

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

"Jim May" 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" wrote in message
...
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.