Как убрать лишние пробелы в Excel
Два одинаковых на вид значения не совпадают в ВПР, ЕСЛИ выдаёт «ложь» там, где должна быть «истина» — почти всегда причина в невидимых лишних пробелах. Разберём, как их убрать: от простой функции СЖПРОБЕЛЫ до неразрывного пробела, который она не видит.
Опубликовано
Пробелы, которые не видно, но которые мешают формулам
Лишний пробел в ячейке чаще всего появляется не оттого, что кто-то нажал пробел специально, а из-за копирования данных из других источников: сайта, PDF, выгрузки из 1С или другой учётной системы. Такой пробел ничем не отличается визуально от обычного, но формулы сравнения (ЕСЛИ, ВПР, СЧЁТЕСЛИ) считают значения с пробелом и без него разными строками.
Быстрый способ — функция СЖПРОБЕЛЫ
Функция СЖПРОБЕЛЫ (в английской версии — TRIM)
убирает пробелы по краям текста и «схлопывает» несколько
пробелов подряд между словами в один:
=СЖПРОБЕЛЫ(A2)— убирает пробелы в начале и конце текста ячейки A2, а также лишние пробелы внутри.
Формулу вводят в соседний столбец, а затем результат копируют и вставляют как значения («Специальная вставка → Значения») поверх исходного столбца, после чего формулу и вспомогательный столбец можно удалить.
Как убрать неразрывный пробел, если СЖПРОБЕЛЫ не помогает
Неразрывный пробел нужно сначала заменить на обычный, а затем уже применять СЖПРОБЕЛЫ:
=СЖПРОБЕЛЫ(ПОДСТАВИТЬ(A2;СИМВОЛ(160);" "))— функция ПОДСТАВИТЬ меняет каждый неразрывный пробел (СИМВОЛ(160)) на обычный, а СЖПРОБЕЛЫ убирает всё лишнее, что осталось.
Если нужно не заменить неразрывный пробел, а полностью его удалить (например, когда пробел стоит внутри числа как разделитель разрядов и число нужно превратить в настоящее число), используют:
=ЗНАЧЕН(ПОДСТАВИТЬ(A2;СИМВОЛ(160);""))— убирает неразрывный пробел и сразу преобразует результат в число.
Как убрать пробелы через «Найти и заменить»
Для быстрой массовой замены без формул подойдёт Ctrl+H:
в поле «Найти» ввести пробел, поле «Заменить на» оставить
пустым и нажать «Заменить все». Способ убирает вообще все
пробелы в выделенном диапазоне — в том числе нужные между
словами, — поэтому годится для чисел, кодов, артикулов, но не
для обычного текста с несколькими словами в ячейке.
Если нужно заменить именно неразрывный пробел, в поле «Найти» такой символ не ввести с клавиатуры напрямую — проще воспользоваться формулой ПОДСТАВИТЬ, как показано выше.
Почему ВПР и СЧЁТЕСЛИ не находят совпадение из-за пробелов
Если формула поиска (ВПР, ПОИСКПОЗ, СЧЁТЕСЛИ) не находит явно существующее значение — прежде чем проверять саму формулу, стоит проверить длину текста в сравниваемых ячейках:
=ДЛСТР(A2)— покажет точную длину текста в ячейке.
Если у двух визуально одинаковых значений длина отличается — значит в одном из них есть лишний пробел (или другой невидимый символ), и его нужно убрать одним из способов выше в обеих сравниваемых таблицах, а не только в одной.
Часто задаваемые вопросы
Почему СЖПРОБЕЛЫ убирает не все пробелы в ячейке?
СЖПРОБЕЛЫ убирает только обычные пробелы (код символа 32) — по краям текста и повторяющиеся между словами, оставляя один. Если пробел в ячейке на самом деле неразрывный (код 160, часто попадает при копировании из интернета или выгрузок из 1С), СЖПРОБЕЛЫ его не трогает. В этом случае нужно сначала заменить неразрывный пробел на обычный: =ПОДСТАВИТЬ(СЖПРОБЕЛЫ(A2);СИМВОЛ(160);"").
Почему ВПР или СЧЁТЕСЛИ не находят совпадение, хотя текст в ячейках выглядит одинаково?
Чаще всего причина в невидимых пробелах в начале или конце значения — визуально текст в двух ячейках выглядит идентично, но по факту разный. Проверить это легко формулой ДЛСТР: если длина текста в двух «одинаковых» ячейках отличается, значит в одной из них есть лишний пробел.
Как убрать пробел, который используется как разделитель разрядов в числе?
Если число на самом деле хранится как текст с пробелом внутри (например «12 500»), сначала уберите неразрывный пробел, а затем преобразуйте в число: =ЗНАЧЕН(ПОДСТАВИТЬ(A2;СИМВОЛ(160);"")). Если же ячейка и так распознана как число, а пробел между разрядами — это просто визуальное отображение формата, трогать ничего не нужно: само число хранится без пробелов.
Можно ли убрать пробелы сразу во всём столбце, не вводя формулу в каждую ячейку?
Да, через «Найти и заменить» (Ctrl+H): в поле «Найти» ввести пробел, поле «Заменить на» оставить пустым и нажать «Заменить все». Это удалит вообще все пробелы, включая нужные между словами, поэтому способ подходит для чисел и кодов, но не для обычного текста. Для текста надёжнее формула СЖПРОБЕЛЫ в соседнем столбце с последующей вставкой результата как значений.
Что дальше
Невидимые пробелы — одна из самых частых причин, когда формулы поиска и сравнения работают не так, как ожидается. Другие типичные причины разобраны в статье «Частые ошибки формул в Excel и как их избежать».
Чистые данные без ручной зачистки
В шаблонах на сайте поля ввода уже настроены так, чтобы не накапливать лишние пробелы — данные сразу пригодны для формул поиска и сравнения.