Slight adaptation of a User Defined Function

Question

I would like to extract a combination of text and numbers from a larger string located within a column within excel.

The constants I have to work with is that each Text string will

•either start with a A, C or S, and •will always be 7 Characters long •the position of he string I would like to extract varies

The code I have been using which has been working efficiently is;

Public Function Xtractor(r As Range) As String
Dim a, ary
ary = Split(r.Text, " ")
    For Each a In ary
        If Len(a) = 7 And a Like "[SAC]*" Then
            Xtractor = a
            Exit Function
        End If
    Next a
Xtractor = ""
End Function

However today I have learnt that sometimes my data may include scenarios like this;

What I would like is to adapt my code so If the 8th character is "Underscore" and the 1st character of the 7 characters is either S, A or C please extract up until the "Underscore"

Secondly I would like to exclude commons words like "Support" & "Collect" from being extracted.

Finally the 7th letter should be a number

Any ideas around this would be much appreciated.

Thanks

Answer 1

try this

ary = Split(Replace(r.Text, "_", " "))

or

ary = Split(Replace(r.Text, "_", " ")," ")

result will be same for both variants

test

update

Do you know how I could leave the result blank if the 7th character returned a letter?

Public Function Xtractor(r As Range) As String
Dim a, ary
ary = Split(Replace(r.Text, "_", " "))
    For Each a In ary
        If Len(a) = 7 And a Like "[SAC]*" And IsNumeric(Mid(a, 7, 1)) Then
            Xtractor = a
            Exit Function
        End If
    Next a
Xtractor = ""
End Function

test