Удаление пробелов в ячейках Excel – начальные пробелы, конечные пробелы, лишние пробелы или все пробелы

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

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

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

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

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

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

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

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

Использование функции MID для удаления только начальных пробелов

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

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

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

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

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

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

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 для Excel, пожалуйста, выберите непрерывные ячейки, из которых вы хотите удалить начальные пробелы, и нажмите кнопку OK.

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

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

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

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 для Excel, пожалуйста, выберите непрерывные ячейки, из которых вы хотите удалить конечные пробелы, и нажмите кнопку OK. Теперь вы можете видеть, что только конечные пробелы удалены из выбранных ячеек.

Удаление всех пробелов из ячеек

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

Использование функции SUBSTITUTE для удаления всех пробелов из ячеек

=SUBSTITUTE(текст, старый_текст, новый_текст, [номер_экземпляра])

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

=SUBSTITUTE(B3," ","")

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

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

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

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

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

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

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

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

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

2. Вы можете видеть, что в диалоговом окне Удалить пробелы есть 5 вариантов:

• Чтобы удалить только начальные пробелы, выберите вариант Начальные пробелы;
• Чтобы удалить только конечные пробелы, выберите вариант Конечные пробелы;
• Чтобы удалить начальные и конечные пробелы одновременно, выберите вариант Начальные и конечные пробелы;
• Чтобы удалить все лишние пробелы (включая начальные, конечные, промежуточные пробелы), выберите вариант Все лишние пробелы;
• Чтобы удалить все пробелы, выберите вариант Все пробелы.

Затем нажмите кнопку OK для начала операции.

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

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

---

Лучшие инструменты для повышения производительности Office

Kutools для Excel решает большинство ваших проблем и увеличивает вашу продуктивность на 80%

• Супер строка формул (легкое редактирование нескольких строк текста и формул); Режим чтения (удобное чтение и редактирование большого количества ячеек); Вставка в отфильтрованный диапазон...
• Объединение ячеек/строк/столбцов с сохранением данных; Разделение содержимого ячеек; Объединение дублирующихся строк с подсчетом суммы/среднего значения... Предотвращение дублирования ячеек; Сравнение диапазонов...
• Выбор дублирующихся или уникальных строк; Выбор пустых строк (все ячейки пустые); Супер поиск и нечеткий поиск во многих книгах; Случайный выбор...
• Точное копирование нескольких ячеек без изменения ссылок на формулы; Автоматическое создание ссылок на несколько листов; Вставка маркеров, флажков и многое другое...
• Избранные и быстрая вставка формул, диапазонов, диаграмм и изображений; Шифрование ячеек с паролем; Создание списка рассылки и отправка электронных писем...
• Извлечение текста, добавление текста, удаление по позиции, удаление пробелов; Создание и печать статистики страниц; Преобразование между содержимым ячеек и комментариями...
• Супер фильтр (сохранение и применение схем фильтрации к другим листам); Расширенная сортировка по месяцу/неделе/дню, частоте и другим параметрам; Специальный фильтр по жирному шрифту, курсиву...
• Объединение книг и листов; Объединение таблиц на основе ключевых столбцов; Разделение данных на несколько листов; Пакетное преобразование xls, xlsx и PDF...
• Группировка сводной таблицы по номеру недели, дню недели и другим параметрам... Отображение разблокированных, заблокированных ячеек разными цветами; Выделение ячеек, содержащих формулы/имена...

Читать далее... Полные функции 30-дневная бесплатная пробная версия... Приобрести... 

---

Office Tab - добавляет интерфейс с вкладками в Office и значительно упрощает вашу работу

• Включение редактирования и чтения с вкладками в Word, Excel, PowerPoint, Publisher, Access, Visio и Project.
• Открывайте и создавайте несколько документов в новых вкладках одного окна, а не в новых окнах.
• Увеличивает вашу продуктивность на 50% и экономит сотни кликов мышью каждый день!

Читать далее... Полные функции 30-дневная бесплатная пробная версия... Приобрести...