☰

在 Excel 中解析数据--空格分隔--从右到左

tag-icon • tag-icon microsoft-excel worksheet-function
在 Excel 中解析数据--空格分隔--从右到左 
1     Exxon Mobil     452,926.0     41,060.0
2     Wal-Mart Stores     446,950.0     15,699.0
3     Chevron     245,621.0     26,895.0
4     ConocoPhillips     237,272.0     12,436.0
5     General Motors     150,276.0     9,190.0
6     General Electric     147,616.0     14,151.0
7     Berkshire Hathaway     143,688.0     10,254.0

如何获取上述数据并在 Excel 中创建四列数据？排名# 公司名称 #数据1 #数据2

Excel 中的文本拆分成列函数
这个解决方案接近...

“从右到左读取行”并使用前两个空格分隔文本功能在哪里？

Chers，Mw

答案1

我会分两步完成此操作。

  1. 数据似乎使用五个空格来分隔，因此我会在文本编辑器中进行查找和替换，将任何五个空格的字符串替换为“安全”字符，例如制表符或管道符（|）
  2. 在 Excel 中打开文件，选择适当的分隔符或使用数据 > 文本到列

答案2

您也可以使用公式来执行此操作。在 B 列中，复制此公式：

=SUBSTITUTE(A2,"     ","|")

这将替换 5 个空格，|结果是1|Exxon Mobil|452,926.0|41,060.0

在 C 列中，向下复制此公式：

=TRIM(LEFT(SUBSTITUTE($B2,"|",REPT(" ",50)),50))

这将|用 50 个空格替换，取左边的 50 个空格，并修剪填充空格。所以你最终会得到1

在 D 列中，向下复制此公式：

=TRIM(MID(SUBSTITUTE($B2,"|",REPT(" ",50)),50,50))

它不取左边的 50 个字符，而是取从第 50 个字符开始的 50 个字符。

E 列相同，但从第 100 个字符开始：

=TRIM(MID(SUBSTITUTE($B2,"|",REPT(" ",50)),100,50))

F列取最右边的50个字符：

=TRIM(RIGHT(SUBSTITUTE($B2,"|",REPT(" ",50)),50))

如果不喜欢中间有列的话，可以替换$B2原公式：=SUBSTITUTE(A2," ","|")跳过一步。

您还可以将原始公式从 更改为 ，=SUBSTITUTE(A2," ","|")这样=SUBSTITUTE(A2,REPT(" ",5),"|")如果您的数据发生变化，可能会更易于阅读和处理。

答案3

没有人给出“神奇”的答案，所以我将给出另一个“艰难前行”的答案。请注意，现在是 2021 年，所以有更好的选择，我也会解决这个问题，但 2013 年版本中可用的变体是优先的。

因此，如果进行完整导入，可以使用Import Wizard但结果并不理想，因为它不接受两个或更多字符的分隔符。因此，“Exxon Mobile”以两列而不是一列的形式出现。我相信 Power Query 在 2013 年就已推出，以某种名称推出，因此这将是进行基本导入的方式。它可以让人们克服单字符分隔符问题，而无需进行大量手动工作（在 PQ 中第一次使用后，然后自动完成）。而且我相信即使在那时它也可以让人们重新排列列，所以...

无论如何，让我们考虑一下问题中的数据已经在 Excel 中，需要拆分和整理。

如何拆分？由于Text to Columns ("T2C")只是Import Wizard在需要导入文件的位置之后输入Import Wizard，因此这样做不会更好。可以运行它并手动移动或公式移动列外的材料，但这会很糟糕。

因此，用公式将其分解。我这里没有提出什么花哨的东西，只是一种非常简单的“将其放入需要的列中”的公式集，然后快速处理排列。

需要四列，并且如第一个答案中所述，五个空格是分隔符字符串。此方法的工作原理如下：

  1. 找到这些分隔符字符串中的第一个的起始位置。从中减去 1，即可得到所需第一个字符串的最后位置。（因此，如果FIND()报告“2”，则行数据字符串第一部分的最后一个字符以该位置结束。（我MID()在公式中使用，因为我更喜欢它，LEFT()或者RIGHT()除非它们是必需的。个人偏好，我发现这样更具扩展性。）

    =MID(A1,1,FIND(" ",A1)-1)

  2. 接下来，在 A1 中“从原始字符串中减去该值”。从字符串中“减去”是通过SUBSTITUTE()将内容替换为空白字符串 (“”) 来完成的。因此：

    =MID(SUBSTITUTE(A1,I1&" ",""),1,FIND(" ",SUBSTITUTE(A1,I1&" ",""))-1)

    它执行“减法”产生一个以下一个所需值开头的较小字符串，然后使用步骤 1 中的方法获取该值。

  3. 正如洗发水瓶上写的“冲洗并重复”。对每个想要的部分重复上述步骤，直到最后一个。在这种情况下，只需再做一次！对于每个部分，您必须将其添加到要替换的字符串中。然后该字符串可能会变得很长，但这几乎不令人生畏，也没有任何困难。

    =MID(SUBSTITUTE(A1,I1&" "&H1&" ",""),1,FIND(" ",SUBSTITUTE(A1,I1&" "&H1&" ",""))-1)

    看看I1&" "&H1&" "绳子是如何增长的？

  4. 因此，最后，只需“减去”已分割出的完整字符串及其中间的分隔符字符串。由于没有更多内容需要查找，因此只SUBSTITUTE()需要“减法”( )，无需通过 `MID() 查找其他部分。

    =SUBSTITUTE(A1,I1&" "&H1&" "&G1&" ","")

现在您有了这些部分，但不是按“从右到左”的顺序。您可以将这些公式设置在 F 列到 I 列中，但从 I 列开始向左移动。但这不是您思维的自然方式，因此，这样做会不舒服，但更重要的是，将公式复制到下一列并不一定以理想的方式调整您的单元格引用。因此，当我遇到这种情况时，我仍然更喜欢以“自然”的方式从左到右工作。尽管在这种情况下，我从内部复制了公式Formula Editor，而不是从一个单元格复制到另一个单元格，所以这可能根本不重要。只是喜欢“自然”的感觉。

像这样创建公式后，只需在公式左侧插入一个单元格，然后Drag and Drop按所需顺序（无法在此处输入“正确”...“自然”等等）重新排列公式即可。完成。根据需要将公式复制下来，然后完全完成。

通常，我喜欢使用INDEX()来反转列顺序，因为它可以自动执行该过程，让公式独立存在，无需手动干预，但这需要使用另一组单元格，因为这些公式是独立的，因为不能轻易将它们引用为 的范围INDEX()。所以，没有它，使用Drag and Drop。无论如何，这只是一个小小的额外步骤。

在 2021 年，人们可以简单地使用FILTERXML()技巧（SUBSTITUTE()对于分隔符字符串，使用新的分隔符字符串</Element><Element>，并在开头标记和结尾标记前面添加和附加（使用“Group”作为外部标记）以获取FILTERXML()可以使用的字符串）。这为您提供了一个数组（一个SPILL数组），您可以直接将其输入INDEX()并使用它来反转数组中的列。所以Drag and Drop最后不要这样做，只需让公式独立存在即可。由于这一切都发生在单个单元格中，向右溢出，因此您可以设置INDEX/FILTERXML"SPILL` DOWNWARDS 来处理以前需要“向下复制”的情况。一个公式可以取代它们。（对不起 JRRT。）

（现在，如果有人想逐个字符地反转字符串，而不是逐个部分地反转，那就完全是另一回事了。从标题来看，我认为这实际上是愿望。好吧，阅读并学习，嗯？）

相关内容