Как найти диапазоны в Excel

Question

В столбце A у меня есть несколько чисел, отформатированных как текст из-за начальных нулей:

+-------------+
|  A          |
+-------------+
| 01011111001 |
| 01011111002 |
| 01011111003 |
| 01011111012 |
| 01011111013 |
+------+------+

Используя столбец A в качестве входных данных, я хочу заполнить два других столбца, B и C, следующим образом:

+-------------+--------------+
|  B          |  C           |
+-------------+--------------+
| 01011111001 | 010111111003 |
| 01011111012 | 010111111013 |
| 01011111115 |              |
| 01011111117 |              |
| 01011111019 | 0101111111020|
+------+------+--------------+

Когда есть последовательность чисел (например, диапазон), я хочу, чтобы начальное число было в столбце B, а конечное число — в столбце C.

Однако если это одно число, его можно оставить в столбце B.

У меня есть 10000 номеров, которые имеют диапазоны и отдельные номера, которые нужно разделить, как указано выше.

Как это можно сделать в Excel?

Answer 1

Использование расширенной выборки данных

01011111002
01011111003
01011111004
01011111005
01011111006
01011111008
01011111009
01011111011
01011111012
01011111013
01011111014
01011111015
01011111016
01011111017
01011111018
01011111020
01011111023
01011111026
01011111027
01011111028
01011111031
01011111032
01011111033
01011111034

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

1) Определите имя pool, которое включает диапазон данных + еще одну ячейку, которая пуста.

2) Определите имя nn, которое учитывает размер poolкак

=ROWS(pool)

3) Определите имя, newrangeкоторое будет формулой:

=MOD((-1+(N(OFFSET(pool,1,,nn-1)-OFFSET(pool,,,nn-1)<>1)*ROW(OFFSET(pool,,,nn-1)))),nn)+1

4) Выберите диапазон рядом со poolстолбцом in Bи введите формулу массива (нажмите Ctrl+ Shift+ Enterвместо просто Enter):

=newrange 

введите 0под последней ячейкой и выберите:

5) Далее скопируйте этот выбор, вставьте как значения и отсортируйте по возрастанию:

6) Введите C1:

=IF(OFFSET($A$1,B1,)>0,OFFSET($A$1,B1,),"")

7) Введите D1:

=IF(OFFSET($A$1,B2-1,)=C1,"",OFFSET($A$1,B2-1,))

8) Выделите C1:D1и перетащите/копируйте вниз, пока в столбце не появятся пустые ячейки C: