Formula to recognize text only in a cell

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

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?
 
One way.........

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

Vaya con Dios,
Chuck, CABGx3
 
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).
 
Test recognition

Try using " =istext(cellbeingchecked) This will either bring back True if it is..or false if it is number....if you need it to say something try imbedding it in an "If" statement.

Sample: =if(istext(cell),"Text","Number"))
 
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?
Click to expand...

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
 
Outstanding !!!
Tks,
Jim

David McRitchie said:
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)
Click to expand...
 
Maybe

=NOT(ISNUMBER(1*SUBSTITUTE(A1," ","")))
Click to expand...

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...
 
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
 
and 1* to convert a string with digits to a number or an error.
Somehow I was convinced it was doing more.
 
Back
Top