Extract string between spaces using location based on instr

Question

All - I'm stuck and need some assistance please. I have a string which is a free-text field to the end user. I need to extract a specific string of text from within this field.

Text is in an array and I have confirmed location of necessary text with InStr and I know it is typically surrounded by at least one space on either side.

I'm looking for a way to extract it based on the location using InStr and Split but I'm not sure how to nest these. There could be any number of spaces in field before or after the string I need because some people like excess spaces. String length is typically 12 BUT could be more or less bc it IS a free text field.

I'm open to any solution that gets the string containing "PO" extracted.

Example String: "V000012345 SAPO22-12345 additional information blah blah"

If InStr(1, Arr2(j, 10), "PO", 1) > 0 Then
   Arr3(i, 18) = Split(Arr2(j, 10), " ")(??)
End if

Answer 1

You may try to Filter() the array after Split(). Alternatively, use a regular expression:

Sub Test()

Dim str As String: str = "V000012345 SAPO22-12345 additional information blah blah"

'Option 1: Via Filter() and Split():
Debug.Print Filter(Split(str), "PO")(0)

'Option 2: Via Regular Expressions:
With CreateObject("vbscript.regexp")
    .Pattern = ".*?\s?(\S*PO\S*).*"
    Debug.Print .Replace(str, "$1")
End With

End Sub

It's case-sensitive and the above would return the 1st match.

Answer 2

This would give you the first element of a SPLIT, that contains "PO":

PONumber = Split(arr2(j, 10), " ")(Len(Left(arr2(j, 10), InStr(1, arr2(j, 10), "PO"))) - Len(Replace(Left(arr2(j, 10), InStr(1, arr2(j, 10), "PO")), " ", "")))

This works by counting the number of spaces before the PO and using that as the index of the SPLIT.

I concede however, the FILTER function offered by JvdV saves you all this hassle - I've not seen it used that way before and it's very efficient.