Excel自动将7位CAS号转换为另一个数字（日期？）

Question

问题：我正在处理两个列表，一个叫做HYPHEN，一个叫做CAS编号，分别在A列和B列中。

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

CAS号是材料/化学物质的唯一标识，通常写作000-00-0，但有时也会出现0000-00-0（或其他变体）。大部分情况下，列C是正确的，因为除了一个CAS号以外，其余都采用了通常的格式。然而，如红色突出显示的6132-04-3被转换为1545801。我尝试过将单元格格式设置为文本格式，添加逗号等方法，但都无法返回所需值6132-04-3，而总是返回1545801。要复制该问题，请在A列和B列输入任何数据，然后使用以下公式来复制C列的输出：

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

(该公式由Stack Overflow上的@Gary's Student提供)

如果在列C中排序时，如何防止红色CAS编号被转换，欢迎提出任何想法。

- Nick

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)

- Tom Sharpe

---

嗨，谢谢你。顺便问一下，当我使用化学名称如3,4-二甲基苯甲醛时，也遇到了问题。问题是由于逗号的存在，数据被分成了3和4-二甲基苯甲醛两个单元格。我真的不熟悉编写复杂的Excel公式，但如果您知道如何实现这一点，那就太完美了。如果不行也没关系。 - Nick

说实话，我对Filterxml还比较陌生，也许有更优雅的解决方案来解决你最初的问题。无论如何，我认为回答你的评论相当容易，只需使用不同的分隔符，例如过滤符号而不是逗号。我会在我的答案中添加这个建议。 - Tom Sharpe

Answer 2

看起来你需要：

D2 单元格中的公式：

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

或者：

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

- JvdV

---

@iNick1，不客气。这是一个有趣的小问题。如果你想了解更多关于FILTERXML()的内容，可以在这里查看。 - JvdV

如果您有时间的话，能否简要解释一下之前发生了什么？我确实想在Excel方面变得更好，但在这个阶段，它是一个相当陡峭的学习曲线。 - Nick

@iNick1，FILTERXML()从我们之前使用TEXTJOIN()串联的有效xml字符串中返回一个值数组。 Excel所做的（在我看来很烦人）是尝试处理每个元素，就像您使用VBA并且每个变量默认为变体类型变量一样。换句话说，Excel将尝试理解数据并自行分配数据类型。为了防止这种机制，我们在其中添加了'以防止Excel识别这些元素为数字（因此我们还防止了自动处理和格式化为不需要的数字）。 - JvdV

Answer 3

我可以向您建议以下内容：

前往“格式单元格”--->“数字”--->“自定义”--->“类型”

在“类型”字段中输入#000-00-0 按“确定”

- Ashgabat