Функция СЖПРОБЕЛЫ в Excel (TRIM): как работает, пример и формула под вашу таблицу
Синтаксис в русском Excel
В английском Excel, Google Таблицах и внутри файла .xlsx
Аргументы
текст | строка с лишними пробелами |
Пример
| A | B | |
|---|---|---|
| 1 | Клиент | |
| 2 | ООО «Север» | ООО «Север» |
Результат в ячейке B2 посчитан LibreOffice на сервере Формулника.
Частые ошибки
это неразрывный пробел: ПОДСТАВИТЬ(A2; СИМВОЛ(160); " ")
Где работает
| Программа | СЖПРОБЕЛЫ | |
|---|---|---|
| Excel 2010 | есть | |
| Excel 2013 | есть | |
| Excel 2016 | есть | |
| Excel 2019 | есть | |
| Excel 2021 | есть | |
| Excel 2024 | есть | |
| Excel 365 | есть | |
| LibreOffice Calc | есть | |
| Google Таблицы | есть | |
| Яндекс Таблицы | есть | |
| Р7-Офис | есть | |
| МойОфис | есть | |
| MOffice Sheets | есть | проверено |
Похожие функции
СЖПРОБЕЛЫ и невидимый мусор: неразрывные пробелы, переносы строк, числа-текст
СЖПРОБЕЛЫ убирает только обычный пробел с кодом 32: в начале, в конце и повторы внутри. Неразрывный пробел (код 160), который приносят выгрузки из 1С, банков и сайтов, она не трогает, как и переносы строк. Поэтому надёжная чистка — связка: ПОДСТАВИТЬ меняет код 160 на обычный пробел, ПЕЧСИМВ убирает переносы, СЖПРОБЕЛЫ сжимает остальное.
По шагам
- Проверьте, что мешает:
=ДЛСТР(A2)показывает длину,=КОДСИМВ(ПРАВСИМВ(A2;1))— код последнего символа (32 — пробел, 160 — неразрывный, 10 — перенос). - Вставьте рядом пустой столбец и напишите формулу очистки (в подсценарии ниже — универсальная).
- Протяните её до конца данных, сверьте пару строк.
- Скопируйте столбец и вставьте поверх исходного как значения, вспомогательный столбец удалите.
Универсальная очистка названия контрагента
После копирования из банк-клиента «ООО Север » с двойным пробелом и неразрывным пробелом в конце не находится через ВПР. Формула ниже делает всё за один проход. После неё ВПР и СЧЁТЕСЛИ по названиям перестают давать «невидимые» #Н/Д и нули.
=СЖПРОБЕЛЫ(ПЕЧСИМВ(ПОДСТАВИТЬ(A2;СИМВОЛ(160);" ")))Сумма «12 500,00» не считается
В выгрузках разряды отделяет неразрывный пробел, и Excel видит в ячейке текст: сумма по столбцу равна 0. Здесь пробелы нужно не сжать, а удалить полностью — ПОДСТАВИТЬ с пустой строкой убирает и обычные, и неразрывные, а два минуса превращают результат в число. Если в выгрузке десятичная точка, добавьте ещё одну замену точки на запятую.
=--ПОДСТАВИТЬ(ПОДСТАВИТЬ(A2;СИМВОЛ(160);"");" ";"")Проверить весь столбец до загрузки в учётную систему
Перед загрузкой справочника посчитайте, сколько строк содержат лишние пробелы: сравните длину строки до и после чистки. Ненулевой результат — сигнал, что столбец надо прогнать через формулу из первого подсценария.
=СУММПРОИЗВ(--(ДЛСТР(A2:A900)<>ДЛСТР(СЖПРОБЕЛЫ(A2:A900))))Сравнить два справочника, не меняя их
Бывает, что исходные файлы трогать нельзя: это выгрузка контрагента или архивная версия. Тогда чистку делают прямо внутри поиска — искомое значение оборачивают в СЖПРОБЕЛЫ, а справочник предварительно приводят в порядок вспомогательным столбцом. Так ВПР сравнивает очищенные строки, а исходники остаются нетронутыми для аудита.
=ВПР(СЖПРОБЕЛЫ(A2);Справочник!$A$2:$C$900;3;0)Какой символ чем убрать
| Символ | Код | Чем убрать |
|---|---|---|
| Обычный пробел (лишний) | 32 | СЖПРОБЕЛЫ |
| Неразрывный пробел | 160 | =ПОДСТАВИТЬ(A2;СИМВОЛ(160);" ") |
| Перенос строки внутри ячейки | 10 | ПЕЧСИМВ или ПОДСТАВИТЬ с СИМВОЛ(10) |
| Табуляция | 9 | ПЕЧСИМВ |
| Все пробелы совсем | 32 | =ПОДСТАВИТЬ(A2;" ";"") |
Частые ошибки
- СЖПРОБЕЛЫ отработала, а ВПР всё равно не находит — в данных неразрывный пробел. Добавьте ПОДСТАВИТЬ с СИМВОЛ(160), как в первом подсценарии.
- Очищенный столбец пропал после удаления исходного — формула ссылалась на удалённые ячейки. Сначала вставьте результат как значения, потом удаляйте исходник.
- Из ФИО пропали все пробелы — использовали ПОДСТАВИТЬ с пустой строкой вместо СЖПРОБЕЛЫ. Для текста пробелы сжимают, для чисел — удаляют.
Вопросы и ответы
Почему СЖПРОБЕЛЫ не удаляет пробелы?
Скорее всего, это неразрывные пробелы с кодом 160. Проверьте =КОДСИМВ(ПСТР(A2;7;1)) на позиции пробела и замените их через ПОДСТАВИТЬ.
Как убрать пробелы в числах в Excel?
Для разового случая — «Найти и заменить»: в поле «Найти» вставьте скопированный из ячейки пробел, поле «Заменить» оставьте пустым. Для регулярной выгрузки — формула из второго подсценария.
Можно ли почистить столбец без вспомогательного столбца?
Формулой — нет, она не может менять свою же ячейку. Без формул помогает «Найти и заменить» или Power Query: «Преобразовать → Формат → Обрезать».
Почему после СЖПРОБЕЛЫ ВПР по кодам всё равно даёт #Н/Д?
Пробелы ушли, но тип данных остался: в одной таблице код — текст, в другой — число. СЖПРОБЕЛЫ всегда возвращает текст, даже из числа. Если ищете по числовому столбцу, добавьте -- перед очищенным значением.
Как СЖПРОБЕЛЫ называется по-английски и есть ли она в LibreOffice?
TRIM. В LibreOffice и Р7-Офис функция есть под тем же русским именем.
Можно ли сразу получить очищенный справочник?
Загрузите файл в Формулник и попросите убрать лишние и неразрывные пробелы — он покажет, сколько ячеек изменилось, и вернёт книгу с остальными данными без изменений.