Excel 自动将 7 位 CAS 编号转换为另一个数字(日期？)

Question

问题:我正在使用 2 个列表。 A 列和 B 列中一个称为 HYPHEN，一个称为 CAS Number。

C 列使用组合 A 列和 B 列的公式并对它们进行排序，如果 A 列中存在连字符，则将其插入到相邻的 CAS 编号之前，然后将其插入下方，并且序列继续，以便所有连字符和包括 CAS 编号。我附上了一张图片以更好地解释这一点，下面给出了复制这一点的公式。

CAS 编号是 Material /化学品的唯一标识，通常写为 000-00-0，但偶尔您会得到 CAS 编号为 0000-00-0(或其他变体)的 Material 。

在大多数情况下，C 列是正确的，因为除一个 CAS 编号外，所有编号均采用通常格式。但是，如红色突出显示的 6132-04-3 正在转换为 1545801。

我尝试过的:我已经意识到 6132-04-3 正在转换为 03/04/6132 所以我很确定这被识别为导致问题。我尝试将单元格格式化为文本格式，我在 CAS 编号前添加了一个逗号，但没有返回所需的 6132-04-3 值，而是始终返回 1545801 。

重现问题:A 列和 B 列可以输入任何数据。要复制 C 列的输出，公式如下:

C 列的公式:=FILTERXML(""&SUBSTITUTE(TEXTJOIN(",",TRUE,A2:B26),",","")&"","//b")

(@Gary 的 Student 在 Stack Overflow 上提供的公式)

任何关于如何防止红色 CAS 编号在 C 列中排序时被转换的想法将不胜感激。

Answer 1

这是通过添加然后删除任意字符来修复它的粗略方法:

=MID(FILTERXML("<a><b>"&SUBSTITUTE(TEXTJOIN(",",TRUE,IF(A2:B26="","","x"&A2:B26)),",","</b><b>")&"</b></a>","//b"),2,99)

如果您遇到某些字符串包含逗号的问题，只需使用不同的分隔符即可:

=MID(FILTERXML("<a><b>"&SUBSTITUTE(TEXTJOIN("|",TRUE,IF(A2:B26="","","x"&A2:B26)),"|","</b><b>")&"</b></a>","//b"),2,99)