☰

如何分解單個單元格中沒有逗號的數字並將其放入多行中？

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. 過濾以僅顯示公式不傳回空白字串的記錄。這意味著IMAGELIST24 個字元長的記錄僅顯示 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
    • 並向右拖曳以覆蓋所有列（即，透過 Column  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（這與 Columns -中的公式相同 D，除了引用Sheet1, Column  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 這row (on Sheet2) 正在從中提取資料。
  • 每次我們前進到一個新Sheet1行（即每次 Column F增加），Column G都會得到一個完整的，8×n-來自 的數字長IMAGELIST值Sheet1。
  • Column 中的數值 G被分割：前八位數放入 Column  E，其餘數字放入 Column  H。
  • 只要 Column 中有非空餘數 H，我們就將其複製到 Column 中 G並保持 Column F不變（即訪問 上的同一行Sheet1）。
  • 當我們用完 Column 中的資料時 H，這意味著我們已經分解了 8×nIMAGELIST-從Sheet1進入的數字長值n上的行Sheet2，因此我們增加 Column F並獲得下一個IMAGELIST值。

答案3

為了實現您的目標，您需要執行兩個步驟：

1 將數字分成不同的列，例如在 Excel 2010 中：資料 - 文字到列 - 固定寬度 - 將寬度設為 8 然後按完成。

2 第二步是取消資料透視，不幸的是這並不簡單，但是網路上有很多解決方案，如下所示：是否可以在 Excel 中「逆透視」或「反向透視」？

相關內容