Check for a combination of characters

[aurelius89]

Hi,

I need some vba to check if a cell contains the combination of NUMBER LETTER NUMBER or starts with a Letter

However, not sure where to even begin with this...

It will be part of a much much larger code, but for a simple example lets say cell A1 is the cell to check and the msgbox says "yes" or "no" if it matches

If A1 = ????? Then
Msgbox "yes"
Else
Msgbox "no"
End If

Combinations that would trigger the match:

123abc1
1a3333
45bbb111
k23
PP1
MMMM8

Combinations that would not:

12a
1111111111k
989
7

Anyone point me in the right direction?

Thanks

 

[Joe4]

Will all your entries only be numbers and letters, or might you also have spaces, punctuation, or other special characters (I am only concerned with what is showing up in the first three spaces)?

 

[Joe4]

Also, I see a discrepancy with your examples:
Combinations that would trigger the match:

123abc1
1a3333
45bbb111
k23
PP1
MMMM8

Neither of the two highlighted in red seem to meet your conditions of:
- starts with a letter
- first 3 characters are NUMBER-LETTER-NUMBER

 

[aurelius89]

Sorry I should of made it clearer - There could be any number of letters or numbers, but if there is X amount letters, followed by X amount of numbers, followed by X amount letters then that should meet the condition for "yes"

Essentially (And another way of thinking about it I guess) I am trying to verify that the cell contains X amount of numbers, POSSIBLY followed by X amount of letters.
Anything other combination is junk.

There will be no spaces or other characters. Purely letter and number.

 

[Joe4]

OK, I just need to confirm what you are really after. You first said:

NUMBER LETTER NUMBER or starts with a Letter

Then you said:

X amount letters, followed by X amount of numbers, followed by X amount letters

and finally:

X amount of numbers, POSSIBLY followed by X amount of letters.

So, your first seems to indicate, numbers followed by letters followed by numbers.
Your second seems to indicate the opposite, letters followed by number followed by letters
And finally your third seems to indicate just numbers followed by letters

So which of these three combinations is actually correct, as they are all different?

 

[aurelius89]

Like i say, X amount of numbers, POSSIBLY followed by X amount of letters is what the cell should contain. Anything else is junk.

X amount letters, followed by X amount of numbers, followed by X amount letters is a common combination that could be in the cell, but it is junk.
X amount of numbers, followed by X amount of letters, followed by X amount of numbers is another combination that is junk.
Just X amount of letters is junk.

 

[Joe4]

OK, but that is NOT what you said:

if there is X amount letters, followed by X amount of numbers, followed by X amount letters then that should meet the condition for "yes"

X amount letters, followed by X amount of numbers, followed by X amount letters is a common combination that could be in the cell, but it is junk.

So, in post 4, you said it should return "yes", but in post 6 you said it is "junk".


And originally, you said:

I need some vba to check if a cell contains the combination of NUMBER LETTER NUMBER or starts with a Letter

but in post #6 you said:

Just X amount of letters is junk.

So I am not clear if an entry starts with letters if it is junk or not.


And in post #6 , you said:

X amount of numbers, followed by X amount of letters, followed by X amount of numbers is another combination that is junk.

yet in your original post you said:

I need some vba to check if a cell contains the combination of NUMBER LETTER NUMBER

and gave "matching" example like:

123abc1
1a3333
45bbb111

So, you can see my confusion. It doesn't seem to make much sense, as you seem to keep contradicting yourself with each post.
Or is "yes" equivalent to "junk"?

 

[shg]

Just guessing ...

[Table="width:, class:grid"][tr][td="bgcolor:#C0C0C0"][/td][td="bgcolor:#C0C0C0"]

A​

[/td][td="bgcolor:#C0C0C0"]

B​

[/td][td="bgcolor:#C0C0C0"]

C​

[/td][/tr][tr][td="bgcolor:#C0C0C0"]

1​

[/td][td="bgcolor:#F3F3F3"]

Input​

[/td][td="bgcolor:#F3F3F3"]

Output​

[/td][td="bgcolor:#F3F3F3"]

​

[/td][/tr]
[tr][td="bgcolor:#C0C0C0"]

2​

[/td][td]123abc1[/td][td="bgcolor:#E5E5E5"]yes[/td][td]B2: =aurelius(A2)[/td][/tr]
[tr][td="bgcolor:#C0C0C0"]

3​

[/td][td]1a3333[/td][td="bgcolor:#E5E5E5"]yes[/td][td][/td][/tr]
[tr][td="bgcolor:#C0C0C0"]

4​

[/td][td]45bbb111[/td][td="bgcolor:#E5E5E5"]yes[/td][td][/td][/tr]
[tr][td="bgcolor:#C0C0C0"]

5​

[/td][td]k23[/td][td="bgcolor:#E5E5E5"]yes[/td][td][/td][/tr]
[tr][td="bgcolor:#C0C0C0"]

6​

[/td][td]PP1[/td][td="bgcolor:#E5E5E5"]yes[/td][td][/td][/tr]
[tr][td="bgcolor:#C0C0C0"]

7​

[/td][td]MMMM8[/td][td="bgcolor:#E5E5E5"]yes[/td][td][/td][/tr]
[tr][td="bgcolor:#C0C0C0"]

8​

[/td][td]12a[/td][td="bgcolor:#E5E5E5"]junk[/td][td][/td][/tr]
[tr][td="bgcolor:#C0C0C0"]

9​

[/td][td]1111111111k[/td][td="bgcolor:#E5E5E5"]junk[/td][td][/td][/tr]
[tr][td="bgcolor:#C0C0C0"]

10​

[/td][td]989[/td][td="bgcolor:#E5E5E5"]junk[/td][td][/td][/tr]
[tr][td="bgcolor:#C0C0C0"]

11​

[/td][td]7[/td][td="bgcolor:#E5E5E5"]junk[/td][td][/td][/tr]
[/table]

Function aurelius(sInp As String) As String
  Dim i             As Long
  Dim sPatt         As String

  For i = 1 To Len(sInp)
    Select Case Mid(sInp, i, 1)
      Case 0 To 9
        If Right(sPatt, 1) <> "n" Then sPatt = sPatt & "n"
      Case "A" To "Z", "a" To "z"
        If Right(sPatt, 1) <> "l" Then sPatt = sPatt & "l"
      Case Else
        aurelius = "junk"
        Exit Function
    End Select
  Next i

  If sPatt = "ln" Or sPatt = "nln" Then aurelius = "yes" Else aurelius = "junk"
End Function

 

[shg]

And if that works, so does this:

Function aurelius(sInp As String) As String
  With New RegExp
    .Pattern = "^\d*[A-Za-z]+\d+$"
    aurelius = IIf(.Test(sInp), "yes", "junk")
  End With
End Function