使用公式进行分隔文本到列

10 浏览
0 Comments

使用公式进行分隔文本到列

我有一些单元格中的字符串，看起来像这样（可能有数百个）

713.00;482.00;337.00;530.00;

71.00;32.54;37.00;5300.30;

  • 有4个数字，每个数字后面都有一个分号。
  • 这些数字的长度会有所不同。

我已经有了第一个单元格（Split 1）的公式，如下：

=LEFT(B2,(FIND(";",B2,1)-1))

我只是无法弄清楚后面3个单元格（Split 2、3和4）的公式。

注意：这必须是一个公式，而不是文本分列功能。

如有建议，将不胜感激，如果需要更多信息，请提问！ 🙂

0

1 答案

0 Comments

在Excel中，有时候我们需要将一个带有分隔符的文本拆分为多列，以便更好地处理数据。然而，Excel本身并没有提供直接拆分文本的函数。因此，我们需要使用一些公式来实现这个功能。

首先，让我们来看一下出现这个问题的原因。在给定的示例中，我们有一个包含分号分隔符的文本字符串，如"A;B;C;D"。我们希望将它拆分为"A"、"B"、"C"和"D"四个列。为了实现这一点，我们可以使用一个基于公式的方法。

解决方法是使用以下公式：

=TRIM(MID(SUBSTITUTE($A1,";",REPT(" ",999)),COLUMNS($A:A)*999-998,999))

这个公式的作用是将原始文本字符串中的分号替换为空格，并使用MID函数按照特定的字符位置截取出所需的文本。这个公式需要在第一列的单元格中输入，并在其他列和行中复制。

如果我们要将拆分后的文本转换为数字而不是文本子字符串，可以使用以下公式：

=--TRIM(MID(SUBSTITUTE($A1,";",REPT(" ",999)),COLUMNS($A:A)*999-998,999))

并使用两位小数的格式来格式化结果。

然而，这个方法存在一个弱点，就是999这个数字是任意的，如果原始字符串的长度大于这个数字，公式将无法正常工作。为了解决这个问题，我们可以使用LEN($A1)来代替999：

=--(TRIM(MID(SUBSTITUTE($A1,";",REPT(" ",LEN($A1))),(COLUMNS($A:A)-1)*LEN($A1)+1,LEN($A1))))

这个方法的一个优点是它非常巧妙。然而，它仍然存在一个弱点，就是数字999是任意的，如果原始字符串的长度大于这个数字，公式将无法正常工作。取而代之的是，可以使用LEN($A1)来代替999。

此外，还需要注意的是Excel函数对字符串长度有限制，最大长度为2^15-1。因此，如果输入列表中的元素足够多，用999个空格替换分隔符的方法将超出这个限制。如果使用LEN($A1)，长度会更长，但仍然有限。

总之，使用公式将带有分隔符的文本拆分为多列是一种常用的Excel技巧。尽管存在一些局限性，但这种方法仍然是解决这个问题的有效途径。

0