regex - Slight adaptation of a User Defined Function -

i extract combination of text , numbers larger string located within column within excel.

the constants have work each text string will

•either start a, c or s, , •will 7 characters long •the position of string extract varies

the code have been using has been working efficiently is;

public function xtractor(r range) string dim a, ary ary = split(r.text, " ")     each in ary         if len(a) = 7 , "[sac]*"             xtractor =             exit function         end if     next xtractor = "" end function 

however today have learnt data may include scenarios this;

what adapt code if 8th character "underscore" , 1st character of 7 characters either s, or c please extract until "underscore"

secondly exclude commons words "support" & "collect" being extracted.

finally 7th letter should number

any ideas around appreciated.

thanks

try

ary = split(replace(r.text, "_", " ")) 

or

ary = split(replace(r.text, "_", " ")," ") 

result same both variants

test

update

do know how leave result blank if 7th character returned letter?

public function xtractor(r range) string dim a, ary ary = split(replace(r.text, "_", " "))     each in ary         if len(a) = 7 , "[sac]*" , isnumeric(mid(a, 7, 1))             xtractor =             exit function         end if     next xtractor = "" end function 

test