Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#7
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#8
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
change "true" and "false" to "availble" and "out of stock" | Excel Worksheet Functions | |||
Typing "true" excel 2007 change it to "TRUE" | Excel Discussion (Misc queries) | |||
Count occurences of "1"/"0" (or"TRUE"/"FALSE") in a row w. conditions in the next | New Users to Excel | |||
IF(VLOOKUP("MYDATA", MYNAME, 4) = 0, "TRUE", "FALSE") | Excel Programming | |||
set "value if true" to "fill cell with color" | Excel Programming |