View Full Version : Solved: Modifying regex to accept comma's?
Simon Lloyd
06-25-2009, 11:11 AM
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
06-25-2009, 10:11 PM
Psst!!!
In the unlikely event you didn't get your answer here try The Code Cage (http://www.thecodecage.com/)! :devil2:
Simon Lloyd
06-25-2009, 11:21 PM
Ted, now why didn't i think of that?
Now also posted here: http://www.thecodecage.com/forumz/excel-vba-programming/110364-modifying-regex-accept-commas.html#post395094
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.
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
Hope that helps (heck, I hope it doesn't blow up anything),
Mark
Simon Lloyd
06-27-2009, 12:44 PM
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 :)
Powered by vBulletin® Version 4.2.5 Copyright © 2024 vBulletin Solutions Inc. All rights reserved.