Как убрать пробелы в Excel
СЖПРОБЕЛЫ убирает лишние, ПОДСТАВИТЬ — все, а неразрывный пробел не берёт ни то, ни другое.
Коротко
Сначала поймите, какой у вас случай — способы разные и не заменяют друг друга.
Лишние пробелы в тексте — по краям и двойные внутри. =СЖПРОБЕЛЫ(A2): уберёт всё лишнее, но оставит по одному пробелу между словами. Это то, что нужно для ФИО, названий и адресов.
Все пробелы без исключения — в артикуле, номере счёта, в числе из выгрузки. =ПОДСТАВИТЬ(A2;" ";""). Результат будет текстом, даже если это цифры: чтобы получилось число, оберните в =ЗНАЧЕН(...).
Пробел, который не убирается ничем. Скопировали с сайта или из 1С — на вид пробел, а СЖПРОБЕЛЫ его не берёт и ВПР по такой ячейке ничего не находит. Это неразрывный пробел, код 160. Убирается только явно: =ПОДСТАВИТЬ(A2;ЮНИСИМВ(160);"").
Почему ЮНИСИМВ, а не СИМВОЛ. В большинстве разборов пишут СИМВОЛ(160), и на русской Windows это работает. Но СИМВОЛ и КОДСИМВ считают по кодовой странице системы: в другой системе — в Google Таблицах, на маке, в LibreOffice — тот же СИМВОЛ(160) вернёт не тот символ, и замена молча не сработает. ЮНИСИМВ и ЮНИКОД считают по коду Unicode и отвечают одинаково везде. Они есть с Excel 2013 и в Google Таблицах.
А пробелов и нет. Если 1 234 567 показывается с промежутками, но в строке формул стоит 1234567 — это разделитель разрядов из формата ячейки. Убирать нечего: Ctrl+1 → Числовой и снять галочку «Разделитель групп разрядов».
То же самое формулой в Excel
=СЖПРОБЕЛЫ(A2) — убрать пробелы по краям и двойные внутри, оставив по одному=ПОДСТАВИТЬ(A2;" ";"") — убрать все пробелы; результат — текст=ЗНАЧЕН(ПОДСТАВИТЬ(A2;" ";"")) — убрать пробелы и получить число=ПОДСТАВИТЬ(A2;ЮНИСИМВ(160);"") — неразрывный пробел из интернета и 1С; ЮНИСИМВ — с Excel 2013=ЗНАЧЕН(ПОДСТАВИТЬ(ПОДСТАВИТЬ(A2;ЮНИСИМВ(160);"");" ";"")) — сумма из банковской выгрузки: оба пробела разом и на выходе число=ПОДСТАВИТЬ(A2;СИМВОЛ(160);"") — старый способ: работает там, где кодовая страница Windows-1251=ПЕЧСИМВ(СЖПРОБЕЛЫ(A2)) — заодно выбросить непечатаемые символы: переносы, табуляцию=ДЛСТР(A2)-ДЛСТР(СЖПРОБЕЛЫ(A2)) — сколько лишних пробелов в ячейке — проверка перед ВПР=ЮНИКОД(ПРАВСИМВ(A2)) — что за символ в конце: 32 — обычный пробел, 160 — неразрывныйФайл с грязными строками и четырьмя очистками
Восемь строк как из выгрузки: двойные пробелы, неразрывные, суммы-текст. Рядом СЖПРОБЕЛЫ, снятие всех пробелов, снятие неразрывных и превращение в число, а два столбца длины показывают, что осталось после каждой.
Файл
ubrat-probely.xlsx · формулы работают в Excel 2016 и новее
и в Google Таблицах · без макросов · формулы проходят автопроверку
перед публикацией
Пошагово
- Проверьте, что вы вообще видите: встаньте на ячейку и посмотрите в строку формул. Если там пробелов нет — это формат, и формулы не нужны.
- В соседнем столбце напишите
=СЖПРОБЕЛЫ(A2)и протяните вниз. - Не помогло — значит, пробел неразрывный:
=ПОДСТАВИТЬ(A2;СИМВОЛ(160);""), а лучше сразу обе замены в одной формуле. - Нужны числа, а не текст: оберните всё в
=ЗНАЧЕН(...). Признак того, что перед вами текст, — значение прижато к левому краю и не суммируется. - Скопируйте столбец с формулами и вставьте обратно как значения (Ctrl+Alt+V → «Значения»), затем удалите исходный.
Быстрый способ без формул — Ctrl+H: в поле «Найти» поставить пробел, поле «Заменить на» оставить пустым, «Заменить всё». Он убирает все пробелы разом, включая нужные между словами, поэтому годится для чисел и артикулов, но не для ФИО. Неразрывный пробел вводится в поле поиска как Alt+0160 на цифровом блоке.
Где обычно ломается
- ВПР не находит совпадение. Классика: в одной таблице «Иванов », в другой «Иванов». Оберните оба аргумента в СЖПРОБЕЛЫ или очистите столбцы заранее — разбор ошибок ВПР на странице функции.
- СЖПРОБЕЛЫ не убирает неразрывный пробел. Это не недоработка: для Excel символ 160 — не пробел. Проверить просто:
=ЮНИКОД(ПРАВСИМВ(A2))вернёт 160. СтарыйКОДСИМВна том же символе может вернуть 32 или 194 — он зависит от кодовой страницы системы. - После ПОДСТАВИТЬ число не считается. Функция всегда возвращает текст. Сумма по такому столбцу даст ноль, пока не обернёте в ЗНАЧЕН или не умножите на единицу.
- ЗНАЧЕН падает с #ЗНАЧ! на дробях. Если в выгрузке десятичная точка, а у вас запятая, сначала замените:
=ЗНАЧЕН(ПОДСТАВИТЬ(A2;".";",")). - Ctrl+H по всему листу. Замена без выделенного диапазона идёт по всему листу и убирает пробелы в заголовках и комментариях тоже. Выделяйте столбец перед заменой.
- Пробел в конце числа, набранного руками. Он делает число текстом молча — в углу появляется зелёный треугольник. Это тот же случай, что и «эксель не считает формулу»: разбор причин.