Очистка данных от лишних символов: текст против цифр

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

Чтобы быстро удалить числа из текста в Excel, используйте вложенную функцию ПОДСТАВИТЬ (заменяет цифры 0–9 на пустоту) или инструмент «Мгновенное заполнение» (Ctrl+E). Если нужно наоборот — извлечь только цифры, примените комбинацию функций СЦЕПИТЬ и ЕСЛИ (для новых версий) или простой макрос VBA. Ниже приведены готовые решения для разных задач без лишней теории.

Как убрать цифры и оставить только текст

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

Универсальный метод: формула ПОДСТАВИТЬ

Работает во всех версиях Excel. Суть метода — последовательно заменить каждую цифру на пустую строку.

Вставьте формулу в соседнюю ячейку (предположим, данные в A1):

=ПОДСТАВИТЬ(ПОДСТАВИТЬ(ПОДСТАВИТЬ(ПОДСТАВИТЬ(ПОДСТАВИТЬ(ПОДСТАВИТЬ(ПОДСТАВИТЬ(ПОДСТАВИТЬ(ПОДСТАВИТЬ(ПОДСТАВИТЬ(A1;"0";"");"1";"");"2";"");"3";"");"4";"");"5";"");"6";"");"7";"");"8";"");"9";"")

Результат: Из строки Товар123 получится Товар.

Чтобы не писать формулу вручную для каждой строки, протяните маркер заполнения вниз до конца таблицы. После этого скопируйте столбец с результатами и вставьте их как «Значения», чтобы убрать зависимость от исходных данных.

Быстрый способ: Мгновенное заполнение (Flash Fill)

Если у вас Excel 2013 или новее, формулы не нужны.

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

Этот метод идеален для разовых задач и не перегружает файл вычислениями.

Как извлечь только цифры из ячейки

Иногда требуется получить числовое значение из смешанной строки (например, вес 5кг → 5 или код заказа A-100-B → 100).

Для современных версий (Excel 365, 2019+)

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

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

Примечание: В старых версиях эту формулу нужно подтверждать сочетанием Ctrl + Shift + Enter.

Для любых версий: Макрос VBA

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

Нажмите Alt + F11, выберите Insert > Module.
Вставьте код:

Function GetNumbers(rng As String) As String
    Dim i As Integer
    For i = 1 To Len(rng)
        If IsNumeric(Mid(rng, i, 1)) Then GetNumbers = GetNumbers & Mid(rng, i, 1)
    Next i
End Function

Вернитесь в таблицу и используйте формулу =GetNumbers(A1).

Функция возвращает текст. Если нужно производить математические операции с результатом, оберните формулу в функцию ЗНАЧЕН: =ЗНАЧЕН(GetNumbers(A1)).

Разделение текста и чисел на разные столбцы

Когда нужно разбить Артикул55 на два столбца: Артикул и 55.

Метод	Когда использовать	Сложность
Мгновенное заполнение	Данные имеют единый шаблон (текст всегда слева, цифры справа)	Низкая
Формулы	Требуется динамическое обновление при изменении исходника	Средняя
Power Query	Обработка тысяч строк, сложная логика очистки	Высокая

Использование формул для разделения

Если цифры находятся строго в конце строки:

Для текста: =ЛЕВСИМВ(A1;ДЛСТР(A1)-СУММПРОИЗВ(--ЕЧИСЛО(--ПСТР(A1;СТРОКА(ДВССЫЛ("1:"&ДЛСТР(A1)));1))))
Для чисел: =ПРАВСИМВ(A1;СУММПРОИЗВ(--ЕЧИСЛО(--ПСТР(A1;СТРОКА(ДВССЫЛ("1:"&ДЛСТР(A1)));1))))

Эти формулы подсчитывают количество цифр в строке и отсекают нужную часть текста.

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

#ЗНАЧ! или #ИМЯ? Проверьте разделители в формуле. В русской локали Excel аргументы разделяются точкой с запятой (;), а не запятой.
Лимит символов. Формулы с функцией ДВССЫЛ (INDIRECT) могут не работать, если длина строки превышает 255 символов. В таких случаях используйте Power Query или макрос.
Лишние пробелы. После удаления цифр часто остаются двойные пробелы. Добавьте функцию СЖПРОБЕЛЫ (TRIM) вокруг основной формулы для чистоты данных.

FAQ

Можно ли удалить цифры, используя «Найти и заменить»? Нет, стандартный поиск не поддерживает маски вроде «любая цифра». Он ищет только конкретные символы (например, только "1" или только "5"), поэтому придется делать 10 замен вручную, что неудобно.

Как извлечь десятичные дроби (например, 12.5 из "Вес 12.5 кг")? Стандартные формулы извлечения цифр уберут точку, превратив 12.5 в 125. Для сохранения дробной части лучше использовать макрос VBA, добавив условие проверки на точку или запятую, либо инструмент Power Query с разделением по типу символов.

Работают ли эти методы в Google Таблицах? Да, синтаксис формул (SUBSTITUTE, REGEXREPLACE) в Google Таблицах аналогичен. Более того, в них есть функция REGEXREPLACE(A1; "\d"; ""), которая удаляет все цифры одной командой, что гораздо удобнее длинных формул Excel.