If cell contains AlphaNumeric "True"
 

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

If cell contains AlphaNumeric "True"
 

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


"Carol" wrote:

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

If cell contains AlphaNumeric "True"
 

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

"Carol" wrote:

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

If cell contains AlphaNumeric "True"
 

the op stated emphatically......
...I do NOT know VBA.... Just thought you should know that!

sigh.
regards
FSt1

"Jacob Skaria" wrote:

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


"Carol" wrote:

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

If cell contains AlphaNumeric "True"
 

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"...
--
Carol


"FSt1" wrote:

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

"Carol" wrote:

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

If cell contains AlphaNumeric "True"
 

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,SUMPRODUC T(--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 (MVP - Excel)


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

If cell contains AlphaNumeric "True"
 

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.

--
Rick (MVP - Excel)


"Rick Rothstein" wrote in message
...
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,SUMPRODUC T(--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 (MVP - Excel)


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

If cell contains AlphaNumeric "True"
 

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


"Rick Rothstein" wrote:

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,SUMPRODUC T(--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 (MVP - Excel)


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