Как убрать лишние пробелы в excel в ячейке

Удаление лишних пробелов функцией СЖПРОБЕЛЫ (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 для 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 (СЖПРОБЕЛЫ), которая используется для удаления лишних пробелов из текста. Далее Вы найдёте пошаговую инструкцию по работе с этим инструментом:
1. Рядом со своими данными добавьте вспомогательный столбец. Можете назвать его Trim.
2. В первой ячейке вспомогательного столбца (C2) введите формулу для удаления лишних пробелов: =TRIM(A2)
  =СЖПРОБЕЛЫ(A2)
3. Скопируйте эту формулу в остальные ячейки столбца. Вы можете использовать советы из статьи Как вставить одинаковую формулу сразу во все выбранные ячейки .
4. Замените исходный столбец полученными данными. Для этого выделите все ячейки вспомогательного столбца и нажмите Ctrl+C, чтобы скопировать данные в буфер обмена.Далее выделите первую ячейку исходного столбца (в нашем случае A2), нажмите Shift+F10 или клавишу вызова контекстного меню, а затем клавишу V (З).
5. Удалите вспомогательный столбец.
Готово! Мы удалили все лишние пробелы при помощи функции TRIM (СЖПРОБЕЛЫ). К сожалению, этот способ занимает много времени, особенно, когда таблица достаточно велика.

Замечание: Если после применения формулы Вы по-прежнему видите лишние пробелы, скорее всего в тексте содержатся неразрывные пробелы. Как их удалить, Вы можете узнать из этого примера.

Применяем инструмент «Найти и заменить», чтобы удалить лишние пробелы между словами

Этот вариант требует меньше действий, но позволяет удалить только лишние пробелы между словами. Начальные и конечные пробелы будут также отсечены до 1, но не удалены полностью.
1. Выделите один или несколько столбцов с данными, в которых нужно удалить лишние пробелы между словами.
2. Нажмите Ctrl+H, чтобы открылось диалоговое окно Find & Replace (Найти и заменить).
3. Введите два раза пробел в поле Find What (Найти) и один раз в поле Replace With (Заменить на).
4. Нажмите кнопку Replace all (Заменить все), а затем ОК, чтобы закрыть появившееся информационное окно.
5. Повторяйте шаг 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 – это первая ячейка столбца с числами или словами, в которой должны быть удалены все пробелы.

покупка

Удалите пробелы в ячейках Excel — ведущие пробелы, конечные пробелы, лишние пробелы или все пробелы

Иногда, когда данные были скопированы и вставлены из других приложений в рабочий лист, что-то может пойти не так, если вместе с данными бессознательно появятся дополнительные пробелы. Например, при добавлении двух числовых ячеек, содержащих начальные пробелы, он вернет результат как 0 вместо правильного результата; Или получите неверный результат при сравнении двух ячеек с одинаковым содержимым, но одна из них содержит начальные или конечные пробелы. Как убрать эти пробелы? Это руководство познакомит вас с несколькими методами.

Quickly remove all spaces (extra spaces, leading spaces or trailing spaces) from cells:

The Remove Spaces utility of Kutools for Excel can help you quickly remove all spaces from cells of selected range as below screenshot. Besides, you can also apply the feature to only remove leading spaces, trailing spaces or extra spaces from selected cells.
Download the full feature 60-day free trail of Kutools for Excel now!

Удалите лишние пробелы, включая начальные, конечные и лишние промежуточные пробелы из ячеек

Предположим, у вас есть столбец со списком ячеек, содержащий ведущие, завершающие и лишние промежуточные пробелы. Для удаления всех этих лишних пробелов из ячеек может помочь функция TRIM.

=TRIM( TEXT )

Выберите пустую ячейку, введите =TRIM(B3) в него и нажмите Enter ключ. А затем перетащите его Ручка заполнения вниз, чтобы применить формулу к другим ячейкам. Смотрите скриншот:
Внимание: B3 — это ячейка, содержащая текст, из которого вы хотите удалить пробелы.

Теперь все лишние пробелы, включая начальные, конечные и лишние промежуточные пробелы, удаляются из указанных ячеек.

Внимание: Эту операцию нужно выполнить в новом столбце. После удаления всех лишних пробелов вам необходимо заменить исходные данные на обрезанные, нажав кнопку Ctrl + C, выберите исходный диапазон данных и щелкните правой кнопкой мыши, чтобы выбрать Наши ценности под Параметры вставки .

Несколько кликов для удаления ненужных пространств из ячеек

Для Удалить пробелы утилита, всего несколько кликов помогут удалить не только ведущие пробелы, конечные пробелы, лишние пробелы а также все места из диапазона, нескольких диапазонов или даже всего листа, что сэкономит много вашего рабочего времени.
Kutools for Excel — Собирает более 300 мощных расширенных функций, предназначенных для более чем 1500 рабочих сценариев, решающих 80% Проблемы с Экселем.

Удалить из ячеек только ведущие пробелы

В некоторых случаях вам может потребоваться удалить только ведущие пробелы и сохранить все промежуточные пробелы в ячейках, как показано на скриншоте ниже. В этом разделе представлена другая формула и код VBA для решения этой проблемы.

Используйте функцию MID, чтобы удалить только ведущие пробелы

Выберите пустую ячейку (здесь я выбираю ячейку D3), скопируйте в нее приведенную ниже формулу и нажмите Enter ключ. А затем перетащите Ручка заполнения вниз, чтобы применить формулу к другим ячейкам.

=MID(B3,FIND(MID(TRIM(B3),1,1),B3),LEN(B3))

В этой формуле, B3 — это ячейка, содержащая текст, из которого вы хотите удалить начальные пробелы.

Внимание: Эту операцию нужно выполнить в новом столбце. После удаления всех начальных пробелов необходимо заменить исходные данные обрезанными данными без формул.

Используйте код VBA для удаления только ведущих пробелов

1. Откройте рабочий лист, содержащий ячейки, из которых вы удалите начальные пробелы, а затем нажмите другой + F11 , чтобы открыть Microsoft Visual Basic для приложений окно.

2. Нажмите Вставить > Модули, затем скопируйте ниже код VBA в окно модуля.
```
Sub RemoveLeadingSpaces() 'Updateby20190612 Dim Rng As Range Dim WorkRng As Range On Error Resume Next xTitleId = "KutoolsforExcel" Set WorkRng = Application.Selection Set WorkRng = Application.InputBox("Range", xTitleId, WorkRng.Address, Type:=8) For Each Rng In WorkRng Rng.Value = VBA.LTrim(Rng.Value) Next End Sub
```
3. нажмите F5 ключ для запуска кода. А Kutools for Excel появится диалоговое окно, выберите непрерывные ячейки, из которых вы удалите начальные пробелы, а затем щелкните OK кнопку.

Теперь вы можете видеть, что из выбранных ячеек удалены только ведущие пробелы.

Удалять из ячеек только конечные пробелы

1. Откройте рабочий лист, содержащий ячейки, из которых вы удалите начальные пробелы, а затем нажмите другой + F11 , чтобы открыть Microsoft Visual Basic для приложений окно.

2. Нажмите Вставить > Модули, затем скопируйте ниже код VBA в окно модуля.
```
Sub RemoveTrailingSpaces() 'Updateby20190612 Dim Rng As Range Dim WorkRng As Range On Error Resume Next xTitleId = "KutoolsforExcel" Set WorkRng = Application.Selection Set WorkRng = Application.InputBox("Range", xTitleId, WorkRng.Address, Type:=8) For Each Rng In WorkRng Rng.Value = VBA.RTrim(Rng.Value) Next End Sub 
```
3. нажмите F5 ключ для запуска кода. А Kutools for Excel появится диалоговое окно, выберите непрерывные ячейки, из которых вы удалите конечные пробелы, а затем щелкните OK кнопка. Теперь вы можете видеть, что из выбранных ячеек удалены только конечные пробелы.

Удалить все пробелы из ячеек

Если вы хотите избавиться от всех пробелов в определенных ячейках, методы из этого раздела могут оказать вам услугу.

Используйте функцию ЗАМЕНА, чтобы удалить все пробелы из ячеек

=SUBSTITUTE( text , old_text , new_text , [instance_num] )

Выберите пустую ячейку, скопируйте в нее формулу ниже и нажмите Enter ключ. Затем перетащите Ручка заполнения вниз, чтобы применить формулу к другим ячейкам.

=SUBSTITUTE(B3,» «,»»)

Tips: В этой формуле B3 это ячейка, содержащая текст, для которого вы хотите удалить все пробелы;
«» (пробел, заключенный в двойные кавычки) здесь представляет собой пробелы, которые вы хотите удалить;
«» здесь означает, что вы ничем не замените все пробелы.

Тогда вы увидите, что все пробелы в определенных ячейках немедленно удаляются.

Внимание: Эту операцию нужно выполнить в новом столбце. После удаления всех пробелов необходимо заменить исходные данные на обрезанные данные без формул.

Используйте функцию «Найти и заменить», чтобы удалить все пробелы из ячеек

Помимо вышеуказанного ЗАМЕНА Функция «Найти и заменить» также может помочь удалить все пробелы из ячеек.

1. Выделите ячейки, содержащие пробелы, которые вы хотите удалить, нажмите кнопку Ctrl + H , чтобы открыть Найти и заменить диалоговое окно.

2. в Найти и заменить диалог и под Замените вкладку, введите один пробел в Найти то, что текстовое поле, оставьте Заменить пустое текстовое поле, а затем щелкните значок Заменить все кнопку.

Легко удаляйте ведущие, конечные, дополнительные и все пробелы из ячеек с помощью Kutools

Функции: Если вам надоело использовать формулы и коды VBA для удаления пробелов, Удалить пробелы полезности Kutools for Excel ваш лучший выбор. Всего несколькими щелчками мыши вы можете удалить не только начальные и конечные пробелы, лишние пробелы, но и все пробелы из диапазона, нескольких диапазонов или даже всего рабочего листа, что сэкономит вам много рабочего времени.

Перед использованием Kutools for Excel вам нужно потратить несколько минут, чтобы бесплатно скачать и установить в первую очередь.

1. Выберите диапазон или несколько диапазонов с ячейками, из которых вы хотите удалить пробелы, нажмите Кутулс > Текст > Удалить пробелы. Смотрите скриншот:

2. Как видите, в Удалить пробелы диалоговое окно:
- Чтобы удалить только начальные пробелы, выберите Ведущие места вариант;
- Чтобы удалить только конечные пробелы, выберите Конечные пробелы вариант;
- Чтобы удалить начальные и конечные пробелы одновременно, выберите Начальные и конечные пробелы вариант;
- Чтобы удалить все лишние пробелы (включая ведущие, конечные и лишние промежуточные пробелы), выберите Все лишние места вариант;
- Чтобы удалить все пробелы, выберите Все пространства опцию.
Затем нажмите OK кнопку, чтобы начать операцию.

Если вы хотите получить бесплатную (30-дневную) пробную версию этой утилиты, пожалуйста, нажмите, чтобы загрузить это, а затем перейдите к применению операции в соответствии с указанными выше шагами.

Другие практические примеры, связанные с пространствами Excel

Помимо удаления пробелов из ячеек, сталкивались ли вы когда-нибудь с обстоятельствами подсчета, добавления пробелов или замены пробелов другими символами в ячейках? Приведенная ниже рекомендация может ускорить вашу работу в Excel.

Подсчитать общее количество пробелов в ячейке
Прежде чем удалять все пробелы из ячейки, вам может быть интересно узнать, сколько в ней пробелов. В этом руководстве подробно описаны методы, которые помогут вам быстро получить общее количество пробелов в ячейке.
Нажмите, чтобы узнать больше .

Добавляйте пробелы после каждой запятой в определенных ячейках Excel
Иногда из определенных ячеек могут быть случайно удалены пробелы. В этом руководстве говорится о добавлении пробела после каждой запятой, чтобы сделать текстовую строку более четкой и стандартной с подробными инструкциями.
Нажмите, чтобы узнать больше .

Добавить пробелы между числами в ячейках Excel
В этом руководстве рассказывается о добавлении пробела между каждым числом или каждым n-м числом в ячейках Excel. Предположим, у вас есть столбец с телефонными номерами, и вы хотите добавить пробелы между ними, чтобы номер выглядел более интуитивно понятным и более легким для чтения. Методы в этом руководстве помогут.
Нажмите, чтобы узнать больше .

Добавить пробелы перед заглавными буквами в ячейках Excel
В этом руководстве говорится о добавлении пробела перед каждой заглавной буквой в ячейках Excel. Предположим, у вас есть список текстовых строк со случайным удалением всех пробелов, например: InsertBlankRowsBetweenData, чтобы добавить пробелы перед каждой заглавной буквой, чтобы разделить слова как «Вставить пустые строки между данными», попробуйте методы из этого руководства.
Нажмите, чтобы узнать больше .

Заменить пробел конкретным символом в ячейках Excel
Во многих случаях вы предпочитаете заменять пробелы определенными символами, а не удалять их напрямую из ячеек. Здесь представлены методы, с помощью которых вы можете легко заменить пробел в ячейках подчеркиванием, тире или запятой.
Нажмите, чтобы узнать больше .

Лучшие инструменты для работы в офисе

Kutools for Excel решает большинство ваших проблем и увеличивает вашу производительность на 80%
- Бар Супер Формулы (легко редактировать несколько строк текста и формул); Макет для чтения (легко читать и редактировать большое количество ячеек); Вставить в отфильтрованный диапазон .
- Объединить ячейки / строки / столбцы и хранение данных; Разделить содержимое ячеек; Объедините повторяющиеся строки и сумму / среднее значение . Предотвращение дублирования ячеек; Сравнить диапазоны .
- Выберите Дубликат или Уникальный Ряды; Выбрать пустые строки (все ячейки пустые); Супер находка и нечеткая находка во многих рабочих тетрадях; Случайный выбор .
- Точная копия Несколько ячеек без изменения ссылки на формулу; Автоматическое создание ссылок на несколько листов; Вставить пули , Флажки и многое другое .
- Избранные и быстро вставляйте формулы , Диапазоны, диаграммы и изображения; Зашифровать ячейки с паролем; Создать список рассылки и отправлять электронные письма .
- Извлечь текст , Добавить текст, Удалить по позиции, Удалить пробел ; Создание и печать промежуточных итогов по страницам; Преобразование содержимого ячеек в комментарии .
- Суперфильтр (сохранять и применять схемы фильтров к другим листам); Расширенная сортировка по месяцам / неделям / дням, периодичности и др .; Специальный фильтр жирным, курсивом .
- Комбинируйте книги и рабочие листы ; Объединить таблицы на основе ключевых столбцов; Разделить данные на несколько листов ; Пакетное преобразование xls, xlsx и PDF .
- Группировка сводной таблицы по номер недели, день недели и другое . Показать разблокированные, заблокированные ячейки разными цветами; Выделите ячейки, у которых есть формула / имя .
Больше информации. Полнофункциональная 30-дневная бесплатная пробная версия . Покупка .

Вкладка Office — предоставляет интерфейс с вкладками в Office и значительно упрощает вашу работу
- Включение редактирования и чтения с вкладками в Word, Excel, PowerPoint , Издатель, доступ, Visio и проект.
- Открывайте и создавайте несколько документов на новых вкладках одного окна, а не в новых окнах.
- Повышает вашу продуктивность на 50% и сокращает количество щелчков мышью на сотни каждый день!
Похожие публикации: