Excel automatically converting 7 digit CAS number to another number date

Question

Problem: I am attempting to deal with two lists. In columns A and B, there is one labelled "HYPHEN" and another labelled "CAS Number."

If a hyphen is present in column A, it is placed before the adjacent CAS number, which is then entered below, and so on until all hyphens and CAS numbers are present. Column C utilizes a formula that combines column A and column B and ranks them. To help illustrate this, I've included an image, and the formula to duplicate it is provided below.

To replicate the issue: Columns A and B can have any data entered. To replicate the output of column C the formula is given below:

Formula for Column C: =FILTERXML(""&SUBSTITUTE(TEXTJOIN(",",TRUE,A2:B26),",","")&"","//b")

(Formula provided by @Gary's Student on Stack Overflow)

Can someone please help me with this?

Answer 1

Looks like you could use:

The formula in D2:

=SUBSTITUTE(FILTERXML("<t><s>'"&TEXTJOIN("</s><s>'",,A2:B10)&"</s></t>","//s"),"'","")

Or:

=MID(FILTERXML("<t><s>'"&TEXTJOIN("</s><s>'",,A2:B10)&"</s></t>","//s"),2,99)