1

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

JvdV
  • 70,606
  • 8
  • 39
  • 70
BastropButtercup
  • 25
  • 4

1 Answers1

0

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')]"))

enter image description here

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

JvdV
  • 70,606
  • 8
  • 39
  • 70
  • 1
    Your explanation of each line is really excellent. We are who still learning excel must read your answers top to bottom line by line. – Harun24hr Dec 26 '20 at 13:43
  • 1
    Formula is AWESOME just what I needed!! Thank you ever so much. – BastropButtercup Jan 07 '21 at 00:34
  • Can this formula be combined with another formula to do the following: Look in Cell C2 and if cell begins with ABC or DEF put the contents of the cell, else perform formula above. If the Cell A2 or Cell C2 contain neither of those things then leave blank. (note cell B2 contains the formula) – BastropButtercup Jan 07 '21 at 16:18
  • FIGURED IT OUT!! Here is the formula that I ended up with ------ =IF(ISNUMBER(SEARCH("abc",A7)),A7,IF(ISNUMBER(SEARCH("DEF",A7)),A7,IFERROR(@FILTERXML(""&SUBSTITUTE(B7," ","")&"","//s[starts-with(., 'CCLVL') or starts-with(., 'GCFAC') or starts-with(., 'CLINK') or starts-with(., 'CCQWC') or starts-with(., 'CCQWA')]"),""))) – BastropButtercup Jan 07 '21 at 16:59