Как извлечь все четырехзначные числа после символа # в Excel

Вы столкнулись с необходимостью быстро извлечь четырехзначные числа, следующие за символом #, из текстовой строки в Excel? Такие задачи часто возникают при анализе данных из смешанных записей – например, когда товары, цвета и коды хранятся в одной ячейке. Сразу отмечу: стандартные функции вроде ПОИСК или ПСТР здесь не справятся, особенно если в строке несколько символов #. Но не переживайте – решение существует, причем в нескольких вариантах.

Давайте рассмотрим три подхода, которые работают даже для сложных случаев вроде «Printer#4000White Table#5000» или «Sky #6000 Blue». Вам не понадобятся макросы или сторонние программы – только формулы.

Способ 1: Комбинация TEXTSPLIT и TEXTJOIN

Этот метод подойдет, если у вас Excel 365 или 2021 с поддержкой новых функций. Формула разбивает текст на части, используя символы-разделители, а затем объединяет нужные элементы:

=TEXTJOIN(",",1,TEXTSPLIT(A1,VSTACK({" ","#"},CHAR(SEQUENCE(26)+{64,96})),,1))

Разберем по шагам:

1. TEXTSPLIT(A1, …) делит текст в ячейке A1 на части. В качестве разделителей выступают:

• Пробел и # – {" ","#"}.
• Все латинские буквы (верхний и нижний регистр) – CHAR(SEQUENCE(26)+{64,96}). Здесь SEQUENCE(26) создает массив чисел от 1 до 26, а CHAR преобразует их в символы (A-Z и a-z).

2. VSTACK() объединяет все разделители в один вертикальный массив.

3. TEXTJOIN(“,”,1,…) склеивает полученные числа через запятую, игнорируя пустые ячейки (аргумент 1).

Совет: Если в тексте есть другие символы (например, кириллица), добавьте их в разделители через запятую: VSTACK({” “,”#”,”А”,”Б”,”В”…}).

Способ 2: Использование LAMBDA-функции для обработки диапазона

Если нужно обработать несколько строк сразу, оберните формулу в MAP и LAMBDA:

=MAP(A1:A3, LAMBDA(x, TEXTJOIN(",",1,TEXTSPLIT(x,VSTACK({" ","#"},CHAR(SEQUENCE(26)+{64,96})),,1)))

Здесь:

• MAP(A1:A3, ...) применяет функцию ко всем ячейкам в диапазоне A1:A3.
• LAMBDA(x, ...) определяет переменную x для текущей ячейки.

Обратите внимание: функция MAP доступна только в последних версиях Excel. Если у вас возникает ошибка, проверьте обновления или перейдите к третьему способу.

Способ 3: Регулярные выражения через REGEXEXTRACT

Для пользователей Google Таблиц или Excel с подключенными надстройками (например, Power Query) подойдет вариант с регулярными выражениями:

=ARRAYTOTEXT(REGEXEXTRACT(A1,"(d{4})",1))

Как это работает:

• REGEXEXTRACT(A1,"(d{4})",1) ищет в тексте все четырехзначные числа (d означает цифру, {4} – количество повторений).
• ARRAYTOTEXT() преобразует массив найденных чисел в строку с разделителями-запятыми.

Важно: В Excel для Windows эта формула не сработает без дополнительных настроек. Но если вы работаете в Google Таблицах или используете Power Query (раздел «Данные» → «Из текста/CSV»), метод станет вашим спасением.

Возможные ошибки и как их избежать

• «Неверное имя функции». Убедитесь, что ваш Excel обновлен до версии 2206 или новее. Старые версии не поддерживают TEXTSPLIT или VSTACK.
• Числа разной длины. Если в данных встречаются не только четырехзначные коды, измените регулярное выражение: например, (d{3,5}) для чисел от 3 до 5 цифр.
• Лишние символы в результате. Проверьте, все ли буквы и разделители учтены в VSTACK. Если в тексте есть цифры в другом контексте (например, «Цена1000»), добавьте разделитель перед числом: замените пробел на {" ","#","Цена"}.

Кстати, если формулы кажутся слишком сложными, попробуйте альтернативу:

1. Скопируйте данные в Блокнот.
2. Используйте поиск и замену (Ctrl+H): замените все символы, кроме # и цифр, на пробелы.
3. Верните данные в Excel и разделите текст по столбцам через меню «Данные» → «Текст по столбцам».

Подводя итог: даже если задача выглядит запутанной, Excel предлагает гибкие инструменты для её решения. Выберите метод, который соответствует вашей версии программы и уровню комфорта с формулами. Если что-то не получается – смело экспериментируйте с разделителями или пишите в комментариях, помогу разобраться!