software

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,quot; quot;,quot;quot;)))
quot;Jim Mayquot; wrote:

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

One way.........

=IF(ISERR(MID(A1,6,1)*1),quot;truequot;,quot;falsequot;)

Vaya con Dios,
Chuck, CABGx3quot;Jim Mayquot; wrote:

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

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

On Tue, 7 Mar 2006 08:52:54 -0800, Jim May gt;
wrote:

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

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

2. Use this formula:

=REGEX.COMP(A1,quot;[A-Z]quot;)--ron

Outstanding !!!
Tks,
Jim

quot;David McRitchiequot; wrote:

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

Brilliant!!
Tks,
Jim

quot;Duke Careyquot; wrote:

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

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: www.mvps.org/dmcritchie/excel/excel.htm
Search Page: www.mvps.org/dmcritchie/excel/search.htm

quot;Jim Mayquot; gt; wrote in message ...
gt; Brilliant!!
gt; Tks,
gt; Jim
gt;
gt; quot;Duke Careyquot; wrote:
gt;
gt; gt; Maybe
gt; gt;
gt; gt; =NOT(ISNUMBER(1*SUBSTITUTE(A1,quot; quot;,quot;quot;)))
gt; gt;
gt; gt;
gt; gt;
gt; gt; quot;Jim Mayquot; wrote:
gt; gt;
gt; gt; gt; In cells A1:A3 I have:
gt; gt; gt; (as text)
gt; gt; gt;
gt; gt; gt; Cell Values Formula Needed
gt; gt; gt; 0100 01029250 FALSE
gt; gt; gt; 0100 01029304 FALSE
gt; gt; gt; 0100 REHAB01 TRUE
gt; gt; gt;
gt; gt; gt; I need a formula in Cells B1:B3
gt; gt; gt; to Recognize is a cells has characters A-Z
gt; gt; gt;
gt; gt; gt; I can't quite figure out here, under pressure...
gt; gt; gt;
gt; gt; gt; Can someone assist?
gt; gt; gt;
On Tue, 7 Mar 2006 09:04:24 -0800, Duke Carey
gt; wrote:

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

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

He asked for a function that would quot;Recognize is a cells has characters A-Zquot;

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,quot; quot;,quot;quot;)))Ron Rosenfeld, wrote...
gt; Although it may be what he wanted, it's not quite what he asked for.
gt; He asked for a function that would quot;Recognize is a cells has characters A-Zquot;
gt; Your function will give a TRUE result for many other non-numeric characters
gt; than the set A-Z.