Убрать лишние пробелы в ячейках Excel — в начале, в конце и двойные между словами — проще всего функцией =СЖПРОБЕЛЫ(A2): она оставляет между словами ровно по одному пробелу. Чтобы удалить вообще все пробелы, например в числах «1 200 500», используйте =ПОДСТАВИТЬ(A2;" ";""). Если пробелы не удаляются ни тем, ни другим, это неразрывные пробелы (символ 160) — их убирает =СЖПРОБЕЛЫ(ПОДСТАВИТЬ(A2;СИМВОЛ(160);" ")). А прямо в исходных ячейках, без формул и дополнительного столбца, все лишние пробелы удаляет в 2 клика процедура !SEMTools.
Между слов лишние пробелы еще заметны по увеличивающемуся между ними расстоянию, пробел с начала строки тоже легко выявить, т.к. строка смещается от левой границы ячейки. А вот если пробел в строке идет после последнего символа, или в ячейке в принципе одни пробелы, их не видно вовсе.
Чем мешают лишние пробелы в Excel
Наличие лишних пробелов может помешать работе в следующих случаях:
- Требуется сравнить ячейки между собой. Это может быть как поиск повторяющихся значений внутри диапазона, так и сравнение двух списков чтобы проверить наличие схожих позиций в них;
- При построении сводных таблиц строки "схлопываются", если являются полностью идентичными, и разница в один пробел не позволит им "схлопнуться". Алгоритм построения сводных таблиц также сравнивает ячейки между собой;
- С помощью "ВПР" или сочетания ИНДЕКС/ПОИСКПОЗ можно "подтянуть" данные из одной таблицы в другую. Эти функции тоже сравнивают ячейки при поиске и учитывают разницу в виде пробелов.
Обычно лишние пробелы не несут никакой пользы, и популярная задача - удалить их из текста. Но удалить именно лишние, чтобы фразы не склеивались в одно слово.
Убрать лишние пробелы заменой и функцией СЖПРОБЕЛЫ
От обычных пробелов можно избавиться штатными средствами: процедурой «Найти и заменить» или функцией СЖПРОБЕЛЫ. Рассмотрим их подробнее.
Убрать двойные пробелы через «Найти и заменить»
Оговорюсь сразу — самый большой недостаток этого подхода в том, что единичные пробелы в начале и конце строк он не удалит. И если для вас они тоже лишние, вам этот вариант не подходит — смотрите варианты ниже.
Тем не менее, на многих сайтах рекомендуется этот способ. Вызвать процедуру замены горячими клавишами Ctrl+H, вбить в поля «Найти» и «Заменить на» 2 пробела и 1 пробел соответственно и произвести замену, нажав кнопку "Заменить все".
Важно делать это неоднократно — до тех пор, пока процедура не выведет сообщение:
Почему так? Потому что всегда есть вероятность, что между словами было 3 и более пробелов.
Функция СЖПРОБЕЛЫ: как пользоваться
СЖПРОБЕЛЫ — полезная функция, но она требует создания дополнительного столбца, применения функции в нем и далее удаления исходного. Формулы на её основе удаляют все лишние пробелы. Сюда входят пробелы:
- после последнего слова;
- перед первым словом;
- два и более пробелов между словами.
| A | B | C | D | |
|---|---|---|---|---|
| 1 | Текст | СЖПРОБЕЛЫ | Длина до | Длина после |
| 2 | Иванов Иван | Иванов Иван=СЖПРОБЕЛЫ(A2) | 13=ДЛСТР(A2) | 11=ДЛСТР(B2) |
| 3 | Петров Пётр | Петров Пётр=СЖПРОБЕЛЫ(A3) | 13=ДЛСТР(A3) | 11=ДЛСТР(B3) |
| 4 | ООО «Ромашка» | ООО «Ромашка»=СЖПРОБЕЛЫ(A4) | 15=ДЛСТР(A4) | 13=ДЛСТР(B4) |
| 5 | Казань | Казань=СЖПРОБЕЛЫ(A5) | 6=ДЛСТР(A5) | 6=ДЛСТР(B5) |
Пробелы не видны, поэтому сравните длину строк функцией ДЛСТР: в A2 два пробела в начале (13 символов), после СЖПРОБЕЛЫ их нет (11). В A4 три пробела между словами сжались до одного.
В английском Excel: =TRIM(A2)
Синтаксис функции простой: в скобках — ячейка или текст, других аргументов нет. Формулу вводят в соседний столбец, протягивают вниз, затем заменяют формулы значениями и переносят результат на место исходных данных.
Удалить все пробелы в ячейке, в том числе в числах
Иногда лишние все пробелы: в телефонах, артикулах, числах с разделителями разрядов, скопированных как текст. Их удаляет ПОДСТАВИТЬ с заменой пробела на пустую строку, а два минуса перед формулой превращают результат в число:
| A | B | C | |
|---|---|---|---|
| 1 | Исходное | Без пробелов | Как число |
| 2 | 1 200 500 | 1200500=ПОДСТАВИТЬ(A2;" ";"") | 1200500=--ПОДСТАВИТЬ(A2;" ";"") |
| 3 | +7 912 345 67 89 | +79123456789=ПОДСТАВИТЬ(A3;" ";"") | 79123456789=--ПОДСТАВИТЬ(A3;" ";"") |
| 4 | 48 000 | 48000=ПОДСТАВИТЬ(A4;" ";"") | 48000=--ПОДСТАВИТЬ(A4;" ";"") |
| 5 | A 10 24 | A1024=ПОДСТАВИТЬ(A5;" ";"") | #ЗНАЧ!=--ПОДСТАВИТЬ(A5;" ";"") |
ПОДСТАВИТЬ возвращает текст. Для чисел добавьте два минуса (столбец C); артикул с буквой числом не станет и вернёт #ЗНАЧ!.
В английском Excel: =SUBSTITUTE(A2," ","")
Без формулы — через «Найти и заменить»: Ctrl+H, в поле «Найти» один пробел, поле «Заменить на» пустое, «Заменить все».
Убрать неразрывные пробелы в Excel
Если пробел не удаляется ни СЖПРОБЕЛЫ, ни заменой, скорее всего, это неразрывный пробел — символ с кодом 160. Он выглядит как обычный, но для Excel это другой символ. Чаще всего такие пробелы приходят из 1С, банковских выписок и с сайтов, особенно в числах. Проверить код подозрительного символа можно формулой =КОДСИМВ(ПСТР(A2;3;1)), где 3 — позиция символа.
| A | B | C | |
|---|---|---|---|
| 1 | Из выгрузки | Обычная замена | Замена символа 160 |
| 2 | 12 500 ₽ | 12 500 ₽=ПОДСТАВИТЬ(A2;" ";"") | 12500₽=ПОДСТАВИТЬ(A2;СИМВОЛ(160);"") |
| 3 | 3 200 | 3 200=ПОДСТАВИТЬ(A3;" ";"") | 3200=ПОДСТАВИТЬ(A3;СИМВОЛ(160);"") |
| 4 | Москва | Москва =ПОДСТАВИТЬ(A4;" ";"") | Москва=ПОДСТАВИТЬ(A4;СИМВОЛ(160);"") |
Замена обычного пробела (столбец B) ничего не меняет. Замена СИМВОЛ(160) в столбце C убирает неразрывные пробелы. Чтобы не склеить слова, меняйте символ 160 на обычный пробел и сжимайте: =СЖПРОБЕЛЫ(ПОДСТАВИТЬ(A2;СИМВОЛ(160);" ")).
В английском Excel: =SUBSTITUTE(A2,CHAR(160),"")
Через «Найти и заменить» неразрывный пробел вводят в поле «Найти» с цифровой клавиатуры: зажмите Alt и наберите 0160.
Удалить все лишние пробелы в 2 клика с !SEMTools
Наверняка многим хотелось бы удалять лишние пробелы без применения функций и создания дополнительных столбцов, меняя строки на их же месте.
Для таких случаев и была создана процедура удаления лишних пробелов в !SEMTools. Выделяем список ячеек, жмем на кнопку в меню. Вуаля!
Удалить пробелы только в начале или в конце строки
Довольно гипотетическая, но все же возможная ситуация - когда нужно удалить только пробелы в конце или начале строки, а если есть два или более пробелов подряд в середине строки, такие не удалять. Здесь на помощь могут прийти также регулярные выражения.
Удалить пробелы в конце строки
Вот такое регулярное выражение заменяет пробел или несколько пробелов на пустоту, если они идут в конце строки:
=REGEXREPLACE(A1;"\s+$";"")
Краткая декомпозиция регулярки:
- \s — любой пробельный символ (пробел, табуляция, перенос строки)
- + — один или более раз
- $ — конец строки
| A | B | C | D | |
|---|---|---|---|---|
| 1 | Текст | Без пробелов в конце | Длина до | Длина после |
| 2 | Москва | Москва=REGEXREPLACE(A2;"\s+$";"") | 9=ДЛСТР(A2) | 6=ДЛСТР(B2) |
| 3 | Тверь обл. | Тверь обл.=REGEXREPLACE(A3;"\s+$";"") | 12=ДЛСТР(A3) | 11=ДЛСТР(B3) |
Двойной пробел внутри «Тверь обл.» остался — шаблон \s+$ касается только хвоста строки.
В английском Excel: =REGEXREPLACE(A2,"\s+$","")
Удалить пробелы в начале строки
Аналогично, если задача удалить только пробелы в начале строк, а другие лишние пробелы не трогать. Синтаксис крайне схож, но здесь используется каретка "^", - символ начала строки:
=REGEXREPLACE(A1;"^\s+";"")
| A | B | C | D | |
|---|---|---|---|---|
| 1 | Текст | Без пробелов в начале | Длина до | Длина после |
| 2 | Москва | Москва=REGEXREPLACE(A2;"^\s+";"") | 9=ДЛСТР(A2) | 6=ДЛСТР(B2) |
| 3 | Тверь обл. | Тверь обл.=REGEXREPLACE(A3;"^\s+";"") | 13=ДЛСТР(A3) | 11=ДЛСТР(B3) |
Знак ^ привязывает шаблон к началу строки, поэтому пробелы между словами не меняются.
В английском Excel: =REGEXREPLACE(A2,"^\s+","")
Напомню, в Excel 365 есть встроенная функция РЕГЗАМЕНИТЬ (REGEXREPLACE) с тем же синтаксисом. В более ранних версиях регулярных выражений по умолчанию нет, но функция REGEXREPLACE появится после установки надстройки !SEMTools — использовать её на листе можно без ограничений даже в бесплатной версии.
Почему СЖПРОБЕЛЫ не удаляет пробелы — частые ошибки
- В тексте неразрывные пробелы. СЖПРОБЕЛЫ убирает только обычный пробел (код 32). Символ 160 сначала заменяют обычным пробелом — см. раздел про неразрывные пробелы.
- Это не пробел, а перенос строки или табуляция. Их удаляет функция ПЕЧСИМВ (CLEAN): =СЖПРОБЕЛЫ(ПЕЧСИМВ(A2)).
- Пробелы в исходных ячейках остались. Формула возвращает результат в своей ячейке и не меняет исходную. Замените формулы значениями и перенесите результат на место данных.
- Число после очистки не суммируется. ПОДСТАВИТЬ и СЖПРОБЕЛЫ возвращают текст. Добавьте два минуса перед формулой или преобразуйте числа, сохранённые как текст.
| A | B | C | D | E | |
|---|---|---|---|---|---|
| 1 | Текст | Код 5-го символа | Длина | Очищено | Длина |
| 2 | Иван Петров | 160=КОДСИМВ(ПСТР(A2;5;1)) | 13=ДЛСТР(A2) | Иван Петров=СЖПРОБЕЛЫ(ПОДСТАВИТЬ(A2;СИМВОЛ(160);" ")) | 11=ДЛСТР(D2) |
КОДСИМВ показывает, что «пробел» на 5-й позиции — символ 160. ПОДСТАВИТЬ превращает такие символы в обычные пробелы, а СЖПРОБЕЛЫ убирает лишние. Переносы строк внутри ячейки так же убирает =СЖПРОБЕЛЫ(ПЕЧСИМВ(A2)).
В английском Excel: =TRIM(SUBSTITUTE(A2,CHAR(160)," "))
Лишние пробелы в Excel — частые вопросы
Как убрать пробелы в начале и в конце ячейки?
Функцией =СЖПРОБЕЛЫ(A2) — она удаляет пробелы по краям и оставляет по одному пробелу между словами. Только начальные или только конечные пробелы убирают регулярным выражением: =REGEXREPLACE(A2;"^\s+";"") или =REGEXREPLACE(A2;"\s+$";"").
Как пользоваться функцией СЖПРОБЕЛЫ?
Введите в соседний столбец =СЖПРОБЕЛЫ(A2), протяните формулу вниз и замените формулы значениями. У функции один аргумент — ячейка или текст.
Как удалить все пробелы в ячейке?
Формулой =ПОДСТАВИТЬ(A2;" ";"") или через Ctrl+H: найти пробел, заменить на пустоту. Для неразрывных пробелов — =ПОДСТАВИТЬ(A2;СИМВОЛ(160);"").
Почему пробелы не удаляются?
Чаще всего это неразрывные пробелы (символ 160) из выгрузок. СЖПРОБЕЛЫ и обычная замена их не видят — используйте =СЖПРОБЕЛЫ(ПОДСТАВИТЬ(A2;СИМВОЛ(160);" ")).
Удалить лишние пробелы — в начале, в конце, двойные между словами и неразрывные — можно в 2 клика с !SEMTools: прямо в исходных ячейках, без СЖПРОБЕЛЫ, дополнительного столбца и замены формул значениями.
!SEMTools решает эту и сотни других задач в пару кликов!
Итоги: как убрать лишние пробелы в Excel
Надеюсь, на этой странице вы нашли то, что искали, и смогли убрать лишние пробелы в своих данных. Коротко о выборе способа:
| Задача | Способ |
|---|---|
| Двойные пробелы между словами | «Найти и заменить» несколько раз или СЖПРОБЕЛЫ |
| Пробелы в начале, в конце и двойные | СЖПРОБЕЛЫ |
| Все пробелы, в том числе в числах | ПОДСТАВИТЬ(A2;" ";"") |
| Неразрывные пробелы | ПОДСТАВИТЬ с СИМВОЛ(160) |
| Только в начале или только в конце | REGEXREPLACE |
| Всё сразу, прямо в ячейках | !SEMTools: выделить → 2 клика |
Вас наверняка заинтересуют и похожие операции: удалить знаки препинания и найти повторяющиеся значения — после очистки пробелов дубли находятся точнее.