Удаление лишних пробелов функцией СЖПРОБЕЛЫ (TRIM) и формулами
Весьма часто в нашем распоряжении оказываются данные, которые хоть и содержат полезную информацию, но не пригодны для немедленного анализа. Одной из весьма распространенных проблем является наличие лишних пробелов — в начале, в конце или внутри между словами. Лечится эта проблема очень легко — специальной функцией СЖПРОБЕЛЫ (TRIM) . Она убирает все лишние пробелы, но оставляет по одному пробелу между словами:
Просто и красиво. Но есть пара нюансов.
Неразрывные пробелы не удаляются
Чайной ложкой дегтя тут представляется только невозможность удалять таким способом неразрывные пробелы. Это особый вариант пробела (внешне неотличимый от обычного), на котором не бывает переноса строки. Обычно его используют в случаях типа «г. Москва» или «Иванов И.И.», чтобы эти фразы не разбивались между строчками. В Microsoft Word такой спецсимвол вводится сочетанием клавиш Ctrl+Shift+Пробел и отображается в виде кружка, а не точки:
К сожалению, функция СЖПРОБЕЛЫ (TRIM) удаляет только обычные пробелы. Для удаления неразрывных нужно использовать другие функции или макросы.
Формулы для удаления начальных и концевых пробелов
Если необходимо удалить только начальные пробелы (т.е. не трогать концевые и пробелы между словами), то придется делать это хитрыми формулами, т.к. готовой функции (по аналогии с функцией LTrim в VBA) в Microsoft Excel, к сожалению, нет. Формула для удаления лишних пробелов только слева будет выглядеть так: 
В английской версии =MID(A1;FIND(LEFT(TRIM(A1));A1);LEN(A1)) Формула для удаления лишних пробелов только справа будет чуть посложнее и должна вводиться уже как формула массива (с Ctrl+Shift+Enter): 
В английском варианте это будет =LEFT(A1;MAX((MID(A1&REPT(» «;99);ROW(A1:A99),1);» «)*ROW(A1:A99)))
Ссылки по теме
- Подсветка ячеек с лишними пробелами условным форматированием
- Зачистка текста от лишних пробелов, непечатаемых символов, английских букв и т.д.
- Как проверить текст на соответствие заданному шаблону (маске)
Как убрать лишние пробелы в Excel
Иногда в различных выгрузках данных, рабочих данных, тексте занесенном сотрудниками появляются лишние пробелы. Возможно в некоторых случаях нам будет все равно есть пробелы или нет, но зачастую лишние пробелы будут мешать сравнению данных и их преобразованию. Так например, если ВПР-ить по фаилии «Иванов» данные из списка фамилий, в которой Иванов записан как » Иванов » нужного результата мы можем и не получить.
Итак, для того чтобы убрать лишние пробелы, в Excel нам доступны два варианта удаления пробелов: воспользоваться функцией удаления и удалить пробелы вручную через замену.
Удаление пробелов вручную через замену не во всех случаях может подходить нам, этот вариант актуален в тех случаях, когда мы заведомо знаем, что в тексте есть пробелы идущие подряд по три (» «) по два (» «) и другие комбинации (возможно вперемешку с какими-то символами, которые также нужно убрать). Чтобы воспользоваться заменой и убрать лишние пробелы сперва выделим диапазон ячеек для работы (если это весь лист, то можно не производить выделения). Теперь вызовем диалоговое окно замены сочетанием клавиш CTRL+H (в зависимости от версии Excel вид окна может немного отличаться):
Диалоговое окно замены
Укажем в поле «Найти» два пробела (» «), а в поле «Заменить на» ничего указывать не будем.
Кликнем «Заменить все», теперь все двойные пробелы исчезли (т.е. были заменены на «пусто»).
Если этот вариант обработки лишних пробелов нам не подходит, например, есть одиночные пробелы сначала строки или в ее конце, то мы можем обратиться к функции =СЖПРОБЕЛЫ(). Эта функция удаляет из текста абсолютно все пробелы, исключая одиночные пробелы между словами. Аргументом выступает текст или ячейка.
Пример удаления пробелов функцией:
Если наша статься помогла Вам, будем благодарны, если Вы нажмете +1 и/или Мне нравится внизу данной статьи или поделитесь с друзьями с помощью кнопок расположенных ниже.
Функция СЖПРОБЕЛЫ
Excel для Microsoft 365 Excel для Microsoft 365 для Mac Excel для Интернета Excel 2021 Excel 2021 для Mac Excel 2019 Excel 2019 для Mac Excel 2016 Excel 2016 для Mac Excel 2013 Excel 2010 Excel 2007 Excel для Mac 2011 Excel Starter 2010 Еще. Меньше
В этой статье описаны синтаксис формулы и использование функции СЖПРОБЕЛЫ в Microsoft Excel.
Описание
Удаляет из текста все пробелы, за исключением одиночных пробелов между словами. Функция СЖПРОБЕЛЫ используется для обработки текстов, полученных из других прикладных программ, если эти тексты могут содержать лишние пробелы.
Важно: Функция СЖПРОБЕЛЫ предназначена для удаления из текста знаков пробела 7-разрядного кода ASCII (значение 32). В наборе знаков Юникода существует дополнительный знак пробела, который называется знаком неразрывного пробела и имеет десятичное значение 160. Этот знак обычно используется на веб-страницах как сущность HTML . Сама по себе функция СЖПРОБЕЛЫ не удаляет этот знак неразрывного пробела. Пример обрезки обоих пробелов из текста см. в десяти лучших способах очистки данных.
Синтаксис
Аргументы функции СЖПРОБЕЛЫ описаны ниже.
- Текст Обязательный. Текст, из которого удаляются пробелы.
Пример
Скопируйте образец данных из следующей таблицы и вставьте их в ячейку A1 нового листа Excel. Чтобы отобразить результаты формул, выделите их и нажмите клавишу F2, а затем — клавишу ВВОД. При необходимости измените ширину столбцов, чтобы видеть все данные.
=СЖПРОБЕЛЫ(» Доход за первый квартал «)
Удаляет начальные и конечные пробелы из текста в формуле («Доход за первый квартал»)
Доход за первый квартал
2 способа удалить пробелы между словами или числами в ячейках Excel
Из этой статьи Вы узнаете 2 быстрых способа удалить лишние пробелы между словами или все пробелы из ячеек Excel. Вы можете использовать функцию TRIM (СЖПРОБЕЛЫ) или инструмент Find & Replace (Найти и заменить), чтобы вычистить содержимое ячеек в Excel.
Когда Вы вставляете данные из внешнего источника на лист Excel (простой текст, числа и т.д.), то можете получить лишние пробелы вместе с важными данными. Это могут быть начальные и конечные пробелы, несколько пробелов между словами или разделители разрядов в числах.
Следовательно, таблица выглядит малость неопрятно и становится трудной для использования. Казалось бы, простая задача может стать непростой. Например, найти покупателя с именем John Doe (нет лишних пробелов между частями имени), в то время как в таблице он сохранён как «John Doe«. Или числа, которые не могут быть просуммированы, а виноваты в этом опять лишние пробелы.
Из этой статьи Вы узнаете, как очистить данные от лишних пробелов:
- Удаляем все лишние пробелы между словами, а также отсекаем начальные и конечные пробелы
- Используем функцию TRIM (СЖПРОБЕЛЫ)
- Применяем инструмент Find & Replace (Найти и заменить)
Удаляем все лишние пробелы между словами, отсекаем начальные и конечные пробелы
Предположим, есть таблица с двумя столбцами. В столбце Name в первой ячейке содержится имя John Doe, записанное корректно, т.е. без лишних пробелов. Все остальные ячейки содержат вариант записи с лишними пробелами между именем и фамилией, а также в начале и в конце (начальные и конечные пробелы). Во втором столбце, с названием Length, показано количество символов в каждом имени.
Используем функцию «СЖПРОБЕЛЫ» для удаления лишних пробелов
В Excel существует функция TRIM (СЖПРОБЕЛЫ), которая используется для удаления лишних пробелов из текста. Далее Вы найдёте пошаговую инструкцию по работе с этим инструментом:
- Рядом со своими данными добавьте вспомогательный столбец. Можете назвать его Trim.
- В первой ячейке вспомогательного столбца (C2) введите формулу для удаления лишних пробелов: =TRIM(A2)
=СЖПРОБЕЛЫ(A2)
- Скопируйте эту формулу в остальные ячейки столбца. Вы можете использовать советы из статьи Как вставить одинаковую формулу сразу во все выбранные ячейки .
- Замените исходный столбец полученными данными. Для этого выделите все ячейки вспомогательного столбца и нажмите Ctrl+C, чтобы скопировать данные в буфер обмена.Далее выделите первую ячейку исходного столбца (в нашем случае A2), нажмите Shift+F10 или клавишу вызова контекстного меню, а затем клавишу V (З).
- Удалите вспомогательный столбец.
Готово! Мы удалили все лишние пробелы при помощи функции TRIM (СЖПРОБЕЛЫ). К сожалению, этот способ занимает много времени, особенно, когда таблица достаточно велика.
Замечание: Если после применения формулы Вы по-прежнему видите лишние пробелы, скорее всего в тексте содержатся неразрывные пробелы. Как их удалить, Вы можете узнать из этого примера.
Применяем инструмент «Найти и заменить», чтобы удалить лишние пробелы между словами
Этот вариант требует меньше действий, но позволяет удалить только лишние пробелы между словами. Начальные и конечные пробелы будут также отсечены до 1, но не удалены полностью.
- Выделите один или несколько столбцов с данными, в которых нужно удалить лишние пробелы между словами.
- Нажмите Ctrl+H, чтобы открылось диалоговое окно Find & Replace (Найти и заменить).
- Введите два раза пробел в поле Find What (Найти) и один раз в поле Replace With (Заменить на).
- Нажмите кнопку Replace all (Заменить все), а затем ОК, чтобы закрыть появившееся информационное окно.
- Повторяйте шаг 4 до тех пор, пока не появится сообщение We couldn’t find anything to replace… (Мы не нашли ничего, что нужно было заменить…).
Удаляем все пробелы между числами
Предположим, у Вас есть таблица с числами, в которых группы разрядов (тысячи, миллионы, миллиарды) разделены пробелами. В этом случае Excel рассматривает числа как текст и никакая математическая операция выполнена быть не может.
Простейший способ избавиться от лишних пробелов – это воспользоваться стандартным инструментом Excel – Find & Replace (Найти и заменить).
- Нажмите Ctrl+Space (Пробел), чтобы выделить все ячейки в столбце.
- Нажмите Ctrl+H, чтобы открыть диалоговое окно Find & Replace (Найти и заменить).
- В поле Find What (Найти) введите один пробел. Убедитесь, что поле Replace With (Заменить на) – пустое.
- Нажмите кнопку Replace all (Заменить все), затем ОК. Вуаля! Все пробелы удалены.
Удаляем все пробелы при помощи формулы
Возможно, Вам пригодится способ удалить все пробелы при помощи формулы. Для этого Вы можете создать вспомогательный столбец и ввести такую формулу:
Здесь A1 – это первая ячейка столбца с числами или словами, в которой должны быть удалены все пробелы.