0

I admit I'm pretty bad at RegEx, and I'm struggling with this VBA snippet. I am trying to look for an "x" next to an evaluation rating (Not Met, Minimal, Met, Exceed, Outstanding), indicating which rating a person received. Then I need to capture that rating in a variable.

My input text looks something like this:

/Not Met     /Minimal   /Met    xExceed    /Outstanding

This person received an "Exceed" rating, since they have an "x" in front. I have a lot of these to parse through, and the "x" can be in front of any rating.

I'm looking at an earlier RegEx post for help:

How to use Regular Expressions

I'm trying to do a pattern like the below, but this isn't even close. I can't figure out how to look for the "x", make sure the letter next to it is upper case, and then capture all the text after the "x" and before the space.

Dim strPattern As String: strPattern = "[x]{1}[A-Z]{1}"

Any suggestions, or is there an easier non RegEx way to do this?

SJR
  • 22,986
  • 6
  • 18
  • 26
Sudio
  • 153
  • 1
  • 9
  • You could do this with formulae - FIND/SEARCH and MID. Using regexp perhaps a pattern like `x([A-Z]{1,1}[a-z]+)` which uses a capture group. Not tested so probably won't work :) – SJR Oct 16 '20 at 19:56
  • Is that string all in one cell? – Ron Rosenfeld Oct 16 '20 at 20:11
  • I should have mentioned I'm running this VBA in Word. It's all one line of text – Sudio Oct 16 '20 at 20:14
  • Wanted to add, there are spaces after "Not Met", "Minimal"...etc. If they receive the lowest rating however, there would be no space. – Sudio Oct 16 '20 at 20:38
  • Does each rating always start with a uppercase later? Can there be whitespace between x and rating? Does the given rating not have / but all the others wil? – QHarr Oct 18 '20 at 03:42

2 Answers2

2

Here's some VBA code to accomplish what you need along with some test cases:

Option Explicit

Private Sub Command1_Click()
   MsgBox GetRating("xNot Met     /Minimal   /Met    /Exceed    /Outstanding  ")
   MsgBox GetRating("/Not Met     /Minimal   /Met    xExceed    /Outstanding  ")
   MsgBox GetRating("/Not Met     /Minimal   /Met    /Exceed    xOutstanding  ")
   MsgBox GetRating("/Not Met     /Minimal   /Met    /Exceed    /Outstanding  ")
End Sub

Private Function GetRating(ByVal Evaluation As String) As String
   Dim i As Long, j As Long
   
   GetRating = "<na>"
   
   Do
      i = InStr(i + 1, Evaluation, "x")
      
      If i > 0 And Mid(Evaluation, i + 1, 1) = UCase(Mid(Evaluation, i + 1, 1)) Then
         j = InStr(i, Evaluation, "  ")
         
         If j > 0 Then
            GetRating = Mid(Evaluation, i + 1, j - i - 1)
            Exit Function
         End If
      End If
   Loop Until i = 0
End Function

A couple things to highlight:

  1. I'm grabbing the rating from the "x" to 2 spaces otherwise "Not Met" will not be returned correctly.

  2. To simplify coding, I appended 2 spaces to the end of the evaluation.

Brian M Stafford
  • 8,483
  • 2
  • 16
  • 25
1

You could use word boundaries and Or syntax

Public Function GetRating(ByVal searchString) As String
    Dim re As Object

    Set re = CreateObject("VBScript.RegExp")

    With re
        .Pattern = "\bx((Not Met|Minimal|Met|Exceed|Outstanding)\b)"
        If .test(searchString) Then GetRating = .Execute(searchString)(0).submatches(0)
    End With
End Function

Explanation:

enter image description here

Try it: here

In sheet:

enter image description here

QHarr
  • 83,427
  • 12
  • 54
  • 101
  • When I pass this string in to the regex function it looks like this: / Not Met/ Minimal / Metx Exceed/ Outstanding (Exceed is the rating in this case). I can't get the function to return anything but an empty string. Are the "|" the word boundaries? I've replaced with forward slashes, but it still returns an empty string. – Sudio Oct 19 '20 at 12:04
  • 1
    In your example in comment above Metx Exceed the x does not touch against the Exceed i.e xExceed (which it does in original post) – QHarr Oct 19 '20 at 15:15
  • Thanks, didn't catch that – Sudio Oct 19 '20 at 17:54