Помогите: нужно вытащить из столбца натуральные числа и сделать анализ

[zoldato55]

Доброй день!

Помогите кто может: Есть столбец с текстовыми значениями подобного типа

120069080000100001000
120069080000100002008
120069080000100003006
120069080000100004009
120069080000100005007
120069080000100007005
120069080000100008001
120069080000100011003
120069080000100012005

в каждой из ячеек нужно отбросить последние три символа, из оставшегося текста взяв последние 5 символов преобразовать их в натуральное число - получится столбец из чисел:
1
2
3
4
5
7
8
11
12

нужно в этом столбце найти пропущенные порядковые номера, например для данного примера пропущенные номера будут 6, 9, 10.
Это видимо можно сделать только макросом?

[mikekaganski]

Что значит "найти"? куда-то записать? показать диалог? подсветить идущие неподряд ячейки?

[zoldato55]

Что значит "найти"? куда-то записать? показать диалог? подсветить идущие неподряд ячейки?

Записать в отдельный столбец, или через запятую в одну строчку.

[mikekaganski]

А, ну тогда да, макросом...

[zoldato55]

А, ну тогда да, макросом...

Поможете?

[bigor]

По данному примеру можно и формулой

Код Выделить Развернуть

=TEXTJOIN(",";1;IF(C1:C8+1<>C2:C9;(C1:C9)+1;"");IF(C2:C9-1<>C1:C8;(C2:C9)-1;""))

[mikekaganski]

Так можно даже без повтора ...

Код Выделить Развернуть

=TEXTJOIN(",";1;IF(C2:C9-C1:C8>1;(C1:C9)+1;"");IF(C2:C9-C1:C8>2;(C2:C9)-1;""))

... но при разрыве в 3 и больше уже правильно не сработает.

[zoldato55]

Что-то не пойму как обе формулы работают - показывают "#ЗНАЧ!".

[kompilainenn]

Наверное функции на русском включены?

[zoldato55]

Наверное функции на русском включены?

вы про настройку в параметрах "Использовать английские имена функций"? переключал ее и так и эдак - все равно ошибка в формуле видимо...

[zoldato55]

Сорян всем - чет туплю - не увидел готовый файлик "for_zold1.ods"...
Работает частично, как Вы и говорили - не работает при больших разрывах в нумерации.

Во вложении пример - выделил красным как должно быть при больших разрывах.

[bigor]

Можно и дальше поизвращаться с формулами
Ищет до 10 пропусков, легко расширяется

Код Выделить Развернуть

=TEXTJOIN(",";1;IF(C2:C9-C1:C8>1;IF({1;2;3;4;5;6;7;8;9;10}<C2:C9-C1:C8;(C1:C8)+{1;2;3;4;5;6;7;8;9;10};"");""))

Что-то не пойму как обе формулы работают - показывают "#ЗНАЧ!".

Формулы массивные, вводятся тремя кнопками Ctrl+Shift+Enter

[mikekaganski]

Здорово!

Упрощение:

Код Выделить Развернуть

=TEXTJOIN(",";1;IF(C2:C9-C1:C8>{1;2;3;4;5;6;7;8;9;10};C1:C8+{1;2;3;4;5;6;7;8;9;10};""))

Жаль, нет сортировки.

[zoldato55]

Да, точно последние формулы работают - большое Спасибо за них (особенно про комбинацию клавиш Ctrl+Shift+Enter - не знал, что так нужно).
Можно еще попросить - кто может добавить:
1) какое-то универсальное решение по расширению диапазона разрывов  - немного проблематично если там пропуск в 500 или 3000 штук например.
2) с сортировкой действительно было бы круто.

[bigor]

Выжимаем формулы дальше
В принципе любой пропуск, меняем диапазон а1:а10, на необходимый

Код Выделить Развернуть

=TEXTJOIN(",";1;IF(C2:C9-C1:C8>TRANSPOSE(ROW(A1:A10));C1:C8+TRANSPOSE(ROW(A1:A10)));"")
С сортировкой пока никак
Добил и сортировку, и теперь даже не массивная

Код Выделить Развернуть

=TEXTJOIN(",";1;TRANSPOSE(IF(C2:C9-C1:C8>TRANSPOSE(ROW(A1:A10));C1:C8+TRANSPOSE(ROW(A1:A10));"")))