Extract Multiple Words in a Phrase

Question

I have this formula to extract a specific word that starts with a given characters “CCLVL”. It is working fine, however, I also need the formula to extract words that start with “GCFAC” or “CLINK”

How can I make it find these other words – there should never be more than one instance of the word in the text I am extracting from.

=TRIM(LEFT(SUBSTITUTE(MID(A2,FIND("CCLVL",A2),LEN(A2))," ",REPT(" ",100)),100))

Does the description contain CCLVL123456? If so, this is 3rd Party CCLVL123456 Does the description contain GCFAC4567 If so, this is 3rd Party
Does the description contain CLINK95182 If so, this is 3rd Party

Answer 1

The general idea here is to cut up a string into an array of words using FILTERXML and some xpath to return only those words from that array that interest us. Luckily there is a function called starts-with() we can use in an or structure:

=FILTERXML("<t><s>"&SUBSTITUTE(A1," ","</s><s>")&"</s></t>","//s[starts-with(., 'CCLVL') or starts-with(., 'GCFAC') or starts-with(., 'CLINK')]")

In Excel365 this would return an vertical array, so you may want to use TRANSPOSE() or TEXTJOIN() in conjunction. Also, if you don't have Excel365, you can use INDEX() to retrieve elements from the array in order.

---

In the below example I used:

=TEXTJOIN(",",,FILTERXML("<t><s>"&SUBSTITUTE(SUBSTITUTE(A1,"?","")," ","</s><s>")&"</s></t>","//s[starts-with(., 'CCLVL') or starts-with(., 'GCFAC') or starts-with(., 'CLINK')]"))

---

For a better understanding of the above, see this post.