alpha numeric check in a cell

[rhombus4]

</p>Hi</p></p>Need to check a cell and make sure the correct characters are entered. It needs to be in the correct order and alpha/numeric. </p></p>A - Alpha, N-Numeric </p></p>It should have 15 characters and be ANNAANNAANNANN and the last character will be a number or X </p>

 

[Rick Rothstein]

If you are talking about testing that pattern in VB code, you could do use something like this...

Dim A As String, YourCellValue As String
A = "[A-Za-z]"
YourCellValue = Range("A1").Value
If YourCellValue Like A & "##" & A & A & "##" & A & A & "##" & A & "##" & "[0-9Xx]" Then
   ' Pattern matches
Else
   ' Pattern does not match
End If

 

[rhombus4]

If you are talking about testing that pattern in VB code, you could do use something like this...

Dim A As String, YourCellValue As String
A = "[A-Za-z]"
YourCellValue = Range("A1").Value
If YourCellValue Like A & "##" & A & A & "##" & A & A & "##" & A & "##" & "[0-9Xx]" Then
   ' Pattern matches
Else
   ' Pattern does not match
End If

</p>Not sure how to use the above. Was hoping to use a formula, the data is entered in cell A1 and I wanted an answer in cell B1, either True or False or maybe enter a MAcro which put a tick or cross in the Box </p>

 

[rhombus4]

If you are talking about testing that pattern in VB code, you could do use something like this...

Dim A As String, YourCellValue As String
A = "[A-Za-z]"
YourCellValue = Range("A1").Value
If YourCellValue Like A & "##" & A & A & "##" & A & A & "##" & A & "##" & "[0-9Xx]" Then
   ' Pattern matches
Else
   ' Pattern does not match
End If

another reason why a formulas may be better is, What would happen if I pasted alot of data into column A and wanted to check whether all the entries were correct in column B. i.e. to see which ones were entered correctly or incorrectly

 

[rhombus4]

If you are talking about testing that pattern in VB code, you could do use something like this...

Dim A As String, YourCellValue As String
A = "[A-Za-z]"
YourCellValue = Range("A1").Value
If YourCellValue Like A & "##" & A & A & "##" & A & A & "##" & A & "##" & "[0-9Xx]" Then
   ' Pattern matches
Else
   ' Pattern does not match
End If

Does anyone know if I just paste the above code into Visual Basic Editor under Sheet1??????

 

[rhombus4]

If you are talking about testing that pattern in VB code, you could do use something like this...

Dim A As String, YourCellValue As String
A = "[A-Za-z]"
YourCellValue = Range("A1").Value
If YourCellValue Like A & "##" & A & A & "##" & A & A & "##" & A & "##" & "[0-9Xx]" Then
   ' Pattern matches
Else
   ' Pattern does not match
End If

Copied the above into Vis Basic Editor in a new module, not sure what to do next, entered data in CEll A1 but nothing happens???????

 

[Rick Rothstein]

Sorry for looking like I had abandoned you, but I had gone to sleep for the night, but I am back now. Okay, since you have now told us how you want to use the test, I am going to turn the code I posted into a UDF (user defined function). Once set up, you will be able to use it in the workbook just like any of the built-in Excel functions. First, the code...

Function TestPattern(S As String) As Boolean
  Dim A As String, YourCellValue As String
  A = "[A-Za-z]"
  TestPattern = S Like A & "##" & A & A & "##" & A & A & "##" & A & "##" & "[0-9Xx]"
End Function

To install this code, press ALT+F11 to go into the VB editor and, once there, click Insert/Module on its menu bar, then copy/paste the above code into the code window that opened up. That's it, you are done. Now go back to your worksheet and put this formula in cell B1...

=TestPattern(A1)

and it will report TRUE or FALSE for the pattern in the referenced cell. This formula can be copied down or across as needed. Also, this function can be embedded within other Excel function calls if need be; for example...

=IF(A2="","",IF(TestPattern(A2),"Matched Pattern","Mismatched Pattern"))

 

[rhombus4]

Sorry for looking like I had abandoned you, but I had gone to sleep for the night, but I am back now. Okay, since you have now told us how you want to use the test, I am going to turn the code I posted into a UDF (user defined function). Once set up, you will be able to use it in the workbook just like any of the built-in Excel functions. First, the code...

Function TestPattern(S As String) As Boolean
  Dim A As String, YourCellValue As String
  A = "[A-Za-z]"
  TestPattern = S Like A & "##" & A & A & "##" & A & A & "##" & A & "##" & "[0-9Xx]"
End Function

To install this code, press ALT+F11 to go into the VB editor and, once there, click Insert/Module on its menu bar, then copy/paste the above code into the code window that opened up. That's it, you are done. Now go back to your worksheet and put this formula in cell B1...

=TestPattern(A1)

and it will report TRUE or FALSE for the pattern in the referenced cell. This formula can be copied down or across as needed. Also, this function can be embedded within other Excel function calls if need be; for example...

=IF(A2="","",IF(TestPattern(A2),"Matched Pattern","Mismatched Pattern"))

Thanks RickIf one of the alpha characters needed to be a specific letter would it be just like the last part of the code. e.g. if the first letter needed to be a X, Y or Z would it just be </p></p>TestPattern = S Like [XxYyZz] & "##" & "##" & A & A & "##" & A & A & "##" & A & "##" & "[0-9Xx]"End Function</p>

 

[Rick Rothstein]

Yes, that is the idea, but you forgot the quote marks around the it...

TestPattern = S Like "[XxYyZz]" & "##" & "##" & A & A & "##" & A & A & "##" & A & "##" & "[0-9Xx]"

However, you need to realize that everything after the "Like" keyword is a text String, so there is no need to keep the constant (non-variable) parts separated from each other...

TestPattern = S Like "[XxYyZz]####" & A & A & "##" & A & A & "##" & A & "##" & "[0-9Xx]"

The only reason there are concatenations in the pattern String is to make it simpler to read. We can make this one long text String without any concatenations by replacing the "A" variables with what I assigned to them, namely, "[A-Za-z]"...

TestPattern = S Like "[XxYyZz]####[A-Za-z][A-Za-z]##[A-Za-z][A-Za-z]##[A-Za-z]##[0-9Xx]"

Trying to construct it this way (and read it back later) makes it harder to follow what is going on in the pattern than using the concatenations (in my opinion).

 

[rhombus4]

Yes, that is the idea, but you forgot the quote marks around the it...

TestPattern = S Like "[XxYyZz]" & "##" & "##" & A & A & "##" & A & A & "##" & A & "##" & "[0-9Xx]"

However, you need to realize that everything after the "Like" keyword is a text String, so there is no need to keep the constant (non-variable) parts separated from each other...

TestPattern = S Like "[XxYyZz]####" & A & A & "##" & A & A & "##" & A & "##" & "[0-9Xx]"

The only reason there are concatenations in the pattern String is to make it simpler to read. We can make this one long text String without any concatenations by replacing the "A" variables with what I assigned to them, namely, "[A-Za-z]"...

TestPattern = S Like "[XxYyZz]####[A-Za-z][A-Za-z]##[A-Za-z][A-Za-z]##[A-Za-z]##[0-9Xx]"

Trying to construct it this way (and read it back later) makes it harder to follow what is going on in the pattern than using the concatenations (in my opinion).

Thanks. Once again!