☰

Excel 中的文本到列 - 选择性地分成列

tag-icon • tag-icon microsoft-excel-2010
Excel 中的文本到列 - 选择性地分成列

我正在尝试将单个单元格分成几列，但我只需要最后 3 个值（以“空格”分隔）位于分隔的单元格中。

我有这个（在一列中）：

             Column A
Perdas com variações cambiais, líquidas 7 21.919.104 1.841,496
Perdas em ativos financeiros, líquidas 18 2.014.467 3.921.932
Perdas em empreendimentos conjuntos 30 2.185.679 3.011.998
Ganhos em empresas associadas, líquidos 33 (442.783.587) (210.273.584)

...我需要有4列（一列用于文本，另外3列用于数值）：

             Column A                     Column B    Column C        Column D
Perdas com variações cambiais, líquidas       7      21.919.104           1.841,496
Perdas em ativos financeiros, líquidas       18       2.014.467       3.921.932
Perdas em empreendimentos conjuntos          30       2.185.679       3.011.998
Ganhos em empresas associadas, líquidos      33    (442.783.587)   (210.273.584)

答案1

这是一个包含一些大型公式的解决方案，但没有任何辅助列需要担心。假设您的相同数据在范围内A1:A4，这些是公式B1:F1。复制/粘贴并向下拖动。

要点：这假设您的数据遵循示例的格式。如果原始数据中的空格少于 3 个，则会抛出错误。不过，您在问题中指定了这种情况，所以我预计不会出现问题。

要点：这假设~您的文本中没有波浪符号。如果出现，请选择其他没有波浪符号的单个字符。管道符|就是一个常见的例子。

B1 =LEN($A1)-LEN(SUBSTITUTE($A1," ",""))
C1 =TRIM(LEFT($A1,FIND("~",SUBSTITUTE($A1," ","~",$B1-2))))
D1 =LEFT(TRIM(RIGHT($A1,LEN($A1)-FIND("~",SUBSTITUTE($A1," ","~",$B1-2)))),FIND(" ",TRIM(RIGHT($A1,LEN($A1)-FIND("~",SUBSTITUTE($A1," ","~",$B1-2)))))-1)
E1 =LEFT(TRIM(RIGHT($A1,LEN($A1)-FIND("~",SUBSTITUTE($A1," ","~",$B1-1)))),FIND(" ",TRIM(RIGHT($A1,LEN($A1)-FIND("~",SUBSTITUTE($A1," ","~",$B1-1)))))-1)
F1 =TRIM(RIGHT($A1,LEN($A1)-FIND("~",SUBSTITUTE($A1," ","~",$B1))))

答案2

这种方法需要一堆“辅助列”。选择一个不碍事的列；例如，AA或Sheet2!A （我假设您选择了AA），然后输入此

=IFERROR(FIND(" ", $A1), "")

进入单元格AA1。这将找到单元格中第一个空格字符的字符位置A1。如果没有，FIND()则返回错误，并IFERROR()用空字符串替换错误。然后输入

=IFERROR(FIND(" ", $A1, AA1+1), "")

到单元格 中AB1。（请注意 有A但$没有AA。）这将找到单元格中A1第二个空格字符（即单元格 找到的空格字符之后的第一个空格字符AA1）的字符位置。同样，错误代码将被空单元格替换。将其拖到所需的右侧。（例如，如果您预计实际文本不会超过 24 个单词，那么最多会有 27 个“单词”，计算这三个数字。因此，最多会有 26 个空格，因此拖出到 应该AZ1就足够了。）如果您拖到AB1，AZ1那么AZ1应该包含

=IFERROR(FIND(" ", $A1, AY1+1), "")

然后（假设您在上面填写了范围），在、和AA1:AZ1中输入以下内容：X1Y1Z1

  • X1–=LARGE(AA1:AZ1,3)
  • Y1–=LARGE(AA1:AZ1,2)
  • Z1–=LARGE(AA1:AZ1,1)

这些成为 中倒数第三个、倒数第二个和最后一个空格的位置A1。例如：


  （上图是其本身全尺寸副本的链接。）

我已手动突出显示Excel 为列、和选择的AA-列中的值。AZXYZ

现在使用这些字符位置来剪切所需的值（文本和数字）是一个“简单”的问题：

  • B1–=LEFT($A1, X1-1)
  • C1–=MID($A1, X1+1, Y1-X1-1)
  • D1–=MID($A1, Y1+1, Z1-Y1-1)
  • E1–=RIGHT($A1, LEN(A1)-Z1)

相当于

  • B1–A1直到（但不包括）倒数第三个空格的所有内容（在 中）
  • C1– 倒数第三个空格和倒数第二个空格之间的所有内容
  • D1– 倒数第二个空格和最后一个空格之间的所有内容
  • E1– 最后一个空格后的所有内容

当然，只要有数据，就把所有内容都拖下来。例如，

           

如果输入行末尾有空格（最后一个数字后）或任意两个数字之间有多个空格，则此方法会失效。如果遇到这种情况，我建议您研究一下该TRIM()函数。

相关内容