Solved: Modifying regex to accept comma's?

[Simon Lloyd]

Hi all i'm using regex (provided for me by some one else because i don't have the foggiest with it) to force an entry of letters (at least for the first two, rather than all numeric, however i want the user to be able to enter the data in this fashion xxxxxx, xxxxxx where x will be a character, however, using this ^[a-z]{2}[a-z ]*$ does not allow the comma, can someone help modify it to accept it?

[Aussiebear]

Psst!!!

In the unlikely event you didn't get your answer here try The Code Cage!

[Simon Lloyd]

Ted, now why didn't i think of that?
Now also posted here: http://www.thecodecage.com/forumz/ex...tml#post395094

[GTO]

Hi Simon,

This could be off, but I think may be okay (how's that for confidence?). Interested but haven't used RegExp, so was giving it a spin since you posted.

Anyways, I see that you came up with something at your link. I'm not sure if important to what you are currently doing, but leastwise in this testing - your current appears to allow multiple commas and/or multiple spaces and/or spaces between strings w/no commas. In case that would goober up a search or split or anything, here is where I got on it.
[vba]
Private Sub Worksheet_Change(ByVal Target As Range)
'// Early-bound: Microsoft VBScript Regular Expressions 5.5

Dim RegExpress As New RegExp

Application.EnableEvents = False
If Target.Count = 1 Then
If Not Target.Value = vbNullString Then
With RegExpress
'// 2 or more alpha char, followed by 0 to 1 comma(s),
'// followed by 0 to 1 spaces, repeatable 1 to x times,
'// I uh...think?
.Pattern = "^([A-Za-z]{2,}[\,]?[ ]?){1,}$"
' "^[A-Za-z]{2}[,A-Za-z ]*$"
If Not .test(Target) Then
Target.Value = vbNullString
End If
End With
End If
End If
Application.EnableEvents = True
End Sub
[/vba]

Hope that helps (heck, I hope it doesn't blow up anything),

Mark

[Simon Lloyd]

GTO, thanks for going to the effort, thats pretty good, far to complex for my situation as i do indeed need the ability for multiple comma's and spaces, but thats a pretty useful routine