How can I validate all characters in a string are only letters or numbers

[AndyTampa]

I posted a similar question last year titled Count cells that contain 6 characters or numbers but no spaces and received many wonderful responses, but my goal has evolved and I'm not getting any more responses in that conversation. Please forgive me if this appears to be a duplicate. It isn't.

With everything I'm being asked to check in this spreadsheet, I thought a VBA option would be best. A person using this sheet could enter all their data and just click one button to validate their input. Once validated, they could click another button to create a proper sheet without the code. With all the input I received, there were two that provided the correct results but I can't get those formulas to work in VBA. I no longer need to count cells with alphanumeric strings. My macro is running down each row and checking several items before moving on to the next row.

I am trying to check the cells to see that they are in the right format. That format is 6 characters of only numbers 0-9 or capital letters A-Z with not spaces or other characters. My macro capitalizes the cell before testing it. But if it fails the test, I want to highlight the cell and increment an error counter.

In that thread, on post #19, jtakw provided this Data Validation formula:

AND(LEN(A1)=6,ISNUMBER(SUMPRODUCT(FIND(MID(A1,ROW(INDIRECT("1:"&LEN(A1))),1),"0123456789ABCDEFGHIJKLMNOPQRSTUVWXYZ"))))

When I tried to use this in an IF statement, VBA balked at the INDIRECT function.

On post #39, Rick Rothstein provided this Data Validation formula:

IF(LEN(A1)=6,(SUMPRODUCT((ABS(CODE(MID(LEFT(A1,6),ROW($1:$6),1))-69)<=21)*(ABS(CODE(MID(LEFT(A1,6),ROW($1:$6),1))-61)>3))=6))

When I tried to use this in the same IF statement, VBA balked at the dollar signs, so I removed them and it balked at the colons. I changed the colons to commas and VBA balked at the ROW function.

This is what I have in a For/Next loop (I've commented them out because they both failed):

'    If Cell.Value = Len("A" & RNum) = 6 And Cell.Value = IsNumber(SumProduct(Find(Mid("A" & RNum, Row(INDIRECT("1:" & Len("A" & RNum))), 1), "0123456789ABCDEFGHIJKLMNOPQRSTUVWXYZ"))) Then

'    If Cell.Value = Len("A" & RNum) = 6 And Cell.Value = SumProduct((Abs(CODE(Mid(Left("A" & RNum, 6), Row($1:$6), 1)) - 69) <= 21) * (Abs(CODE(Mid(Left("A" & RNum, 6), Row($1:$6), 1)) - 61) > 3)) = 6 Then

        End If

    Else
        Cells(RNum, 1).Interior.Color = RGB(0, 0, 125)
        CNumErr = CNumErr + 1
        Range("J3").Value = CNumErr & " Invalid Case Numbers"
    End If

I liked these formulas for DV, because they correctly identified a blank cell as being an invalid case number. Now, it would be really useful if there was already an Excel function that can do this.

 

[Micron]

What I think you need to do is research Regex (Regular Expressions). Seems to me it's custom made for your question. However, I can't help you with that because I find it too daunting to learn at my age. Likely you'll be able to find examples of expressions that will do exactly what you want. I'd say this one is the most common request.

 

[Akuini]

That format is 6 characters of only numbers 0-9 or capital letters A-Z with not spaces or other characters

So there are valid, right?
122456
FSDFGH
23E5T6

Try:

If Cell.Value Like WorksheetFunction.Rept("[0-9A-Z]", 6) Then

 

[AndyTampa]

Micron, I've seen someone mention that before, but when I looked into it, it seemed that you had to enable something in what appeared to be add-ins. I can't ask everyone using this spreadsheet to do that. I'd spend my day telling people how once I figure it out.

Akuini, I like that it's simple, but I don't know how I'd use it. In my examples, the cell I'm looking at is in the formula. Your example doesn't pick a cell or even a variable. How would I use it? I've never seen a "WorksheetFunction", so I don't know how that works (yet).

 

[RoryA]

If Cells(RNum, 1).Value = "" or Cells(RNum, 1).value like "*[!A-Za-z0-9]*" then
' it's invalid so do whatever you need to
End If

would be one way.

 

[Peter_SSs]

To validate values in column A, try something like this

Sub Validate_Entries()
  Dim Cell As Range
  Dim CNumErr As Long
  
  For Each Cell In Range("A1", Range("A" & Rows.Count).End(xlUp))
    If Not Cell.Value Like Replace(Space(6), " ", "[A-Z0-9]") Then
      Cell.Interior.Color = RGB(0, 0, 125)
      CNumErr = CNumErr + 1
    End If
  Next Cell
  Range("J3").Value = CNumErr & " Invalid Case Numbers"
End Sub

 

[Micron]

it seemed that you had to enable something in what appeared to be add-ins. I can't ask everyone using this spreadsheet to do that. I'd spend my day telling people how once I figure it out.

I believe you're referring to references that are set in the vb editor. There is only one workbook that is shared, yes? References belong to the vb project, not the user. I believe the one required for this is Microsoft VBScript Regular Expressions - or something like that.

 

[AndyTampa]

My apologies to everyone. I wrote this post at 8:00 this morning then got involved with work. I guess I forgot to post the message.
===================================

I played around with Akuini's statement and this appears to be working and it's small enough to run on two columns:

    If Range("A" & RNum) Like WorksheetFunction.Rept("[0-9A-Z]", 6) Then
        GoTo CaseVal
    Else
        Cells(RNum, 1).Interior.ColorIndex = 22
        InvEnt = InvEnt + 1
        Range("J4").Value = InvEnt & " Invalid Data"
    End If
CaseVal:

    If Range("G" & RNum) Like WorksheetFunction.Rept("[0-9A-Z]", 6) Then
        GoTo CaseVal2
    Else
        Cells(RNum, 7).Interior.ColorIndex = 22
        InvEnt = InvEnt + 1
        Range("J4").Value = InvEnt & " Invalid Data"
    End If
CaseVal2:

More testing is necessary

 

[AndyTampa]

I do appreciate the other responses. I noticed something in Peter_SSs response. Can I use the NOT in my code so I don't have to jump to CaseVal and CaseVal2?

I think I still need to make it two separate checks instead of combining the ranges since I want an error for each one.

 

[Micron]

GoTo's to control program flow are usually considered to be bad practice.
I was going to re-write that as a suggestion but something just came up and I've go to bug out for a while. Will see if anyone beats me to it.