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

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

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

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

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

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

Использование функции ПОДСТАВИТЬ (SUBSTITUTE) для удаления всех пробелов из ячеек

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

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

=SUBSTITUTE(B3," ","")

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

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

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

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

Помимо уже упомянутой функции ПОДСТАВИТЬ, удалить все пробелы из ячеек можно и с помощью функции «Найти и заменить».

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

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

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

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

2. В диалоговом окне Удалить пробелы доступны следующие варианты:

• Чтобы удалить только Пробелы в начале, установите флажок напротив параметра Пробелы в начале;
• Чтобы удалить только Пробелы в конце, установите флажок напротив параметра Пробелы в конце;
• Чтобы одновременно удалить Пробелы в начале и Пробелы в конце, установите флажок напротив параметра Leading & Пробелы в конце;
• Чтобы удалить все лишние пробелы (включая начальные, конечные и избыточные между словами), установите флажок напротив параметра Все лишние пробелы;
• Чтобы удалить все пробелы, установите флажок рядом с параметром All spaces.

Затем нажмите кнопку ОК, чтобы запустить операцию.

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

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

---

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

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

• Супер строка формул (удобное редактирование многострочного текста и формул); Режим чтения (удобный просмотр и редактирование большого количества ячеек); Вставка в диапазон фильтрации…
• Объединение ячеек, строк или столбцов с сохранением данных; разделение содержимого ячеек;Объединение дублирующихся строк с суммированием или усреднением… предотвращение дублирования записей в ячейках;Сравнение диапазонов…
• Выбор дубликатов или уникальных строк; выбор пустых строк (все ячейки пустые); улучшенный и нечёткий поиск по множеству книг; случайный выбор…
• Точная копия нескольких ячеек без изменения ссылок в формулах; автоматическое создание ссылок на несколько листов; вставка маркеров, флажков и многое другое…
• Избранные и быстрая вставка формул, диапазонов, диаграмм и изображений; Защита ячеек паролем; Создание списка рассылкии отправка электронных писем…
• Извлечение текста, добавление текста, удаление символов в определённой позиции, удаление пробелов; создание и печать статистики по странице данных; преобразование между содержимым ячеек и примечаниями…
• Суперфильтр (сохранение и применение схем фильтрации к другим листам); расширенная сортировка по месяцам, неделям, дням, частоте и другим параметрам; специальный фильтр по полужирному, курсиву…
• Объединение книг и листов; объединение таблиц на основе ключевого столбца; разделение данных на несколько листов; пакетное преобразование XLS, XLSX и PDF…
• Сводная таблица: группировка по номеру недели, дню недели и многому другому… Показ разблокированных и заблокированных ячеек разными цветами; Выделение ячеек, содержащих формулы или имена…

Подробнее…Полный функционал, бесплатная пробная версия на 30 дней…Купить… 

---

Office Tab — добавляет вкладки в Office и значительно упрощает вашу работу

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

Подробнее…Полный функционал, бесплатная пробная версия на 30 дней…Купить…