excel formula - Extract Word that Begins with a " Quote

Question

I have the following formula that Part 1) Looks at cell A7 and if begins with ABC or DEF places that in the cell, if it does not then it looks at the notes in cell B7 and extracts a claim number from my data if it begins with CCLVL, GCFAC, CLINK, CCQWC or CCQWA

the problem is that the word CLINK in my data begins with a " quote - how do I get the formula to find "CLINK and then drop the "? Is that possible?

Here is the formula

=IF(ISNUMBER(SEARCH("abc",A7)),A7,IF(ISNUMBER(SEARCH("DEF",A7)),A7,IFERROR(@FILTERXML("<t><s>"&SUBSTITUTE(B7," ","</s><s>")&"</s></t>","//s[starts-with(., 'CCLVL') or starts-with(., 'GCFAC') or starts-with(., 'CLINK') or starts-with(., 'CCQWC') or starts-with(., 'CCQWA')]"),"")))

If data contains "CLINK456789 I want the result to be CLINK456789

question from:https://stackoverflow.com/questions/65617227/extract-word-that-begins-with-a-quote

Answer 1

Use a helper column

Use a helper column. Assuming the helper column is C, then the formula in C7 is going to be:

=SUBSTITUTE(A7,"""","")

Then in your own formula in B7, replace all references to A7 with C7. There are plenty of related questions like this.