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

Question

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

+-------------+
|  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 в столбце 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 появятся пустые ячейки: