Вы узнаете, как с помощью формул удалять начальные и конечные пробелы в ячейке, лишние пробелы между словами, избавляться от неразрывных пробелов и непечатаемых символов.
Какая самая большая проблема с интервалами? Часто они невидимы для человеческого глаза. Внимательный пользователь может иногда заметить спрятавшийся перед текстом пустой пробел или какие-то ненужные пробелы между словами. Но нет возможности визуально обнаружить последующие комнаты, те, что находятся вне поля зрения в конце ячеек.
- Удалите все пробелы с помощью функции «Найти и заменить
- Используем формулу ТРИМ
- Удалить начальные и конечные пробелы с помощью формулы
- Как удалить разрывы строк и непечатаемые символы
- Формула удаления неразрывных пробелов
- Как найти и удалить непечатаемый символ
- Как преобразовать числа с пробелами в число
- Подсчет пробелов по формуле
- Простой способ удалить пробелы без формул.
Не было бы большой проблемой, если бы эти дополнительные интервалы просто существовали, но они могут повлиять на результаты ваших формул. Дело в том, что две ячейки, содержащие один и тот же текст с пробелами и без них, даже если это всего один символ, считаются разными значениями. В результате вы можете почесать голову, пытаясь понять, почему, казалось бы, правильная формула не может соответствовать двум, казалось бы, идентичным записям.
Теперь, когда вы полностью осознали проблему, пришло время найти решение. Есть несколько способов удалить пробелы из строки, и это руководство поможет вам выбрать метод, который лучше всего подходит для вашей конкретной задачи и типа данных, с которыми вы работаете.
Как убрать пробелы в Excel при помощи «Найти и заменить»
Это более быстрый метод, который может быть полезен в следующих ситуациях:
- Удаляет двойной интервал.
Обратите внимание, что этот метод не рекомендуется для удаления начальных или конечных пробелов, так как по крайней мере один из них останется.
Итак, давайте найдем и заменим двойные интервалы независимо от их расположения.
Вот как это сделать:
- Выберите ячейки, из которых вы хотите их удалить.
- Перейдите в главное меню -> Найти и выбрать -> Заменить (Также можно использовать горячую клавишу - CTRL+H).
- В диалоговом окне «Найти и заменить» введите:
- Находка: Двухместный номер.
- Заменить на: Одиночный.
Нажмите «Заменить все».
Обратите внимание, что если у вас есть три пробела между двумя словами, теперь вы получите два (один будет удален). В таких случаях вы можете повторить эту операцию еще раз, чтобы удалить все оставшиеся повторяющиеся комнаты.
Вы сами видите недостатки этого метода: перед любым словом стоит пробел. Точно так же после текста есть завершающие пробелы, просто они явно не видны. Поэтому для текстовых выражений я бы не рекомендовал использовать этот метод.
- Выполните следующие действия, чтобы удалить все пробелы в тексте:
- Выберите нужные ячейки.
- Перейдите в раздел «Главная» -> «Найти и выбрать» -> «Заменить». (Также можно использовать сочетание клавиш - CTRL+H).
- В диалоговом окне «Найти и заменить» введите:
Находка: одноместный номер.
Заменить на: Оставьте это поле пустым.
- Нажмите «Заменить все».
Это удалит все пробелы в выбранном диапазоне.
С текстовыми данными такие манипуляции делать вряд ли стоит, а вот для чисел вполне годится. Числовой интервал часто возникает при импорте данных из других программ через файл .csv. Правда, по рисунку видно, что цифры по-прежнему написаны в виде текста, но вид их стал более упорядоченным. Как их превратить в реальные числа — поговорим дальше отдельно.
Функция СЖПРОБЕЛЫ.
Если ваш набор данных содержит лишние пробелы, функция TRIM может помочь вам удалить их все за один раз — начальные, конечные и несколько промежуточных, оставив только один пробел между словами.
Синтаксис по умолчанию очень прост:
=ОТРЕЗАТЬ ПРОСТРАНСТВА(A2)
Где A2 — это ячейка, из которой вы хотите удалить.
Как показано на следующем снимке экрана, TRIM удалил все до и после текста, а также лишний пробел в середине строки.
И теперь вам просто нужно заменить значения в исходном столбце на новые. Самый простой способ сделать это — использовать Специальная вставка > Значения.
Формулы для удаления начальных и концевых пробелов.
В некоторых ситуациях вы можете захотеть удвоить или утроить слова, чтобы сделать ваши данные более читабельными. Однако вам нужно избавиться от начальных пробелов (тех, что в начале), например:
Как вы уже знаете, функция TRIM удаляет лишние пробелы в середине строк текста, которые в данном случае нам не нужны. Чтобы сохранить их в целости, воспользуемся чуть более сложной конструкцией:
=СРЗНАЧ(A2;НАЙТИ(СРЗНАЧ(ОТРЕЗКА(A2);1;1);A2);LSTR(A2))
Это выражение в начале вычисляет позицию первого символа в строке. Затем вы передаете это число другой функции MID, чтобы вернуть всю текстовую строку (длина строки вычисляется с помощью DLSTR), начиная с позиции первого символа.
Вы можете видеть, что все начальные пробелы исчезли, хотя между словами все еще остается несколько пробелов.
В качестве последнего штриха замените исходный текст полученными значениями, как описано выше.
Если вы просто хотите удалить пробелы в конце каждой ячейки, формула будет немного сложнее:
=ЛЕВО(A2,МАКС((СРЕД(A2&REP(" ",99),СТРОКА(A2:A100),1)" ")*СТРОКА(A2:A100)))
И обратите внимание, что его нужно вводить как формулу массива (с помощью Ctrl+Shift+Enter). В столбце A выравнивание по правому краю выполняется плохо из-за разного количества пробелов в конце каждой ячейки. В столбце B эта проблема решена, и текст можно красиво разместить.
Как удалить разрывы строк и непечатаемые символы
При импорте данных из внешних источников появляются не только лишние пробелы, но и различные непечатаемые символы, такие как возврат каретки, переносы строк, вертикальные или горизонтальные табуляции и так далее
Функция TRIM может удалять пробелы, но не может удалять непечатаемые символы. Технически он удаляет только значение 32 в 7-битном коде ASCII, что и является кодом пробела.
Чтобы также удалить из строки непечатаемые символы, используйте ее в сочетании с функцией CLEAN. Как следует из названия, CLEAN предназначен для очистки данных от ненужного «мусора» и может удалить все первые 32 непечатаемых символа 7-битного набора ASCII (значения от 0 до 31), включая разрывы строк (значение 10).
Предполагая, что удаляемые данные находятся в ячейке A2, формула будет выглядеть так:
=ОТРЕЗАТЬПРОСТРАНСТВА(ОЧИСТИТЬ(A2))
Почти всегда, когда вы используете приведенное выше выражение, вы удаляете разрывы строк, отдельные слова «приклеиваются» друг к другу. Вы можете исправить это, используя один из следующих методов:
- Используйте инструмент Excel «Заменить все»: в поле «Найти» введите возврат каретки, нажав сочетание клавиш Ctrl+J. А в поле «Заменить» введите пробел. При нажатии кнопки «Заменить все» все разрывы строк в выбранном диапазоне заменяются пробелами.
- Используйте следующую формулу, чтобы заменить возврат каретки (код 13) и перевод строки (код 10) пробелами:
=TRIMSPACES(ЗАМЕНИТЬ(ЗАМЕНИТЬ(A2; СИМВОЛ(13)," "), СИМВОЛ(10); " "))
Как видите, почтовый адрес в колонке C выглядит вполне разборчиво.
Как убрать неразрывные пробелы в Excel?
Если после использования формулы TRIM SPACE and CLEAN некоторые упрямые символы все еще остаются, то, скорее всего, вы скопировали/вставили данные из Интернета или из другой программы, и в вашу электронную таблицу все еще закралось несколько неразрывных пробелов.
Чтобы избавиться от неразрывных пробелов (html-кода), замените их на обычные, затем попросите функцию TRIM удалить их:
=ОТРЕЗАТЬ ПРОСТРАНСТВА((ПОДСТАВИТЬ(A2;СИМВОЛ(160);" ")))
Чтобы лучше понять логику, разберем формулу:
- Символ неразрывного пробела равен 160 в 7-битном ASCII, поэтому вы можете определить его с помощью CHAR(160).
- Функция REPLACE используется для преобразования неразрывных пробелов в обычные пробелы.
- И, наконец, вы вставляете SUBSTITUTE в TRIM SPACES, чтобы удалить все ненужное.
Если ваша электронная таблица также содержит непечатаемые символы, помимо вышеперечисленных, вы можете использовать функцию ОЧИСТИТЕЛЬ, чтобы одним махом избавиться от пробелов и других ненужных символов:
=ОТРЕЗАТЬ ПРОСТРАНСТВА(CUTCHAR((ПОДСТАВИТЬ(A2;СИМВОЛ(160);" "))))
На следующем снимке экрана показана разница в результатах:
Как удалить определенный непечатаемый символ
Если взаимодействие трех функций, описанных в приведенном выше примере, не смогло устранить весь мусор из текста, это значит, что оставшиеся символы имеют значения ASCII от 0 до 32 (непечатаемые символы) или 160 (неразрывный пробел).
В этом случае вы используете функцию CODE, чтобы сначала определить код оскорбительного символа, а затем используете REPLACE, чтобы заменить его обычным пробелом. А затем используйте TRIM, чтобы удалить его.
Предполагая, что нежелательные символы, от которых вы хотите избавиться, находятся в ячейке A2, напишите два выражения:
- В ячейке C2 определите код флага ошибки, используя одну из следующих функций:
- Ведущие пробелы или символы, которые не печатаются в начале строки:
= Знак (ЛЕВЫЙ ЗНАК (A2; 1))
- Завершающие пробелы или непечатаемые символы в конце строки:
= КОД СИМВОЛА (ПРАВО (A2; 1))
- Пробел или непечатаемый символ в середине строки, где n — позиция проблемного символа:
= КОД СИМВОЛА (СРЕДНИЙ (A2; n; 1)))
В этом примере у нас есть неизвестный символ в середине текста, на позиции 11, и мы хотим определить код:
=СИМВОЛ(СРЕДНЕЕ(A2;11;1))
Получаем значение 127 (см скриншот ниже).
- В ячейке C3 замените CHAR(127) на обычный пробел (" "), а затем удалите его:
=ОТРЕЗАТЬ ПРОБЕЛ(ПОДСТАВИТЬ(A2;СИМВОЛ(127); " "))
Если ваши данные содержат несколько разных непечатаемых символов, а также неразрывные пробелы, вы можете использовать следующие две или более функции REPLACE для удаления всех нежелательных символов одновременно:
= Trimspaces (Изменить ((заменить (заменить (A2, Char (127), "")), Char (160);" "))))
Результат должен выглядеть примерно так:
Мы использовали эту универсальную формулу. Как видите, успешно.
Как удалить все пробелы
В некоторых ситуациях может потребоваться удалить абсолютно все пробелы в ячейке, в том числе между словами или числами. Например, если вы импортировали в таблицу числовой столбец, в котором в качестве разделителя тысяч используются пробелы. Конечно, тысячи знаков препинания облегчают чтение больших чисел, но они также мешают вычислению ваших формул. Эти разделители должны создаваться с помощью форматирования, а не вручную.
Чтобы удалить все пробелы одним махом, используйте REPLACE, как описано в предыдущем примере, с той лишь разницей, что вы заменяете символ пробела, возвращаемый CHAR(32), ничем (""):
=ЗАМЕНИТЬ(A2; СИМВОЛ(32); "")
Или вы можете просто ввести его ("") в формулу, например:
=ЗАМЕНИТЬ(A2; “ “; "")
Результатом этого является текст, состоящий из цифр. Если в результате вам нужны числа, добавьте два знака «-» (минус) перед формулой. Любая математическая операция автоматически превращает цифры в числа. А употребив дважды минус, то есть дважды умножив на минус 1, мы не изменим значение числа и его знак.
Как посчитать пробелы в Excel
Чтобы получить общее количество пробелов в ячейке, выполните следующие действия:
- Вычислить всю длину строки с помощью функции ДЛИННОЕ: ДЛИННОЕ(A2)
- Замените все пробелы ничем: ПОДСТАВИТЬ (A2; " "; "")
- Вычислить длину строки без пробелов: DLSTR(SUBSTITUTE(A2; " "; ""))
- Вычтите этот результат из исходной длины.
Предполагая, что исходная текстовая строка находится в ячейке A3, вся формула выглядит так:
=ДЛИНА(A3) - LSTR(ЗАМЕНИТЬ(A3;" ";""))
Чтобы узнать, сколько лишних пробелов в ячейке, получите длину текста без них и вычтите ее из исходной длины:
=ДЛИННЫЙ(A3)-ДЛИННЫЙ(ОТРЕЗАТЬ ПРОСТРАНСТВА(A3))
На рисунке показаны обе формулы в действии:
Теперь, когда вы знаете, сколько пробелов содержит каждая ячейка, вы можете безопасно удалить лишние пробелы с помощью функции TRIM.
Простой способ удаления пробелов без формул.
Как вы уже знаете, лишние пробелы и другие нежелательные символы могут незаметно скрываться в ваших электронных таблицах, особенно если вы импортируете данные из внешних источников. Вы также знаете, как убрать пробелы в Excel с помощью формул. Конечно, изучение некоторых формул — хорошее упражнение для оттачивания навыков, но это может занять много времени.
Пользователи Excel, которые ценят свое время и удобство, могут воспользоваться текстовыми инструментами, включенными в надстройку Ultimate Suite for Excel. Один из этих удобных инструментов позволяет удалять пробелы и непечатаемые символы одним нажатием кнопки.
После установки Ultimate Suite добавляет на ленту Excel несколько полезных кнопок, таких как «Удалить пробелы», «Удалить символы», «Преобразовать текст» и другие.
Если вы хотите удалить лишние пробелы в электронных таблицах Excel, выполните следующие 4 быстрых шага:
- Выберите ячейки (диапазон, весь столбец или строку), в которых вы хотите удалить лишние пробелы.
- Нажмите кнопку «Обрезать пробелы» на вкладке «Данные Ablebits».
- Выберите один или несколько вариантов:
- Обрежьте начальные и конечные пробелы.
- Удалите лишние пробелы между словами, кроме одного.
- Удалите неразрывные пробелы;)
- Удалите лишние разрывы строк до и после значений, оставив только один между значениями.
- Удалить все разрывы строк в ячейке.
- Нажмите кнопку «Обрезать» .
Прозрачный! Все лишние пробелы удаляются одним кликом:
Здесь вместе с лишними пробелами мы также убрали переносы строк, чтобы значения размещались в обычном виде, на одной строке.
