Иногда у пользователя Excel возникает проблема: появляются ненужные пробелы в начале и конце ячейки или появляются непонятные расстояния между словами. Их главная проблема в том, что они часто невидимы для человека. Поэтому часто ничего не подозревает, особенно если глаз достаточно расплывчатый при больших объемах данных. Проблема в том, что нет возможности визуально определить наличие лишних пробелов в конце ячеек в режиме отображения «часть текста». Сегодня мы подробнее рассмотрим, что можно сделать, чтобы удалить все пробелы.
Как убрать все пробелы в конце ячеек при помощи «Найти и заменить»
Этот метод можно использовать в некоторых ситуациях, потому что он особенно быстрый и простой.
Удаление двойных интервалов
Если в начале или в конце ячейки есть пробел, мы не рекомендуем использовать этот метод. Все потому, что один из них еще будет. Однако вот как использовать этот метод для удаления двойных пробелов:
- Щелкните ячейку, в которой вы хотите удалить ненужные пробелы. Вы также можете выделить весь диапазон значений.
- Откройте главное меню. Есть группа инструментов «Редактировать», и там вы должны нажать на кнопку «Найти и выбрать». После этого появится небольшое меню, в котором нужно выбрать «Заменить». Вы также можете использовать комбинацию горячих клавиш CTRL + H.
- В появившемся окне введите двойной пробел в поле «Найти» и замените его одинарным пробелом.
После того, как вы ввели все необходимые данные, просто нажмите кнопку «Заменить все». Но не рекомендуется нажимать кнопку «Заменить», потому что она заменяет только одну ошибку, и вам придется нажимать несколько раз, чтобы исправить ее.
Бывают и более сложные ситуации. Например, у пользователя есть три диапазона в одной ячейке, поэтому, если все действия, описанные выше, выполнены точно, их будет два, поскольку описанный выше метод удаляет только пустое пространство. Поэтому рекомендуется сделать это несколько раз, чтобы удалить оставшиеся пробелы.
В минусах этого метода вы сами можете разобраться. Перед некоторыми словами стоит пробел. Точно так же некоторые замыкающие пространства остаются видимыми, их просто невозможно увидеть невооруженным глазом. Поэтому, если выражение является текстом в ячейке, использовать этот метод не рекомендуется.
Удаление всех пробелов в тексте
Возможно, вам также придется удалить все пробелы. Если вам нужно это сделать, вам необходимо выполнить следующие действия:
- Выделите ячейки, с которыми мы будем выполнять эту операцию.
- Откройте вкладку «Главная» и нажмите кнопку «Заменить», как мы это делали в предыдущем примере. Сочетание клавиш такое же.
- В окне введите следующие данные:
- Находка: единое пространство.
- Не заполняйте поле «Заменить.
- Затем нажмите «Заменить все».
Этот метод удаляет все пробелы в ячейке. Оборотная сторона тоже понятна. Используя этот метод, вы можете убрать необходимые пробелы между словами. Поэтому перед его использованием нужно убедиться, что все ячейки диапазона не содержат более одного слова. По этой причине этот метод лучше всего подходит для числовых операций.
В некоторых случаях между цифрами могут быть ненужные пробелы. Это происходит очень часто, когда пользователь пытается импортировать информацию из другой программы. Также ячейка может быть в текстовом формате. На данном этапе это не проблема, поэтому мы разберемся, что делать, чтобы изменить формат на числовой.
Как удалить пробелы в ячейках при помощи формулы СЖПРОБЕЛЫ
Также есть специальная функция, предназначенная для удаления ненужных пробелов. Это называется TRIM. Позволяет одновременно удалять и инициалы, и инициалы. Также он способен заменить промежуточные под нож. После этого остается только пустое место, так что вы должны быть осторожны до определенного момента. Синтаксис этой функции: = TRIM (A2).
В скобках необходимо указать ячейку, из которой мы будем убирать пробелы. Итак, как вы можете видеть на следующем изображении, этот оператор смог легко удалить все пробелы в начале и конце строки, записанной в ячейке, а также ненужные интервалы внутри строки.
Затем вам нужно заменить значения, содержащиеся в исходном столбце, на обновленные. Самый простой способ сделать это — Специальная вставка. Там нужно выбрать запись «Значения».
Как удалить пробелы в начале и в конце ячейки формулами
Что касается пробелов только в начале и в конце, есть специальные формулы, которые можно использовать для их удаления. На этом скриншоте четко показана формула, по которой мы достигли этой цели.
Как видно из вышеизложенного, TRIM не подходит для удаления пробелов только в начале и в конце строки. Чтобы оставить промежуточные пробелы нетронутыми, формула будет намного сложнее и будет включать несколько операторов одновременно, как видно на скриншоте выше.
Он работает следующим образом: определяет, где находится первый символ текстовой строки. Затем это число принимается в качестве аргумента функцией MID, которая затем передает всю строку, начиная с первой позиции.
В свою очередь, оператор DLSTR определяет размер конкретной строки. После ввода этой формулы проверим результат. Если пробелы исчезли, значит, мы все сделали правильно. В качестве последнего шага вам нужно заменить исходный текст значениями, которые мы получили выше.
Если нам просто нужно удалить конечные пробелы, нам нужно использовать еще более сложную формулу: = LEFT (A2; MAX ((MID (A2 & REPEAT («»; 99)); STRING (A2: A100); 1) «») * СТРОКА (A2: A100))).
Важно: эту формулу необходимо вводить как формулу массива. То есть вам придется использовать комбинацию клавиш Ctrl + Shift + Enter. У нас не очень хорошо получалось выравнивать значение в первом столбце по правому краю, потому что количество пробелов в конце каждой строки было разным. Во втором столбце мы видим, что мы смогли решить эту проблему, и текст был размещен правильно.
Как удалить разрыв строки и непечатаемые символы
При передаче информации из других программ часто появляются непечатаемые символы, которые выглядят как пробел или немного иначе, но занимают место в ячейке. Это различные символы возврата каретки, перевода строки, табуляции и так далее.
Описанная выше функция TRIM отлично справляется с удалением пробелов из ячейки, но не подходит для удаления непечатаемых символов из ячейки. Фактически, достаточно удалить символ с кодом 32 в 7-битной таблице ASCII. Вот как закодирован этот символ. Но есть также значения с другими кодами, для которых нужно использовать функцию СЕКРЕТНО. В английской локализации программы он называется ЧИСТЫЙ. Как следует из названия, с помощью этого оператора можно очистить ячейку от мусора любого типа и удалить первые 32 непечатаемых символа.
Предположим, мы хотим удалить как пробелы, так и непечатаемые символы в ячейке A2. В этом случае наша задача — сначала использовать формулу TRIM для удаления непечатаемых символов, а затем передать эту строку функции TRIM, которая удаляет оставшиеся пробелы. Результат использования этой комбинации операторов Excel виден на скриншоте.
По сути, всякий раз, когда вы удаляете разрывы строк с помощью описанного выше метода, слова, разделенные ими, вставляются вместе. Как можно исправить эту ситуацию?
- Откройте диалоговое окно «Найти и заменить», используя метод, описанный выше. В поле «Найти» укажите символ возврата каретки. Для этого введите комбинацию клавиш Ctrl + J. В свою очередь используйте пробел в поле «Заменить». Когда вы нажимаете кнопку «Заменить все», все разрывы автоматически заменяются пробелами.
- вы также можете заменить символы возврата каретки пробелами, используя следующую формулу: = TRIM (SUBSTITUTE (SUBSTITUTE (A2; CAR (13); «»); CAR (10); «»)). Конечно, вы должны заменить адрес ячейки в функции REPLACE на тот, который нужен именно в вашей ситуации.
Как видите, проблема с почтовым адресом теперь успешно решена.
Удаление неразрывных пробелов
Довольно часто пользователь, использующий функцию ОБРЕЗАТЬ или ВТОРОЙ, обнаруживает, что они не работают. Это потому, что есть еще один непечатаемый символ, обозначающий неразрывные пробелы. Чаще всего такие символы появляются, когда пользователь пытается ввести информацию из Интернета непосредственно в электронную таблицу Excel.
Для этого замените неразрывный пробел (код 160) пробелом с помощью функции REPLACE. Впоследствии щель убирается так же, как и раньше. Если окажется, что рабочий лист также содержит непечатаемые символы, необходимо также использовать следующую формулу: = ОБРЕЗАТЬ (ПЕЧАТЬ ((ПОДСТАВИТЬ (A2; СИМВОЛ (160);» «)))).
Как найти и удалить непечатаемый символ
Но что, если мы уже знаем, какой именно непечатаемый шрифт нас беспокоит? Или символ, который мы хотим удалить, не входит в число 32 первых в таблице ASCII? В этом случае вам нужно использовать функцию REPLACE, чтобы заменить его пробелом.
Если мы не знаем код непечатаемого символа, который мы должны удалить, мы должны использовать функцию CODE, которая позволяет нам его получить. Кроме того, нет необходимости использовать функции CODE и SUBSTITUTE по отдельности, их можно сразу использовать в формуле, чтобы компьютер сначала определил код мешающего символа, а затем записал его как аргумент SUBSTITUTE.
Сама структура использования функции в нашем случае довольно сложна. Чтобы получить нужный нам символ, нам нужно использовать операторы LEFT, RIGHT или MID. Давайте подробнее разберемся, что использовать и в каких случаях:
- Если неразборчивый непечатаемый символ, который нам нужно удалить, находится в самом начале, нам нужно использовать следующую формулу: = CODE (LEFT (A2; 1))
- Если он находится в конце строки, формула такая же, но используется другой оператор: = КОД (ПРАВО (A2; 1))
- Если он находится в середине строки, то нам нужно использовать функцию MID, где значение n — это номер символа, на котором находится проблемный символ.
Комбинации могут быть самыми необычными. Например, используя эту формулу, пользователь может определить желаемые коды символов, заменить их пробелами и немедленно удалить. И все это в одной формуле: = ОБРЕЗАТЬ (ПЕЧСИМВ ((ПОДСТАВИТЬ (ПОДСТАВИТЬ (A2; СИМВОЛ (127); «»); СИМВОЛ (160);» «)))).
Преобразование цифр с пробелами в число
Иногда может потребоваться удалить даже те пробелы, которые используются для разделения цифр чисел. Конечно, они значительно облегчают чтение больших чисел, но в то же время мешают правильной работе формул. Поэтому пробелы нельзя использовать в качестве разделителей, для этого нужно использовать инструменты форматирования. Для этого необходимо использовать меню «Форматировать ячейку», которое вызывается из контекстного меню.
Как посчитать пробелы при помощи формул
Однако иногда пользователю необходимо определить общее количество пробелов в формуле. Для этого необходимо выполнить следующую формулу: = DLSTR (A3) — DLSTR (ПОДСТАВИТЬ (A3; «»; «»)). Если нам нужно определить, сколько ненужных символов в ячейке, нам нужно использовать следующую формулу: = DLSTR (A3) -LSTR (TRIM (A3)).
Как видите, удалить лишние пробелы из таблицы совсем не сложно, даже если они скрыты или находятся в нестандартном месте. Что касается удаления одного или нескольких пробелов в начале или конце строки, это совершенно тривиальная задача.