Get text after position in string

[sharky12345]

I'm trying to get the text after a string which can be found at any point in a cell.

The full string is 'VIDEO PRESENT: *YES/NO/NOT APPLICABLE'

What I need to extract is the answer, i.e. YES, NO or NOT APPLICABLE. The issue I have is that people have to delete the part of the answer which isn't relevant, leaving their answer, but I am getting all sorts of weird answers such as, *YES, /NO, *YES/ and any other variation you can think of!

So is there an efficient VBA method to grab the answer?

 

[Micron]

I think I see a problem with that. Would need 3 variables, not just i.

 

[nemmi69]

formula method

In B1

=IF(NOT(ISERR(SEARCH("YES",A1))),"Yes",IF(NOT(ISERR(SEARCH("NOT AVAILABLE",A1))),"Not Available",IF(NOT(ISERR(SEARCH("NO",A1))),"No")))

 

[Micron]

OK, this seems to work but due to lack of info about sheet name(s) or ranges those references would need to be dealt with

Sub GetAnswer()
Dim i As Integer, x As Integer, y As Integer
Dim rng As Range

Set rng = Sheets("Sheet5").Range("A2")
i = InStr(rng, "YES")
x = InStr(rng, "NO")
y = InStr(rng, "NOT APPLICABLE")

Select Case True
     Case Is = i > 0 ' YES
          rng = Mid(rng, i, 3)
          
     Case Is = x > 0 And y = 0 ' NO
          rng = Mid(rng, x, 2)
          
     Case Is = y > 0 ' NOT APPLICABLE
          rng = Mid(rng, y, 14)

End Select

End Sub

 

[sharky12345]

Thanks so much guys, a couple of things for me to try so I'll do some testing and come back to you.

 

[Georgiboy]

Sorry my solution won't be of any help as I had overlooked some detail from one of your previous posts.

 

[Micron]

I realized that some issues can arise with that code so more checks are required. Those will also leave the entire string alone if no one edited a value (i.e. the cell value is VIDEO PRESENT: *YES/NO/NOT APPLICABLE). In that case you can be left with a yes or no if all tests are not made. Current version does nothing if that string is found.

Sub GetAnswer()
Dim i As Integer, x As Integer, y As Integer
Dim Lrow As Long, a As Long
Dim rng As Range

On Error GoTo errHandler
Application.EnableEvents = False
Lrow = Range("A2").End(xlDown).Row
For a = 2 To Lrow ' 2 assumes there is one header row
     Set rng = Sheets("Sheet5").Range("A" & a)
     i = InStr(rng, "YES")
     x = InStr(rng, "NO")
     y = InStr(rng, "NOT APPLICABLE")
    
     Select Case True
          'if i,x,y are all >0 then the cell value was left as original so do nothing
          Case Is = i > 0 And x = 0 And y = 0 ' YES
              rng = Mid(rng, i, 3)
          Case Is = x > 0 And y = 0 And i = 0 ' NO
              rng = Mid(rng, x, 2)
          Case Is = y > 0 And x = 0 And i = 0 ' NOT APPLICABLE
              rng = Mid(rng, y, 14)
     End Select
Next
exitHere:
Set rng = Nothing
Application.EnableEvents = True
Exit Sub

errHandler:
MsgBox "Error " & Err.Number & ": " & Err.Description
Resume exitHere

End Sub

 

[sharky12345]

OK, this seems to work but due to lack of info about sheet name(s) or ranges those references would need to be dealt with

Sub GetAnswer()
Dim i As Integer, x As Integer, y As Integer
Dim rng As Range

Set rng = Sheets("Sheet5").Range("A2")
i = InStr(rng, "YES")
x = InStr(rng, "NO")
y = InStr(rng, "NOT APPLICABLE")

Select Case True
     Case Is = i > 0 ' YES
          rng = Mid(rng, i, 3)
         
     Case Is = x > 0 And y = 0 ' NO
          rng = Mid(rng, x, 2)
         
     Case Is = y > 0 ' NOT APPLICABLE
          rng = Mid(rng, y, 14)

End Select

End Sub

This one works just fine - thanks to all who took the time to respond, but Micron has the solution that works.

 

[Micron]

Glad we could help & thanks for the recognition.

 

[sharky12345]

As is usually the case I identify a further issue when I try to expand the solution to cover another scenario!

How could this work if the options are MALE/FEMALE?

I've tried to adapt the suggestion which works for the original question but it's not providing correct answers, I assume because FEMALE also contains MALE?

 

[Micron]

If you look for male then yes, it will be found in "female". Using that method, now you need two more variables (for "male" and "female"), 2 more Case lines and add those 2 tests to every existing line.