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

Обновлено: 21.11.2024

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

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

В этой статье вы узнаете, как очистить ваши данные.

Обрезать пробелы между словами до 1, удалить конечные/начальные пробелы

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

Используйте формулу обрезки, чтобы удалить лишние пробелы

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

Добавьте вспомогательный столбец в конец ваших данных. Вы можете назвать его "Обрезка".
В первой ячейке вспомогательного столбца (C2) введите формулу для обрезки лишних пробелов =TRIM(A2)
Скопируйте формулу в другие ячейки столбца. Вы можете воспользоваться некоторыми советами из статьи "Введите одну и ту же формулу во все выделенные ячейки одновременно".
Замените исходный столбец столбцом с очищенными данными. Выделите все ячейки во вспомогательном столбце и нажмите Ctrl + C, чтобы скопировать данные в буфер обмена.

Вот оно! Мы удалили все лишние пробелы с помощью формулы trim(). К сожалению, это занимает немного времени, особенно если ваша электронная таблица довольно большая.

Примечание. Если после использования формулы вы все еще видите лишние пробелы (последняя ячейка на снимке экрана), ознакомьтесь с разделом Если не работает функция ОБРЕЗ.

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

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

Выберите один или несколько столбцов с данными, чтобы удалить пробелы между словами.
Нажмите Ctrl + H, чтобы открыть диалоговое окно Найти и заменить.
Дважды нажмите клавишу пробела в поле Найти и один раз в поле Заменить на
Нажмите кнопку "Заменить все", а затем нажмите ОК, чтобы закрыть диалоговое окно подтверждения Excel.
Повторяйте шаг 4, пока не появится сообщение "Нам не удалось найти ничего для замены". :)

3 клика, чтобы очистить данные с помощью инструмента Trim Spaces

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

Надстройка Trim Spaces очистит данные, импортированные из Интернета или любого другого внешнего источника. Он удаляет начальные и конечные пробелы, лишние пробелы между словами, неразрывные пробелы, разрывы строк, непечатаемые символы и другие нежелательные символы. Кроме того, есть возможность конвертировать слова в ПРОПИСНОЙ, нижний или правильный регистр. А если вам нужно преобразовать текстовые числа обратно в числовой формат и удалить апострофы, это тоже не будет проблемой.

Чтобы удалить все лишние пробелы на листе, в том числе лишние шаги между словами, вам нужно сделать следующее:

Выберите диапазон в таблице, из которого вы хотите удалить лишние пробелы. Для новых таблиц я обычно нажимаю Ctrl + A, чтобы сразу обработать все столбцы.
Перейдите на вкладку Данные Ablebits и щелкните значок Обрезать пробелы.
Панель надстройки откроется в левой части листа. Просто отметьте нужные галочки, нажмите кнопку «Обрезать» и наслаждайтесь идеально очищенным столом.

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

Удалить все пробелы между числами

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

Самый простой способ избавиться от лишних пробелов — использовать стандартную функцию поиска и замены Excel:

Нажмите Ctrl + Пробел, чтобы выделить все ячейки в столбце.
Нажмите Ctrl + H, чтобы открыть диалоговое окно Найти и заменить.
Нажмите пробел в поле Найти и убедитесь, что поле "Заменить на" пусто.
Нажмите кнопку "Заменить все", а затем нажмите ОК. Вуаля! Все пробелы удалены.

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

Возможно, вам придется удалить все пробелы, как в цепочке формул. Для этого можно создать вспомогательный столбец и ввести формулу: =ПОДСТАВИТЬ(A1," ","")

Здесь A1 – это первая ячейка столбца с числами или словами, в которой необходимо удалить все пробелы.

Затем следуйте шагам из части, используя формулу, чтобы удалить лишние пробелы между словами до 1

Итог: изучите 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.

5 ответов 5

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

=ЕСЛИ(ИЛИ(ЛЕВО(A1,1)=" ",ПРАВО(A1,1)=" "),ИСТИНА,ЛОЖЬ)

TRUE идентифицирует данные с начальным или конечным пробелом

Да, есть. Таким образом, исходная длина

верно? Если вы обрежете его, и он вернет другую длину, тогда в начале или в конце значения был пробел, что было бы-

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

Вы хотите поставить чек в отдельный столбец? Если это так, попробуйте добавить это в столбец рядом с данными (или куда угодно, если вы ссылаетесь на столбец):

Это будет в ячейке B1. Обратите внимание, что это также будет захватывать экземпляры с несколькими пробелами до/после строки. Однако, как упоминает @YograjGupta, это также вернет true, если внутри строки есть последовательные пробелы.

Если ни один из других ответов в этом посте не работает, но формула Сачина Пандита «КОД(160)» работает, то на самом деле у вас не было «пробелов» как таковых [КОД(32)], а скорее какой-то неразрывный пробелы. Чтобы иметь дело с текстом, содержащим двойные (или более) пробелы CODE(32) между словами, вы можете использовать следующую формулу:

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

Не тот ответ, который вы ищете? Просмотрите другие вопросы с пометкой excel или задайте свой вопрос.

Связанные

Горячие вопросы о сети

Чтобы подписаться на этот RSS-канал, скопируйте и вставьте этот URL-адрес в программу для чтения RSS.

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

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

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

Формула Excel, используемая в решении.

В формуле используются функции ДЛСТР, ОБРЕЗКА и ЕСЛИ для проверки длины строки штрих-кода. Затем он сравнивается с длиной того же штрих-кода после удаления всех лишних пробелов. Формула выглядит следующим образом.

Excel берет длину строки в B2 и вычитает ее из той же строки, которая была обрезана. Если результат больше нуля (или были лишние пробелы), то аргумент ИСТИНА возвращает «ДОПОЛНИТЕЛЬНЫЕ ПРОБЕЛЫ». Если результат не больше нуля и не соответствует действительности, значение ячейки остается пустым.

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

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

Функция ЕСЛИ.

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

Синтаксис функции ЕСЛИ

=ЕСЛИ (логическая_проверка, [значение_если_истина], [значение_если_ложь])

logical_test — значение или логическое выражение, которое можно оценить как ИСТИНА или ЛОЖЬ.

value_if_true – [необязательно] значение, которое будет возвращено, если logical_test даст ИСТИННОЕ значение.

value_if_false — [необязательный] значение, которое будет возвращено, когда logical_test оценивается как FALSE.

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

Функция ДЛСТР.

Функция ДЛСТР в Excel возвращает длину заданной текстовой строки в виде количества символов. LEN также считает символы в числах, но форматирование чисел не учитывается.

Синтаксис функции LEN

=LEN (текст)

текст — текст, для которого вычисляется длина.

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

Функция TRIM.

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

Синтаксис функции TRIM

=ОБРЕЗАТЬ(текст)

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

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

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