Как разбить число в одной ячейке без запятых и поместить его в несколько строк?

Question

Итак, вот что я хочу сделать. У меня есть электронная таблица с ячейкой, в которой есть строка чисел (некоторые длиннее других), мне нужно извлечь каждые восемь чисел и поместить их в отдельные строки.

Вот как выглядит текущая таблица:

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. Также мне нужно сохранить все 8 цифр в готовом продукте перед сохранением в формате csv, чтобы я мог создать ссылку на то, где находится это изображение. Спасибо за помощь!

Answer 1

Это не очень сложно сделать с помощью функций листа Excel.  Предположим, что ваши данные находятся на Sheet1 с заголовками в строке 1.  Создать Sheet2 .  Заполните это следующим образом:

• A1 → =Sheet1!A1
  • и перетащите вправо, чтобы покрыть все столбцы (т.е. через столбец E , на основе вашего примера из пяти столбцов),
  • или просто скопируйте заголовки из Sheet1 в Sheet2 .
• A2 → =OFFSET(Sheet1!A$1, $F2-1, 0)
  • и перетащите вправо, чтобы покрыть все столбцы, кроме IMAGELIST (т. е. через столбец 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 получает полное значение IMAGELIST длиной 8 × n из цифр из Sheet1 .
• Значение в столбце G разделяется: первые восемь цифр в столбце E а остальные в столбце H
• Пока у нас есть непустой остаток в столбце H , мы копируем его в столбец G и оставляем столбец F одинаковым (т. Е. Обращаемся к той же строке на Sheet1).
• Когда у нас заканчиваются данные в столбце H , это означает, что мы разложили значение IMAGELIST длиной 8 × n - цифр из Sheet1 на n строк в Sheet2 , поэтому мы увеличиваем столбец F и извлекаем следующее значение IMAGELIST .

Answer 2

Есть два шага, которые вам нужно сделать, чтобы достичь своей цели:

1 разделите числа на разные столбцы, например, в Excel 2010: данные - текст в столбцы - фиксированная ширина - установите ширину 8, затем нажмите «Готово».

2 2-й шаг - отменить вывод данных, к сожалению, это не так просто, но в Интернете существует множество решений, таких как: можно ли "отменить поворот" или "отменить поворот" в Excel?

Answer 3

В Access есть довольно простое решение.

1. Создайте таблицу, которая представляет собой не что иное, как список чисел: 1, 2, 3, 4, ... n где n - это наибольшее количество элементов, которое когда-либо было в вашем IMAGELIST . Это не очень высокая стоимость, чтобы сделать его больше, чем нужно, так что поднимитесь до 50 или около того.
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 символов. Если длина варьируется, этот подход не сработает.