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