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

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

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

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

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

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

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

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

=ОТРЕЗАТЬ(A2)

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

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

И теперь вам нужно только заменить значения в исходном столбце усеченными значениями. Самый простой способ сделать это — использовать Специальная вставка > Ценностиподробные инструкции можно найти здесь: Как скопировать значения в Excel.

Кроме того, вы можете использовать функцию Excel TRIM для удаления только начальных пробелов, сохраняя при этом все пробелы в середине текстовой строки. Пример формулы находится здесь: Как обрезать начальные пробелы в Excel (Обрезка по левому краю).

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

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

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

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

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

= ОТРЕЗАТЬ (ОЧИСТИТЬ (A2))

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

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

  =ПОДСТАВИТЬ(ПОДСТАВИТЬ(A2, СИМВОЛ(13),” “), СИМВОЛ(10), ” “)

Для получения дополнительной информации см. Как удалить возврат каретки (разрывы строк) в Excel.

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

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

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

=ОТРЕЗАТЬ(ПОДСТАВИТЬ(A2, СИМВОЛ(160), ” “))

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

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

Если ваш рабочий лист также содержит непечатаемые символы, используйте функцию CLEAN вместе с TRIM и SUBSTITUTE, чтобы одним махом избавиться от пробелов и нежелательных символов:

=ОТРЕЗАТЬ(ЧИСТЫЙ((ПОДСТАВИТЬ(A2,СИМВОЛ(160),” “))))

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

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

Если сочетание трех функций, обсуждавшихся в приведенном выше примере (TRIM, CLEAN и SUBSTITUTE), не смогло удалить пробелы или непечатаемые символы на вашем листе, это означает, что эти символы имеют значения ASCII, отличные от 0 до 32 (непечатаемые символы). ) или 160 (неразрывный пробел).

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

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

1. В ячейке B2 определите проблемное значение символа, используя одну из следующих функций КОД:
   • Ведущий пробел или непечатаемый символ в начале строки:

     =КОД(СЛЕВА(A2,1))

   • Завершающий пробел или непечатаемый символ в конце строки:

     =КОД(ПРАВО(A2,1))

   • Пробел или непечатаемый символ в середине строки, где н позиция проблемного персонажа:

     =КОД(СРЕДНИЙ(A2, н1)))

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

   =КОД(СРЕДНИЙ(A2,4,1))

   Функция CODE возвращает значение 127 (см. скриншот ниже).

2. В ячейке C2 вы заменяете CHAR(127) обычным пробелом (” “), а затем обрезаете этот пробел:

   =ОТРЕЗАТЬ(ПОДСТАВИТЬ(A2, СИМВОЛ(127), ” “))

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

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

=ОТРЕЗАТЬ(ПОДСТАВИТЬ(ПОДСТАВИТЬ(A2, СИМВОЛ(127), ” “), СИМВОЛ(160), ” “)))

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

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

Чтобы удалить все пробелы за один раз, используйте ПОДСТАВИТЬ, как описано в предыдущем примере, с той лишь разницей, что вы заменяете символ пробела, возвращаемый CHAR(32), ничем (“”):

=ПОДСТАВИТЬ(A2, СИМВОЛ(32), “”)

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

=ПОДСТАВИТЬ(A2,” “,””)

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

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

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

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

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

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

=ДЛСТР(A2)-ДЛСТР(ПОДСТАВИТЬ(A2,” “,””))

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

=ДЛСТР(A2)-ДЛСТР(ОТРЕЗКА(A2))

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

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

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

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

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

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

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

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

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

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

Вас также могут заинтересовать