If cell contains AlphaNumeric "True"

[Carol]

I have a column where cells will contain one of the following:
" " (blank)
A9999 (A=Alpha/9=Numeric)
A (A=Alpha)

The alpha and numeric characters will vary from cell to cell.

I need a formula that will enter TRUE (or some kind of indicator) if the
cell contains alpha/numeric characters. Is this possible? Thank you!

....I do NOT know VBA.... Just thought you should know that!

 

[Jacob Skaria]

If you are looking for a user defined function please find the below. Launch
VBE using Alt+F11. Insert module. Paste the below function and try as below

A1 = a123
B1 = IsAlphaNumeric(A1)

Function IsAlphaNumeric(varTemp) As Boolean
If varTemp Like "*#*" And UCase(varTemp) Like "*[A-Z]*" _
Then IsAlphaNumeric = True
End Function

If this post helps click Yes

 

[FSt1]

hi carol,
not sure if i understand completely but..
try this...
=IF(B2<>"","True","False") 'adjust to suit
if not blank(alpha/numeric or both) then true else if blank then false.


is that what you have in mind????
or did i misunderstand???

regards
FSt1

 

[FSt1]

the op stated emphatically......
sigh.
regards
FSt1

If you are looking for a user defined function please find the below. Launch
VBE using Alt+F11. Insert module. Paste the below function and try as below

A1 = a123
B1 = IsAlphaNumeric(A1)

Function IsAlphaNumeric(varTemp) As Boolean
If varTemp Like "*#*" And UCase(varTemp) Like "*[A-Z]*" _
Then IsAlphaNumeric = True
End Function

If this post helps click Yes
---------------
Jacob Skaria

I have a column where cells will contain one of the following:
" " (blank)
A9999 (A=Alpha/9=Numeric)
A (A=Alpha)

The alpha and numeric characters will vary from cell to cell.

I need a formula that will enter TRUE (or some kind of indicator) if the
cell contains alpha/numeric characters. Is this possible? Thank you!

...I do NOT know VBA.... Just thought you should know that!

 

[Carol]

Thanks FSt1 -

I'm not sure that is what I'm looking for....and forgive me here - my
programming skills are "beginner novice"...and that's on a good day. It
looks like "B2<>"" - will look for blank. But, how do I find cells that
contain both Alpha and Numeric? It seems everything I try returns TRUE for
the Alpha - even when there is only alpha in the cell. I want the
alpha/numeric cells to be "true" - and everything else "false"...

 

[Rick Rothstein]

While I'm sure there is probably a shorter formula that will do what you
want, give this a try in the meantime...

=AND(SUMPRODUCT(--ISNUMBER(SEARCH(MID(A1,ROW(INDIRECT("A$1:A"&LEN(A1))),1),"abcdefghijklmnopqrstuvwxyz")))>0,SUMPRODUCT(--ISNUMBER(--MID(A1,ROW($1:$999),1)))>0)

This formula returns TRUE for entries with both numbers and letters (but not
non-letters, non-digits, such as punctuation) in them and FALSE otherwise.

 

[Rick Rothstein]

Actually, assuming the input you showed us, I think this shorter formula
will work...

=AND(SUMPRODUCT(--ISNUMBER(--MID(A1,ROW($1:$999),1)))<LEN(A1),MIN(FIND({0,1,2,3,4,5,6,7,8,9},A1&"0123456789"))<=LEN(A1))

This formula differs from my last one in that **any** non-digit qualifies as
an "alpha" character, which means that punctuation is considered an alpha
character. Given that, a floating point number (such as 123.45) would
register as an alpha-numeric entry; however, your list of possible entries
does not show floating point numbers as an entry type, so the formula should
work for you.

 

[Carol]

You are amazing - I only wish I was capable of THAT kind of excel wizardry.
This works perfectly - thank you so very much!