Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
cell validation/format masks???
Hi Everybody
I have a list of cells with alphanumeric data in it eg ("01R1234567") I want to write an if statement to check that the contents of the cells is a valid reference. Is there any way to check the individual characters for there values eg IF A1 = "NNANNNNNNN" Then B1 = "valid" (N=numberic A=Aplph) else B1 = "corrupt" End If Thanks Anthony |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
cell validation/format masks???
Hi Anthony,
I don't think there is any direct way. However you can use the worksheet function bellow (HasPattern(,)) which uses the LIKE operator (see vba online help) '------------------------------------------------------------------------------- 'HasPattern returns the value of the expression: Str LIKE Pattern 'It returns #Value error if either STr or Pattern cannot be converted into strings. Public Function HasPattern(Str As Variant, Pattern As String) As Variant Dim s1 As String, s2 As String On Error Resume Next s1 = CStr(Str) s2 = CStr(Pattern) If Err < 0 Then 'if error, returns #Value HasPattern = CVErr(xlErrValue) Else HasPattern = (Str Like Pattern) End If End Function '----------------------------------------------------------------- In the sheet, in your case, in B1, enter: =HasPattern(UPPER(A1),"##[A-Z]#######") will retun True if A1 is like "NNANNNNNNN" else False You can also use it in : -Conditional Formatting (Format of the cell varies based on a condition: menu Format Conditional Formatting) or - Dat Validation: validates user data entry (menu Data Validation) - directly within vba I hope this helps, Sebastien "antinoz" wrote: Hi Everybody I have a list of cells with alphanumeric data in it eg ("01R1234567") I want to write an if statement to check that the contents of the cells is a valid reference. Is there any way to check the individual characters for there values eg IF A1 = "NNANNNNNNN" Then B1 = "valid" (N=numberic A=Aplph) else B1 = "corrupt" End If Thanks Anthony |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
cell validation/format masks???
Didn't you see the several fine responses to your post of 9/8/2004 9:53 PM
?? "antinoz" wrote in message ... Hi Everybody I have a list of cells with alphanumeric data in it eg ("01R1234567") I want to write an if statement to check that the contents of the cells is a valid reference. Is there any way to check the individual characters for there values eg IF A1 = "NNANNNNNNN" Then B1 = "valid" (N=numberic A=Aplph) else B1 = "corrupt" End If Thanks Anthony |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
cell validation/format masks???
A big thank you to all the guys who responded to the previous "duplication".
I foolishly lost the question and could not find it again so thinking I had not infact posted one I did it again. apologies and thanks thanks also sebastienm for responding along with, Bob, Don & Dave cheers guys "Bob Kilmer" wrote: Didn't you see the several fine responses to your post of 9/8/2004 9:53 PM ?? "antinoz" wrote in message ... Hi Everybody I have a list of cells with alphanumeric data in it eg ("01R1234567") I want to write an if statement to check that the contents of the cells is a valid reference. Is there any way to check the individual characters for there values eg IF A1 = "NNANNNNNNN" Then B1 = "valid" (N=numberic A=Aplph) else B1 = "corrupt" End If Thanks Anthony |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Format Cell and Data Validation | Excel Discussion (Misc queries) | |||
Cell validation = format? | Excel Discussion (Misc queries) | |||
input masks and validation | Excel Discussion (Misc queries) | |||
Boolean masks | Excel Worksheet Functions | |||
Lists and masks | Excel Programming |