Быстрая очистка текста в Excel от мусора

Иван Корнев·21.05.2024·⏱4 мин

Чтобы убрать буквы, знаки препинания и лишние символы в Excel, используйте функцию ПОДСТАВИТЬ для конкретных знаков или комбинацию СЖПРОБЕЛЫ и ТЕКСТПОСЛЕ для сложной очистки. Для массового удаления всех нецифровых символов эффективнее всего применить макрос VBA или формулу массива. Эти методы позволяют превратить грязные данные (например, «Тел: +7 (999)...») в чистый формат за секунды.

Извлечение только цифр из ячеек

Частая задача — оставить в ячейке только числа, удалив весь текст. Это необходимо при обработке телефонных номеров, артикулов или счетов.

Способ 1: Формула для новых версий Excel (365, 2021)

Если у вас современная версия, используйте связку функций для фильтрации символов. Формула перебирает каждый знак строки и оставляет только те, что являются числами:

=ТЕКСТСОЕДИНИТЬ("";;ЕСЛИ(ЕЧИСЛО(--ПОДСТР(A1;СТРОКА(ДВССЫЛ("1:"&ДЛСТР(A1)));1));ПОДСТР(A1;СТРОКА(ДВССЫЛ("1:"&ДЛСТР(A1)));1);""))

Примечание: В английской версии замените имена функций на TEXTJOIN, MID, ROW, INDIRECT, LEN, ISNUMBER.

Способ 2: Универсальная формула через ПОДСТАВИТЬ

Для старых версий или если нужно удалить конкретный набор букв, вложите функции ПОДСТАВИТЬ. Однако перечислять весь алфавит неудобно. Лучше использовать такой подход для удаления латиницы и кириллицы выборочно:

=ПОДСТАВИТЬ(ПОДСТАВИТЬ(ПОДСТАВИТЬ(A1;"a";"");"b";"");"c";"")

Этот метод подходит, если набор лишних символов ограничен (например, только валюта или определенные приставки).

Лайфхак с «Мгновенным заполнением»: Выделите столбец с данными. В соседней ячейке вручную введите желаемый результат (только цифры). Нажмите Ctrl + E. Excel автоматически распознает паттерн и очистит весь столбец без формул.

Удаление знаков препинания и спецсимволов

Знаки вроде ! @ # $ % ^ & * ( ) - _ часто мешают сортировке и сводным таблицам.

Глубокая очистка функцией ПОДСТАВИТЬ

Для удаления нескольких типов символов вкладывайте функции друг в друга. Лимит вложений в одной формуле — до 64 уровней, чего хватит для большинства задач:

=ПОДСТАВИТЬ(ПОДСТАВИТЬ(ПОДСТАВИТЬ(ПОДСТАВИТЬ(A1;"-";"");"(";"" );")";"");" ";"" )

Эта формула удалит дефисы, скобки и пробелы из номера телефона.

Использование функции СИМВОЛ для непечатных знаков

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

СИМВОЛ(10) — перенос строки (Line Feed).
СИМВОЛ(13) — возврат каретки (Carriage Return).
СИМВОЛ(160) — неразрывный пробел (часто приходит из веба).

Формула для полной зачистки:

=СЖПРОБЕЛЫ(ПОДСТАВИТЬ(ПОДСТАВИТЬ(ПОДСТАВИТЬ(A1;СИМВОЛ(10);"");СИМВОЛ(13);"");СИМВОЛ(160);" "))

Работа с пробелами и структурой текста

Лишние пробелы — самая частая причина ошибок при поиске (ВПР) и сравнении данных.

СЖПРОБЕЛЫ(текст): Удаляет все пробелы, кроме одиночных между словами, и убирает пробелы в начале/конце строки.
ТЕКСТРАЗДЕЛИТЬ: Позволяет разбить строку по разделителю (например, по пробелу или дефису) и вытащить нужную часть.

Пример: Нужно получить код города из номера «8 (495) 123-45-67».

=ТЕКСТПОСЛЕ(ТЕКСТДО(ТЕКСТПОСЛЕ(A1;"(");")");" ")

Эта цепочка сначала вырежет текст между скобками, а затем очистит лишнее.

Задача	Оптимальное решение	Пример результата
Убрать все пробелы	`=ПОДСТАВИТЬ(A1;" ";"")`	"Иван Иванов" → "ИванИванов"
Нормализовать пробелы	`=СЖПРОБЕЛЫ(A1)`	" Текст " → "Текст"
Оставить только цифры	Мгновенное заполнение (`Ctrl+E`) или макрос	"Арт. 123-Б" → "123"
Удалить переносы строк	`=ПОДСТАВИТЬ(A1;СИМВОЛ(10);"")`	Многострочный текст → Одна строка

Автоматизация через макрос VBA

Если нужно обработать тысячи строк или логику очистки нельзя описать формулой (например, удаление всех символов, кроме цифр и плюса), используйте макрос. Он работает мгновенно даже на больших массивах.

Нажмите Alt + F11, чтобы открыть редактор VBA.
В меню выберите Insert → Module.
Вставьте следующий код:

Sub CleanNonNumeric()
    Dim cell As Range
    Dim i As Integer
    Dim result As String
    Dim char As String
    
    ' Проход по выделенным ячейкам
    For Each cell In Selection
        If Not IsEmpty(cell) Then
            result = ""
            ' Перебор каждого символа в ячейке
            For i = 1 To Len(cell.Value)
                char = Mid(cell.Value, i, 1)
                ' Оставляем только цифры и знак плюс (для телефонов)
                If char Like "[0-9+]" Then
                    result = result & char
                End If
            Next i
            cell.Value = result
        End If
    Next cell
End Sub

Закройте редактор, выделите диапазон ячеек и нажмите Alt + F8, выберите CleanNonNumeric и нажмите Выполнить.

Макросы необратимо меняют данные. Перед запуском обязательно сохраните копию файла или продублируйте исходный столбец. Файл необходимо сохранить в формате .xlsm (с поддержкой макросов).

Частые ошибки при очистке

#ЗНАЧ! после вставки формулы: Проверьте разделители. В русской локали аргументы разделяются точкой с запятой (;), в английской — запятой (,).
Данные не очищаются полностью: Возможно, в ячейках присутствуют неразрывные пробелы (код 160). Обычная функция СЖПРОБЕЛЫ их не всегда видит, нужно явно заменять СИМВОЛ(160).
Числа остаются текстом: После очистки формат ячейки может остаться текстовым. Выделите столбец, перейдите в Данные → Текст по столбцам и нажмите «Готово», чтобы преобразовать их в числа.

FAQ

Как удалить первый символ в ячейке? Используйте формулу: =ПРАВСИМВ(A1; ДЛСТР(A1)-1). Она берет все символы справа, кроме первого.

Можно ли удалить все буквы сразу одной формулой? Стандартной функции «удалить все буквы» нет. Самый быстрый способ без макросов — использовать «Мгновенное заполнение» (Ctrl + E), введя пример правильного значения вручную.

Почему формула не работает в онлайн-версии Excel? Некоторые функции работы с массивами и старые функции могут отличаться. Для веб-версии лучше использовать простые вложенные ПОДСТАВИТЬ или скрипты Office JS, если доступна расширенная функциональность.