Excel Function or VBA to validate each character in cell to match various input masks

G

GREGO99

New Member
Joined
Mar 28, 2022
Messages
4
Office Version
  1. 365
Platform
  1. Windows
for example >LLLL-99-99-99-LLL9999-LL## where L is ALPHA, 9 numeric, # can be either.

example of cell passing this mask; "ABCD-10-20-45-ABC1234-AB00"

a function would be best the have a column to flag the data column good/bad and perhaps where "bad"

thanks for any guidance,

Greg

SAMPLE.xlsx
AB
1DATAGOOD/BAD
2ABCD-10-20-45-ABC1234-AB00G
3ABCD-10-20-45-ABC1234-AB01G
4ABCD-10-20-45-ABC12B4-AB02B
5ABCD-10-20-45-ABC1234-A123B
6ABCD-10-20-45-ABC1234-AB04G
7ABCD-10-20-45-ABC1234-AB05G
8ABCD-10-20-45-ABC1234-1234B
9ABCD-10-20-45-ABC1234-AB07G
Sheet1


1648504405856.png
 

Excel Facts

How to calculate loan payments in Excel?
Use the PMT function: =PMT(5%/12,60,-25000) is for a $25,000 loan, 5% annual interest, 60 month loan.
Sort by date Sort by votes
Blank_2_Years (25).xlsx
ABC
1DATAGOOD/BADREGEX
2ABCD-10-20-45-ABC1234-AB00GG
3ABCD-10-20-45-ABC1234-AB01GG
4ABCD-10-20-45-ABC12B4-AB02BB
5ABCD-10-20-45-ABC1234-A123BB
6ABCD-10-20-45-ABC1234-AB04GG
7ABCD-10-20-45-ABC1234-AB05GG
8ABCD-10-20-45-ABC1234-1234BB
9ABCD-10-20-45-ABC1234-AB07GG
Sheet3
Cell Formulas
RangeFormula
C2:C9C2=ISVALID(A2)


VBA Code: 
Function ISVALID(s As String)
With CreateObject("VBScript.RegExp")
    .Global = True
    .Pattern = "\D{4}(?:-\d{2}){3}-\D{3}\d{4}-\D{2}\w{2}"
    ISVALID = IIf(.test(s), "G", "B")
End With
End Function
 
Last edited:
Upvote 0
Here is a direct Excel formula that will also work...
Excel Formula: 
=IF(AND(LEN(A2)=26,AND(MID(A2,{5,8,11,14,22},1)="-"),IF(MID("AAAA-00-00-00-AAA0000-AA00",{1,2,3,4,6,7,9,10,12,13,15,16,17,18,19,20,21,23,24,25,26},1)="A",(MID(A2,{1,2,3,4,6,7,9,10,12,13,15,16,17,18,19,20,21,23,24,25,26},1)>="A")*(MID(A2,{1,2,3,4,6,7,9,10,12,13,15,16,17,18,19,20,21,23,24,25,26},1)<="Z"),ISNUMBER(-MID(A2,{1,2,3,4,6,7,9,10,12,13,15,16,17,18,19,20,21,23,24,25,26},1)))),"G","B")
 
Upvote 0
lrobbo314 said:
Blank_2_Years (25).xlsx
ABC
1DATAGOOD/BADREGEX
2ABCD-10-20-45-ABC1234-AB00GG
3ABCD-10-20-45-ABC1234-AB01GG
4ABCD-10-20-45-ABC12B4-AB02BB
5ABCD-10-20-45-ABC1234-A123BB
6ABCD-10-20-45-ABC1234-AB04GG
7ABCD-10-20-45-ABC1234-AB05GG
8ABCD-10-20-45-ABC1234-1234BB
9ABCD-10-20-45-ABC1234-AB07GG
Sheet3
Cell Formulas
RangeFormula
C2:C9C2=ISVALID(A2)


VBA Code: 
Function ISVALID(s As String)
With CreateObject("VBScript.RegExp")
    .Global = True
    .Pattern = "\D{4}(?:-\d{2}){3}-\D{3}\d{4}-\D{2}\w{2}"
    ISVALID = IIf(.test(s), "G", "B")
End With
End Function
Click to expand...
Thank-you, that is elegant and works perfectly on sample data I provided. However, I realized it wont work if I have "shorter" valid data; (also I have questions on the code, and assumptions)

SAMPLE.xlsm
ABCDEFGHIJKLM
1DATAGOOD/BADTESTPattern = "\D{4}(?:-\d{2}){3}-\D{3}\d{4}-\D{2}\w{2}"Function ISVALID(s As String)
2ABCD-10-20-45-ABC1234-AB00GGWith CreateObject("VBScript.RegExp")
3ABCD-10-20-45-ABC1234-AB01GGD = alpha4 1st chars .Global = True
4ABCD-10-20-45-ABC12B4-AB02BB?:means what? .Pattern = "\D{4}(?:-\d{2}){3}-\D{3}\d{4}-\D{2}\w{2}"
5ABCD-10-20-45-ABC1234-A123BB-means must be dash ISVALID = IIf(.test(s), "G", "B")
6ABCD-10-20-45-ABC1234-AB04GG\d= numericdash then next 2 chars, repeat 3 timesEnd With
7ABCD-10-20-45-ABC1234-AB05GG-means must be dashEnd Function
8ABCD-10-20-45-ABC1234-1234BB\D = alphanext 3 chars
9ABCD-10-20-45-ABC1234-AB07GG\dnext 4 are numeric
10ABCD-10-20-45-ABC1234GB-means must be dash
11ABCD-10GB\D = alphanext 2 alpha
12\w= alpha or numeric ?
13
14HOW TO MAKE IT ALLOW SHORTER DATA ?
Sheet1
Cell Formulas
RangeFormula
C2:C11C2=ISVALID(A2)
 
Upvote 0
Give this UDF a try...
VBA Code: 
Function IsValid(S As String) As String
  Dim Pattern As String
  Pattern = Replace(Replace(Left("LLLL-##-##-##-LLL####-LLAA", Len(S)), "A", "[0-9A-Z]"), "L", "[A-Z]")
  IsValid = Mid("BG", 1 + (S Like Pattern) * (Len(S) > 6), 1)
End Function
Note: This function assumes the minimum length of an entry must be 7 characters (4 letters, 1 dash, 2 digits).
 
Upvote 0
Solution
Rick Rothstein said:
Give this UDF a try...
VBA Code: 
Function IsValid(S As String) As String
  Dim Pattern As String
  Pattern = Replace(Replace(Left("LLLL-##-##-##-LLL####-LLAA", Len(S)), "A", "[0-9A-Z]"), "L", "[A-Z]")
  IsValid = Mid("BG", 1 + (S Like Pattern) * (Len(S) > 6), 1)
End Function
Note: This function assumes the minimum length of an entry must be 7 characters (4 letters, 1 dash, 2 digits).
Click to expand...
Very cool solution, thanks! I think I can figure the logic to apply to other patterns.
 
Upvote 0
Inspired by Rick's worksheet formula attempt at the original question, I thought I would try for one here.
Also, since I'm keen to practice regular expressions I have suggested another udf.
Both suggestions I think should work for any shortening of the original pattern.

VBA Code: 
Function GB(S As String) As String
  Dim RX As Object
  
  Const Pat As String = "LLLLHDDHDDHDDHLLLDDDDHLLXX" 'L=Letter, H = Hyphen, D = Digit, X = Letter or Digit
  Set RX = CreateObject("VBScript.RegExp")
  RX.Pattern = "^" & Replace(Replace(Replace(Replace(Left(Pat, Len(S)), "L", "[A-Z]"), "H", "\-"), "D", "\d"), "X", "[A-Z\d]") & "$"
  If Len(S) > 0 Then GB = Mid("BG", 1 - RX.Test(S), 1)
End Function

GREGO99.xlsm
ACD
1DATATESTTEST
2ABCD-10-20-45-ABC1234-AB00GG
3ABCD-10-20-45-ABC1234-AB01GG
4ABCD-10-20-45-ABC12B4-AB02BB
5ABCD-10-20-45-ABC1234-A123BB
6ABCD-10-20-45-ABC1234-AB04GG
7ABCD-10-20-45-ABC1234-AB05GG
8ABCD-10-20-45-ABC1234-1234BB
9ABCD-10-20-45-ABC1234-AB07GG
10ABCD-10-20-45-ABC1234GG
11ABCD-10GG
12  
13AGG
14WXYZ-GG
158BB
16ABCD-10-20-45-ABC1234-ABXXGG
Sheet1
Cell Formulas
RangeFormula
C2:C16C2=LET(s,A2,D,CONCAT(MID(s,{5,8,11,14,22},1)),L,MID(s,1,4)&MID(s,15,3)&MID(s,23,2),N,CONCAT(MID(s,{6,9,12,18,20},2)),y,MID(s,25,1),z,MID(s,26,1),aa,SUBSTITUTE(D,"-","")="",bb,ABS(77.5-CODE(MID(L,SEQUENCE(LEN(L)),1)))<13,cc,IF(LEN(N)=0,TRUE,ISNUMBER(-MID(N,SEQUENCE(LEN(N)),1))),dd,AND(OR(ISNUMBER(y+0),ABS(77.7-CODE(y&"A"))<13),OR(ISNUMBER(z+0),ABS(77.7-CODE(z&"A"))<13)),IF(LEN(s)=0,"",IF(AND(aa,bb,cc,dd),"G","B")))
D2:D16D2=GB(A2)
 
Upvote 0
Peter_SSs said:
Inspired by Rick's worksheet formula attempt at the original question, I thought I would try for one here.
Also, since I'm keen to practice regular expressions I have suggested another udf.
Both suggestions I think should work for any shortening of the original pattern.

VBA Code: 
Function GB(S As String) As String
  Dim RX As Object
 
  Const Pat As String = "LLLLHDDHDDHDDHLLLDDDDHLLXX" 'L=Letter, H = Hyphen, D = Digit, X = Letter or Digit
  Set RX = CreateObject("VBScript.RegExp")
  RX.Pattern = "^" & Replace(Replace(Replace(Replace(Left(Pat, Len(S)), "L", "[A-Z]"), "H", "\-"), "D", "\d"), "X", "[A-Z\d]") & "$"
  If Len(S) > 0 Then GB = Mid("BG", 1 - RX.Test(S), 1)
End Function

GREGO99.xlsm
ACD
1DATATESTTEST
2ABCD-10-20-45-ABC1234-AB00GG
3ABCD-10-20-45-ABC1234-AB01GG
4ABCD-10-20-45-ABC12B4-AB02BB
5ABCD-10-20-45-ABC1234-A123BB
6ABCD-10-20-45-ABC1234-AB04GG
7ABCD-10-20-45-ABC1234-AB05GG
8ABCD-10-20-45-ABC1234-1234BB
9ABCD-10-20-45-ABC1234-AB07GG
10ABCD-10-20-45-ABC1234GG
11ABCD-10GG
12  
13AGG
14WXYZ-GG
158BB
16ABCD-10-20-45-ABC1234-ABXXGG
Sheet1
Cell Formulas
RangeFormula
C2:C16C2=LET(s,A2,D,CONCAT(MID(s,{5,8,11,14,22},1)),L,MID(s,1,4)&MID(s,15,3)&MID(s,23,2),N,CONCAT(MID(s,{6,9,12,18,20},2)),y,MID(s,25,1),z,MID(s,26,1),aa,SUBSTITUTE(D,"-","")="",bb,ABS(77.5-CODE(MID(L,SEQUENCE(LEN(L)),1)))<13,cc,IF(LEN(N)=0,TRUE,ISNUMBER(-MID(N,SEQUENCE(LEN(N)),1))),dd,AND(OR(ISNUMBER(y+0),ABS(77.7-CODE(y&"A"))<13),OR(ISNUMBER(z+0),ABS(77.7-CODE(z&"A"))<13)),IF(LEN(s)=0,"",IF(AND(aa,bb,cc,dd),"G","B")))
D2:D16D2=GB(A2)
Click to expand...
Thanks! a great solution!
 
Upvote 0
You must log in or register to reply here.

Similar threads

L
  • Question Question
VBA works on work PC but not home PC
Replies
3
Views
2K
rollis13
rollis13
Jaafar Tribak
  • Question Question
Cool Customized standard InputBox to allow validating input as you type in.
2
Replies
17
Views
4K
KeepTrying
K
S
  • Question Question
VB Script to Excel cells using VBA
2 3
Replies
22
Views
8K
sanits591
S

Forum statistics

Threads
1,223,886
Messages
6,175,194
Members
452,616
Latest member
intern444

Share this page

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top