Пробелы в Excel, как использовать

Обновлено: 05.07.2024

Итог: изучите 5 различных способов поиска и удаления пробелов в Excel. Пробелы могут привести к ошибкам в формулах и проблемам с подготовкой и анализом данных.

Уровень квалификации: средний

Посмотреть обучающее видео

Загрузить файл Excel

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

Удалить пробелы BEFORE.xlsm

Удалить пробелы ПОСЛЕ.xlsm

Космическая охота

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

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

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

1. Найти и заменить

Первый способ поиска пробелов – использование функции "Найти и заменить".

После выделения ячеек, в которых вы хотите выполнить поиск, перейдите на вкладку "Главная". Затем откройте раскрывающееся меню «Найти и выбрать». Выберите вариант «Заменить». Сочетание клавиш для этого — Ctrl + H .

Появится окно «Найти и заменить». На вкладке «Заменить» поместите одно пустое место в поле «Найти». Убедитесь, что в поле «Заменить на» ничего нет. Нажатие «Заменить все» (сочетание клавиш: Alt + A) удалит все экземпляры пробела в выбранном наборе данных.

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

Этот сценарий подводит нас ко второму методу поиска пространства.

2. Функция ТРИМ

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

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

В пустом столбце начните с ввода знака равенства ( = ) и слова TRIM, а затем перейдите к функции TRIM.

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

Если вы скопируете формулу вниз для всего столбца, результат будет выглядеть следующим образом.

Как видите, интервал между словами остается на месте, но все пробелы до и после текста удаляются.

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

3. Power Query

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

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

Для удаления пробелов в Power Query есть функция обрезки, которая находится в контекстном меню. Выделив столбец, который вы хотите исправить, просто щелкните правой кнопкой мыши и выберите "Преобразовать" и "Обрезать".

Это обрезает все пустое пространство до/после текстовой строки, чтобы запрос возвращал правильные значения.

Если вы новичок в Power Query или хотите освежить свои знания, я рекомендую посетить мой бесплатный веб-семинар The Modern Excel Blueprint.

4. Макросы и VBA

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

Для начала откройте редактор VB. Перейдите на вкладку «Разработчик» и нажмите кнопку Visual Basic. Сочетание клавиш — Alt + F11 .

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

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

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

Таким образом, я могу использовать этот макрос в любой книге.

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

Также важно отметить, что функция VBA Trim НЕ обрезает лишние пробелы между словами. Функция TRIM в Excel удалит дополнительные пробелы между словами. Поэтому, если между именем и фамилией есть два пробела, TRIM удалит дополнительный пробел. Trim VBA этого не сделает. Он удаляет только пробелы в начале или конце строки. Спасибо Марвину (члену сообщества Excel Campus) за то, что обратил на это наше внимание!

Вот учебники о том, как создать личную книгу макросов, а также о том, как создавать кнопки макросов на листе или на ленте.

5. Обрезка других символов

Иногда после того, как вы обрезали пробелы в тексте, вы все еще можете получать ошибки вычислений. Одной из причин этого может быть то, что в игре может быть другой персонаж, которого трудно обнаружить. Одним из таких символов является неразрывный пробел. Это часто используется в кодировании HTML ( ) и похоже на обычный пробел, но не удаляется функцией TRIM или нашим макросом.

Чтобы определить, какой персонаж может испортить работу, вы можете использовать функцию КОД. Скопировав и вставив невидимый символ в пустую ячейку, а затем определив текст этой ячейки в функции КОД («текст» — единственный аргумент, который необходимо указать для функции КОД), вы вернете кодовый номер для этого символа. . Быстрый поиск в Google по этому кодовому номеру покажет, с каким типом символа вы имеете дело.

В этом случае неразрывный пробел имеет код 160.

Один из способов удалить этот символ из текста – воспользоваться функцией "Найти и заменить". Вы можете просто скопировать символ и вставить его в поле «Найти».

Вы также можете использовать функцию REPLACE, чтобы сделать то же самое.

Еще один вариант, поскольку мы знаем, что хотим удалить последний символ в тексте, заключается в использовании функций ВЛЕВО и ДЛСТР для создания настраиваемой функции обрезки.

LEFT возвращает крайние левые символы в ячейке до заданного количества. LEN (сокращение от Length) возвращает количество символов в ячейке.

Поэтому объединение этих двух функций и вычитание 1 из LEN для удаления этого нежелательного символа оставит только текст без дополнительного символа.

Заключение

Надеюсь, это было полезно для вас. Я также объясняю, как пробелы могут вызывать ошибки в формулах, в моем видео о введении в функцию ВПР.

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

Звучит знакомо? Вы ищете данные, которые, как вы знаете, вы ввели, но запрос для Джо Смита просто не возвращает никаких результатов. Или, например, вы пытаетесь сложить два столбца чисел, но постоянно получаете ошибки.

Часто эти проблемы возникают из-за того, что перед, после или между текстом и числами в ваших данных скрываются лишние пробелы. Когда информация была скопирована/вставлена в электронную таблицу, могут появиться дополнительные пробелы, которые останутся незамеченными, пока что-то не пойдет не так.

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

Эти шаги применимы к Excel 2007–2013. Изображения были сделаны с помощью Excel 2013 в ОС Windows 7.

Использовать функцию обрезки

Чтобы использовать функцию TRIM, вам потребуется создать временный или вспомогательный столбец для исправленных данных в конце электронной таблицы. В целях демонстрации мы назвали столбец «Обрезка».

Нажмите на первую ячейку столбца "Обрезать". На вкладке "Формулы" щелкните раскрывающееся меню "Текст" в группе "Функции" и выберите "ОБРЕЗАТЬ".

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

Скопируйте функцию в оставшиеся ячейки столбца, чтобы применить функцию к остальным ячейкам имени. Вы можете видеть, что функция TRIM убрала лишние пробелы, и количество символов теперь одинаковое, за исключением для «JoeSmith», у которого не было пробелов между именем и фамилией. Обратите внимание, что TRIM не ДОБАВЛЯЕТ пробелы.

Теперь вам просто нужно заменить исходные данные чистыми данными. Для этого выделите все ячейки в столбце «Обрезать», затем нажмите CTRL+C, чтобы скопировать ячейки в буфер обмена.

Затем выберите первую ячейку в столбце Имя и щелкните правой кнопкой мыши. Выберите «Вставить значения» под заголовком «Параметры вставки». Теперь вы можете удалить вспомогательный столбец Trim, и ваш столбец Name станет чистым.

Использование формулы для удаления всех пробелов

Если вам нужно подготовить данные для импорта в базу данных (например, MS Access) или иметь комбинацию числовых и текстовых данных, для которой просто необходимо удалить ВСЕ пробелы, вы можете использовать формулу вместо функции ОБРЕЗАТЬ, чтобы удалить ВСЕ пробелы в ячейке.

Создайте временный или вспомогательный столбец для исправленных данных в конце электронной таблицы, как вы сделали для функции TRIM. В этом примере столбец называется «Без пробелов».

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

Когда появится диалоговое окно:

Нажмите на первую ячейку в столбце, который нужно изменить.
В поле «Старый_текст» введите символ, который вы хотите заменить, разделив его кавычками. Чтобы удалить пробелы, это будет ” “.
В поле «Новый_текст» введите символ, который должен отображаться вместо него. Поскольку мы не хотим, чтобы вместо пробела использовались НИКАКИЕ символы, введите «».

Нажмите "ОК", чтобы применить формулу к ячейке.

Скопируйте формулу в остальные ячейки вспомогательного столбца. Затем скопируйте данные в столбец данных, как вы делали до этого, используя CTRL-C и параметры вставки.

Бонусная подсказка!

Вы также можете удалить пробелы с помощью функции "Найти/Заменить" в Excel. Нажмите CTRL+F, чтобы открыть диалоговое окно «Найти», затем щелкните вкладку «Заменить». Введите один пробел " " в поле "Найти:" и оставьте поле "Заменить на:" пустым, чтобы удалить все пробелы.

Предупреждение. Этот метод будет искать и заменять ВСЕ пробелы на всем листе или в выбранной области. Используйте осторожно, чтобы не удалить пробелы, которые вам нужны или нужны!

С помощью этих советов вы сможете очистить данные, введенные из нескольких источников, и убедиться, что они непротиворечивы и менее подвержены непреднамеренному дублированию. Используйте эти советы, чтобы удалить пробелы, если вы хотите:

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

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

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

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

Как удалить пробелы в Excel — начальные, конечные, между словами

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

Обычная формула TRIM очень проста:

Где A2 — это ячейка, из которой вы хотите удалить пробелы.

Как показано на следующем снимке экрана, формула TRIM в Excel успешно удалила все пробелы до и после текста, а также последовательные пробелы в середине строки.

Как удалить разрывы строк и непечатаемые символы

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

Функция TRIM позволяет избавиться от пробелов, но не может устранить непечатаемые символы. Технически функция TRIM в Excel предназначена для удаления только значения 32 в 7-битной системе ASCII, которое представляет собой символ пробела.

Чтобы удалить пробелы и непечатаемые символы в строке, используйте функцию TRIM в сочетании с функцией CLEAN. Как следует из названия, CLEAN предназначен для очистки данных и может удалить любые и все первые 32 непечатаемых символа в 7-битном наборе ASCII (значения от 0 до 31), включая разрыв строки (значение 10).

Предполагая, что очищаемые данные находятся в ячейке A2, формула выглядит следующим образом:

Если формула "Обрезать/Очистить" объединяет содержимое нескольких строк без пробелов, это можно исправить одним из следующих способов:

Используйте функцию Excel "Заменить все": в поле "Найти" введите возврат каретки, нажав сочетание клавиш Ctrl+J; и в поле «Заменить на» введите пробел. При нажатии кнопки Заменить все все разрывы строк в выбранном диапазоне будут заменены пробелами.
Используйте следующую формулу, чтобы заменить символы возврата каретки (значение 13) и перевода строки (значение 10) пробелами:

Как удалить неразрывные пробелы в Excel

Если после использования формулы TRIM & CLEAN какие-то неразрывные пробелы остались, скорее всего, вы откуда-то скопировали/вставили данные и влезло несколько неразрывных пробелов.

Чтобы избавиться от неразрывных пробелов (символ html ), замените их обычными пробелами, а затем удалите их с помощью функции TRIM:

Чтобы лучше понять логику, давайте разберем формулу:

Неразрывный символ имеет значение 160 в 7-битной системе ASCII, поэтому его можно определить с помощью формулы CHAR(160).
Функция ПОДСТАВИТЬ используется для преобразования неразрывных пробелов в обычные пробелы.
И, наконец, вы вставляете оператор SUBSTITUTE в функцию TRIM, чтобы удалить преобразованные пробелы.

Если ваш рабочий лист также содержит непечатаемые символы, используйте функцию ОЧИСТИТЬ вместе с ОБРЕЗАТЬ и ПОДСТАВИТЬ, чтобы одним махом избавиться от пробелов и нежелательных символов:

Следующий снимок экрана демонстрирует разницу:

Как удалить определенный непечатаемый символ

Если сочетание трех функций, обсуждавшихся в приведенном выше примере (ОТРЕЗАТЬ, ОЧИСТИТЬ и ПОДСТАВИТЬ), не смогло удалить пробелы или непечатаемые символы на вашем листе, это означает, что эти символы имеют значения ASCII, отличные от 0 до 32 (не -печатные символы) или 160 (неразрывный пробел).

В этом случае используйте функцию CODE, чтобы определить значение символа, а затем используйте SUBSTITUTE, чтобы заменить его обычным пробелом, и TRIM, чтобы удалить пробел.

Предполагая, что пробелы или другие нежелательные символы, от которых вы хотите избавиться, находятся в ячейке A2, вы пишете 2 формулы:

В этом примере у нас есть какой-то неизвестный непечатаемый символ в середине текста, на 4-й позиции, и мы узнаем его значение по этой формуле:

Результат должен выглядеть примерно так:

Если ваши данные содержат несколько разных непечатаемых символов, а также неразрывные пробелы, вы можете вложить две или более функции ЗАМЕНЫ, чтобы одновременно удалить все нежелательные коды символов:

=ОТРЕЗАТЬ(ЗАМЕНИТЬ(ЗАМЕНИТЬ(A2, СИМВОЛ(127), " "), СИМВОЛ(160), " ")))

Как удалить все пробелы в Excel

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

Чтобы удалить все пробелы за один раз, используйте ЗАМЕНУ, как описано в предыдущем примере, с той лишь разницей, что вы заменяете символ пробела, возвращаемый CHAR(32), ничем (""):

Или вы можете просто ввести пробел (" ") в формулу, например:

После этого замените формулы значениями, и ваши числа будут рассчитываться правильно.

Как считать пробелы в Excel

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

Чтобы получить общее количество пробелов в ячейке, выполните следующие действия:

Вычисление всей длины строки с помощью функции ДЛСТР: ДЛСТР(A2)
Замените все пробелы ничем: ПОДСТАВИТЬ(A2," ","")
Вычислить длину строки без пробелов: LEN(SUBSTITUTE(A2," ",""))
Вычтите длину строки без пробелов из общей длины.

Предполагая, что исходная текстовая строка находится в ячейке A2, полная формула выглядит следующим образом:

Чтобы узнать, сколько лишних пробелов в ячейке, получите длину текста без лишних пробелов, а затем вычтите ее из общей длины строки:

На следующем снимке экрана показаны обе формулы в действии:

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

Бесформенный способ удаления пробелов и очистки данных

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

Пользователи Excel, которые ценят свое время и удобство, могут воспользоваться текстовыми инструментами, включенными в наш Ultimate Suite for Excel. Один из этих удобных инструментов позволяет удалять пробелы и непечатаемые символы одним нажатием кнопки.

После установки Ultimate Suite добавляет на ленту Excel несколько полезных кнопок, таких как Обрезать пробелы, Удалить символы, Преобразовать текст, Очистить форматирование и некоторые другие.

Всякий раз, когда вы хотите удалить пустые места на листах Excel, выполните следующие 4 простых шага:

Выделите ячейки (диапазон, весь столбец или строку), в которых вы хотите удалить лишние пробелы.
Нажмите кнопку Обрезать пробелы на вкладке Данные Ablebits.
Выберите один или несколько вариантов:
- Удалить начальные и конечные пробелы
- Сократить лишние пробелы между словами до одного
- Удалить неразрывные пробелы ( )
Нажмите кнопку Обрезать.

Готово! Все лишние пробелы удаляются в один клик.

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

3 способа замены пробелов между символами в текстовых строках Excel

От группы обучения Avantix | Обновлено 20 октября 2021 г.

Применимо к: Microsoft® Excel® 2013, 2016, 2019 и 365 (Windows)

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

Хотите узнать больше об Excel? Посетите наш виртуальный класс или очные курсы Excel >

1. Замена пробелов с помощью функции «Найти и заменить»

Чтобы заменить пробелы с помощью функции "Найти и заменить":

Выделите диапазон ячеек, содержащих текстовые строки, включающие пробелы, которые вы хотите заменить.
Нажмите Ctrl + H, чтобы открыть диалоговое окно «Найти и заменить». Вы также можете открыть вкладку "Главная" на ленте и выбрать "Заменить" в группе "Найти и выбрать".
В поле "Найти" введите пробел.
В поле «Заменить на» введите символ подчеркивания, дефис или другое значение. Если вы хотите заменить пробел ничем, оставьте это поле пустым.
Нажмите «Заменить все». Появится диалоговое окно с указанием количества замен.
Нажмите "ОК".

Ниже показано диалоговое окно «Найти и заменить» в Excel для замены пробелов символами подчеркивания:

2. Замена пробелов с помощью формулы

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

Синтаксис функции ПОДСТАВИТЬ:

=SUBSTITUTE(ячейка, содержащая данные, которые вы хотите заменить, значение, которое вы хотите удалить, значение, которое вы хотите использовать в качестве замены)

Текст должен быть заключен в кавычки или двойные кавычки.

Например, если вы хотите заменить пробелы символами подчеркивания в ячейке B1 на основе текстовой строки в ячейке A1, формула будет выглядеть так:

Если вам нужно удалить начальные и конечные пробелы, вы можете комбинировать функцию ПОДСТАВИТЬ с функцией ОБРЕЗ следующим образом:

Чтобы преобразовать формулу в текст, просто скопируйте и вставьте формулу как значение.

3. Замена пробелов с помощью Flash Fill

Если у вас Excel 2013 или более поздняя версия, вы также можете попробовать использовать Flash Fill для замены пробелов. Поскольку Flash Fill должен видеть шаблон слева, вам, вероятно, потребуется ввести несколько примеров, прежде чем запускать Flash Fill.

Чтобы заменить пробелы символами подчеркивания или другими значениями:

Нажмите на ячейку справа от ячейки с пробелами, которые вы хотите заменить.
Введите ту же запись, что и в исходной ячейке, но с символами подчеркивания вместо пробелов, и нажмите Enter.
Повторите эти действия для следующих 3–4 ячеек ниже первой.
В ячейке под последней записью нажмите Ctrl + E, чтобы запустить мгновенное заполнение.

Обязательно проверьте записи, сгенерированные Flash Fill.

Подпишитесь, чтобы получать больше статей, подобных этой

Была ли эта статья полезной для вас? Если вы хотите получать новые статьи, ПРИСОЕДИНЯЙТЕСЬ к нашему списку рассылки.

Читайте также: