☰

将文本拆分为多行——TEXTSPLIT 的替代方法

tag-icon • tag-icon microsoft-excel office365
将文本拆分为多行——TEXTSPLIT 的替代方法

办公室 365

TEXTSPLIT：我没有这个新功能。请提出解决方案。我不懂 VBA。

问题：从我们的会计服务下载到 Excel 文件时，所有数据都放在一个没有分隔符的单元格中。数据大约有 200 行。

搜索其他帖子：有约 1600 个帖子，因此没有搜索全部

  • 将单个单元格中的数据拆分为多个记录：与帖子有相似之处，只是单元格内没有 AltReturn 来分隔数据。
  • 如何将一行 Excel 数据拆分为多行：我正在查看 Power Query，但建议的响应认为有逗号分隔符

同一单元格中 4 条记录的数据示例：

APS Deposit 04/01/2022 $5,174.27 APS ACH Deposit 04/04/2022 $65,186.66 APS Deposit 04/04/2022 $10,380.00 APS Deposit 04/05/2022 $5,943.36

所需结果（列间距）：
APS 存款 2022 年 4 月 1 日 5,174.27 美元
APS ACH 存款 2022 年 4 月 4 日 65,186.66 美元
APS 存款 2022 年 4 月 4 日 10,380.00 美元
APS 存款 2022 年 4 月 5 日 5,943.33 美元

谢谢您的任何意见。

答案1

我建议您将这些数据导入 Power Query，然后使用拆分列 > 按分隔符 > 在自定义下输入“APS”并选择将数据拆分为行。

在此处输入图片描述

然后单击“删除行”并选择“删除空白行”。

在此处输入图片描述

单击自定义列 > 输入公式Text.Combine({"APS",[Column1]})以添加回“APS”。

在此处输入图片描述

删除之前的一列，剩下“自定义”这一列，继续拆分。

单击拆分列 > 按分隔符 > 在自定义下输入“$”，选择列并输入“2”。

在此处输入图片描述

选择 Custom.1 列 > 拆分列 > 按字符数 > 输入 11 并选择“一次，尽可能靠右”。

在此处输入图片描述

答案2

在 Power Query 中执行的操作如下：

  • 在数据表中选择一些单元格
  • Data => Get&Transform => from Table/Range或者from within sheet
  • 当 PQ 编辑器打开时：Home => Advanced Editor
  • 记下表格姓名在第 2 行
  • 将下面的 M 代码粘贴到您所看到的位置
  • 将第 2 行的表名改回最初生成的表名。
  • 阅读评论并探索Applied Steps以了解算法

M 代码

let
    Source = Excel.CurrentWorkbook(){[Name="Table3"]}[Content],
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Column1", type text}}),

//Insert delimiters by prepending semicolon to every instance of " APS" (so will exclude the first "APS"
    #"Insert Delimiters" = Table.TransformColumns(#"Changed Type",{"Column1", each Text.Replace(_," APS",";APS")}),

//Split by the semicolon into rows
    #"Split Column by Delimiter" = Table.ExpandListColumn(Table.TransformColumns(#"Insert Delimiters", {
        {"Column1", Splitter.SplitTextByDelimiter(";", QuoteStyle.Csv), let itemType = (type nullable text) meta [Serialized.Text = true] in type {itemType}}}), "Column1"),

//Split the column by the last two spaces {" "," "}
    #"Split Column by Delimiter1" = Table.SplitColumn(#"Split Column by Delimiter", "Column1", 
         Splitter.SplitTextByEachDelimiter({" "," "}, QuoteStyle.Csv, true), {"Type", "Date","Amount"}),

//set the data types
    #"Changed Type1" = Table.TransformColumnTypes(#"Split Column by Delimiter1",{{"Date", type date}, {"Amount", Currency.Type}})
in
    #"Changed Type1"

在此处输入图片描述

答案3

假设您已将数据粘贴到 A1 单元格中。
您可以在任何其他单元格中使用公式，如下所示：

=CONCATENATE("APS " & FILTERXML("<t><s>" & SUBSTITUTE(A1; "APS"; "</s><s>") & "</s></t>"; "//s"))

输出中的第一个单元格会出错 - 这是因为输入字符串以“APS”开头，而过滤器获取的替换字符串以“</s><s>”开头。您可以尝试进一步解决此问题。

答案4

使用 Excel 中的内置函数无法轻松完成此操作，尤其是在您不了解 VBA 的情况下。

如果你不介意使用其他工具，我强烈建议你使用 Sublime Text 来处理原始数据。它就像是 Notepad 的高级版本，我最喜欢的功能是它允许你毫不费力地选择特定字符，然后使用多个光标和箭头键同时编辑它们。

您可以遵循以下一些简单的步骤来处理数据：

  1. 下载并安装Sublime Text，还有便携版本可用，选择您喜欢的版本。
  2. 打开 Sublime Text，将所有数据粘贴到其中。屏幕截图 1
  3. 观察：每行数据有且仅有 1 笔交易，并且小数点后始终有 2 位。这就是我们可以开始处理的地方。
  4. 用鼠标光标突出显示交易中的任何.交易，然后按住Ctrl+D直到全部.选中。（或者，您可以按Ctrl+H打开搜索功能，然后单击Find All按钮全选.）屏幕截图 2
  5. 按箭头键->3 次移动到每笔交易的末尾，然后Enter按键。现在您已将每笔交易分成几行。屏幕截图 3
  6. 接下来，您需要添加分隔符。关注步骤4要选择全部$，请按箭头键<-向左移动一次，然后键入#分隔符（或原始数据中未使用的任何其他字符）。屏幕截图 4
  7. 与...一样第 6 步，全选/20，向左移动 6 次，任意在日期前输入“#”。屏幕截图 5
  8. 最后，您可以将其保存为.txt文件，然后使用文本导入向导在 Excel 中的功能中使用它#作为分隔符来获得所需的结果。

这些动作乍一看可能很复杂，但熟悉之后只需要几分钟，可以为你以后处理此类数据节省大量时间。

相关内容