☰

如何拆分单个单元格中没有逗号的数字并将其放入多行？

tag-icon • tag-icon microsoft-excel worksheet-function microsoft-access
如何拆分单个单元格中没有逗号的数字并将其放入多行？

所以这就是我想要做的。我有一个电子表格，其中有一个单元格，里面有一串数字（有些比其他的长），我需要提取每八个数字并将它们放入一行中。

当前电子表格如下所示：

IFIELD1     IFIELD2   IFIELD3     IFIELD4    IMAGELIST
05/30/2009  ERMS      100 Day     L. C.      0000001200000013
05/30/2009  ESE       20 Day      J. R.      0000001400000015
05/30/2009  IHS       100 Day     H. W.      00000016
05/30/2009  LMS       100 Day     D. E.      0000001700000018

这就是我想要得到的：

IFIELD1     IFIELD2   IFIELD3   IFIELD4   IMAGELIST
05/30/2009  ERMS    100 Day     L. C.     00000012
05/30/2009  ERMS    100 Day     L. C.     00000013
05/30/2009  ESE     20 Day      J. R.     00000014
05/30/2009  ESE     20 Day      J. R.     00000015
05/30/2009  IHS     100 Day     H. W.     00000016
05/30/2009  LMS     100 Day     D. E.     00000017
05/30/2009  LMS     100 Day     D. E.     00000018

有些数字组每行多达 30 组或更多，每组 8 个数字。有超过 1000 行数据需要分隔。是否有任何类型的代码可以帮助使这个过程不那么令人生畏？我目前使用左、中和右，但这将需要我几年的时间才能完成。工作表即将退出访问，因此如果有更简单的方法来完成它，我愿意接受建议。最终产品必须保存在 csv 中。此外，我需要在保存为 csv 之前保留完成产品中的整个 8 位数字，以便我可以创建指向该图像所在位置的链接。感谢您的帮助！

答案1

Access 中有一个相当简单的解决方案。

  1. 创建一个表格，该表格只是一串数字：您的 中最多包含哪些项目1, 2, 3, 4, ... n。不过，将其做得比需要的要大，成本不会很高，因此可以将其增加到 50 左右。nIMAGELIST
  2. 创建一个查询，提取原始表和数字表没有任何连接。如果您SELECT *此时这样做，您将获得n原始表中每条记录的副本。
  3. 不要提取字段IMAGELIST，而是添加一个公式，该公式根据数字表中的值MID对字段执行函数。IMAGELIST
  4. 筛选仅显示公式不返回空白字符串的记录。这意味着IMAGELIST长度为 24 个字符的记录仅显示 3 次，而不是n显示一堆空白记录。

以下是该查询的 SQL：

SELECT Table1.ID, Table1.IFIELD1, Table1.IFIELD2, Table1.IFIELD3, Table1.IFIELD4, Table2.Number, Mid([IMAGELIST],([Number]-1)*8+1,8) AS ImageListParse
FROM Table1, Table2
WHERE Mid([IMAGELIST],([Number]-1)*8+1,8)<>""
ORDER BY Table1.ID, Table2.Number;

在我的快速模型中，Table1是您的原始表格，Table2是数字表格，Number是唯一的字段Table2，它只是数字 1-n，我添加了自动编号字段ID只是Table1为了排序。

这一切都假设每个项目IMAGELIST恰好是 8 个字符。如果长度不定，这种方法就行不通了。

答案2

使用 Excel 工作表函数完成这项工作并不难。假设您的数据Sheet1在第 1 行带有标题。创建Sheet2。按如下方式填写：

  • A1→=Sheet1!A1
    • 并向右拖动以覆盖所有列（即通过 列 E，基于您的五列示例），
    • 或者简单地将标题从 复制Sheet1到Sheet2。
  • A2→=OFFSET(Sheet1!A$1, $F2-1, 0)
    • 并向右拖动以覆盖所有列除了 IMAGELIST （即通过 Column  D）。
    • 在某些时候，您需要Sheet2正确格式化列（即包含日期的列）。
  • E2→=LEFT($G2, 8)
  • F2→2
  • G2→=OFFSET(Sheet1!E$1, $F2-1, 0)
    • A（这与列-中的公式相同 D，只是引用了Sheet1列 E。）
  • H2→=RIGHT($G2, LEN($G2)-8)

将第 2 行拖拽/填充至第 3 行。然后更改

  • F3至=IF(H2<>"", F2, F2+1)，和
  • G3到=IF(H2<>"", H2, OFFSET(Sheet1!E$1, $F3-1, 0))

现在将第 3 行向下拖动/填充，直至获得所需的所有数据Sheet1：

然后复制并粘贴值和格式。

解释：

  • 列 F表示哪一行Sheet1 这行（在 上Sheet2）正在从中提取数据。
  • 每次我们前进到新的Sheet1一行（即每次列 F增加时），列 G都会得到一个完整的 8×n-digit-longIMAGELIST值来自Sheet1。
  • Column 中的值 G被拆分：前八位数字放入 Column 中 E，余数放入 Column 中 H。
  • 只要 Column 中有一个非空余数 H，我们就将其复制到 Column 中 G，并保持 Column F不变（即访问 上的同一行Sheet1）。
  • 当我们用完第 列中的数据时 H，这意味着我们已经分解了 8×n-digit-longIMAGELIST值从Sheet1到n行Sheet2，因此我们增加列 F并获取下一个IMAGELIST值。

答案3

为了实现你的目标，你需要执行两个步骤：

1 将数字分成不同的列，例如在 Excel 2010 中：数据 - 文本分列 - 固定宽度 - 将宽度设置为 8，然后单击完成。

2 第二步是取消数据透视，不幸的是，这并不简单，但互联网上有很多解决方案，如下所示：可以在 Excel 中“取消透视”或“反转透视”吗？

相关内容