John Carney
asked on
RegX alpha Pattern won't process the letter 'H'
The function below analyzes free text and if the text contains a number followed immediately with a letter from A to M, it enters that value in the calling cell. But for some reason it won't extract "31H" from this text:
"BKK/MNL. 31H nad 22E inop, no sound. 4SR/NR" (In fact it prevents "22E" from displaying as well)
It will display everyalphanumeric combo from 31A to 31M, but not 31H. The really curious thing is that if I change the word "nad" to any other word, then the H works. For example,
"BKK/MNL. 31H supercalifragilisticexpial idocious 22E inop, no sound. 4SR/NR" does indeed produce "31H, 22E" in the desired cell.
Is there anything in the fuinction that would explain that?
Thanks,
John :-)
"BKK/MNL. 31H nad 22E inop, no sound. 4SR/NR" (In fact it prevents "22E" from displaying as well)
It will display everyalphanumeric combo from 31A to 31M, but not 31H. The really curious thing is that if I change the word "nad" to any other word, then the H works. For example,
"BKK/MNL. 31H supercalifragilisticexpial
Is there anything in the fuinction that would explain that?
Thanks,
John :-)
Function GetSeats(InputCell As Range) As String ' 'brettdj & Brad
Dim RegEx, RegM, RegMC
Dim MyDic
Dim tmpStr As String
Dim i As Long
Set MyDic = CreateObject("scripting.dictionary")
Set RegEx = CreateObject("vbscript.regexp")
With RegEx
'.Pattern = "ROW(S)? (\d+)-(\d+)"
'.Pattern = "ROW(S)?\s*(\d+)\s*[,-/]\s*?(\d+)" 'Brad
.Pattern = "ROW(S)?\s*(\d+)\s*([,-/|]|to|thru|through)\s*(\d+)" 'expanded Brad
.Global = True
.IgnoreCase = True
If .test(InputCell.Value) Then
Set RegMC = .Execute(InputCell)
For Each RegM In RegMC
If MyDic.exists(LCase$(RegM)) = False Then
For i = RegM.submatches(1) To RegM.submatches(3)
Select Case i
Case Is <= 4
tmpStr = tmpStr & i & "ACDF, "
Case Is < 7 > 4
tmpStr = tmpStr & i & "ABCDEF, "
Case Else
tmpStr = tmpStr & i & "ABCDEFHJK, "
End Select
Next
tmpStr = Left$(tmpStr, Len(tmpStr) - 2)
MyDic.Add LCase$(RegM), 1
End If
Next
End If
.Pattern = "\d+[A-M]+"
If .test(InputCell.Value) Then
Set RegMC = .Execute(InputCell)
For Each RegM In RegMC
If tmpStr = vbNullString Then
tmpStr = RegM
Else
tmpStr = tmpStr & ", " & RegM
End If
Next
End If
GetSeats = tmpStr
End With
End Function
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
Thanks for the suggestions.
- John