Функция TRIM: как использовать и примеры
Вы узнаете несколько быстрых и простых способов убрать начальные, конечные и лишние пробелы между словами, почему Excel TRIM (английская версия) не работает и как это исправить.
Вы сравниваете два столбца на предмет дубликатов, но ваши формулы не могут найти повторяющиеся записи? Или вы складываете два столбца чисел, но получаете только нули? И почему, черт возьми, ваша, казалось бы, правильная формула ВПР возвращает лишь кучу N / A ошибок? Это всего лишь несколько примеров проблем, ответы на которые вы, возможно, ищете. И все это вызвано дополнительными пробелами, скрытыми до, после или между числовыми и текстовыми значениями в ваших ячейках.
Microsoft Excel предлагает несколько способов очистки ваших данных. В этом руководстве мы рассмотрим возможности функции TRIM как самого простого и быстрого способа удаления пробелов в Excel.
Итак, функция ОБРЕЗАТЬ в Excel используется для удаления лишнего интервала из текста. Удаляет все начальные, конечные и средние части, кроме пробела между словами.
Его синтаксис максимально прост:
ОБРЕЗАТЬ (текст)
Где текст — это ячейка, из которой вы хотите удалить лишний интервал (или просто текстовую строку, заключенную в кавычки).
Например, чтобы удалить их в ячейке A1, используйте эту формулу:
= ОБРЕЗАТЬ (A1)
И на этом скриншоте показан результат:
Да, это так просто!
Если ваши данные содержат разрывы строк и непечатаемые символы, а также лишние пробелы, используйте TRIM вместе с CLEAN, чтобы удалить первые 32 непечатаемых символа ASCII.
Например, чтобы удалить пробелы, разрывы строк и другие нежелательные символы из ячейки A1, используйте эту формулу:
= ОБРЕЗАТЬ (PSCH (A1))
Для получения дополнительной информации см. Как удалить непечатаемые символы в Excel.
Теперь, когда вы знаете основы, давайте обсудим несколько примеров использования TRIM в Excel, а также подводные камни, с которыми вы можете столкнуться.
Как убрать лишние пробелы во всём столбце.
Допустим, у вас есть столбец имен, в котором есть пробелы до и после текста и более одного пробела между словами. Итак, как удалить все начальные, конечные и конечные лишние пробелы во всех ячейках за один раз?
Записываем формулу Excel TRIM в соседний столбец и затем заменяем формулы их значениями. Подробные инструкции приведены ниже.
Напишите это выражение для самой верхней ячейки, A2 в нашем примере:
= ОБРЕЗАТЬ (A2)
Поместите курсор в нижний правый угол ячейки формулы (B2 в этом примере) и, когда курсор изменится на знак плюса, дважды щелкните, чтобы скопировать столбец в последнюю ячейку данных. В результате у вас будет 2 столбца: исходные имена интервалов и имена, переупорядоченные с помощью формулы.
Наконец, замените значения в исходном столбце новыми данными. Но будь осторожен! Простое копирование нового столбца поверх оригинала нарушит формулы. Чтобы этого не произошло, вам просто нужно скопировать значения, а не целые ячейки.
Вот как это сделать:
Выделите все ячейки с вычислениями (B2: B8 в этом примере) и нажмите Ctrl + C, чтобы скопировать их. Или используйте контекстное меню, щелкнув правой кнопкой мыши.
Выделите все ячейки со старыми данными (A2: A8) и нажмите Ctrl + Alt + V. Это сочетание клавиш только вводит значения и делает то же самое, что и контекстное меню «Специальная вставка»> «Значения.
Щелкните ОК. Готовый!
Как удалить ведущие пробелы в числовом столбце
Как вы только что видели, функция TRIMS в Excel плавно удалила все лишние интервалы из столбца текстовых данных. Но что, если ваши данные — это числа, а не текст?
На первый взгляд может показаться, что функция TRIM сделала свое дело. Однако при ближайшем рассмотрении вы заметите, что усеченные значения не ведут себя как числа. Вот лишь несколько признаков аномалии:
- И исходный столбец, и обрезанные числа выравниваются по левому краю, даже если к ним применяется числовой формат. В то время как обычные числа по умолчанию выравниваются по правому краю.
- Если выбраны две или более ячеек с невыбранными числами, Excel отображает в строке состояния только КОЛИЧЕСТВО. Для чисел также должно отображаться СУММ и СРЕДНЕЕ.
- Формула СУММ, примененная к этим ячейкам, возвращает ноль.
И что мы можем с этим поделать?
Маленькая уловка жизни. Если вы используете умножение на 1 вместо TRIM (A2), которое равно A1 * 1, вы получите тот же результат. И еще один элегантный способ избавиться от пробелов перед числом:
= — LA1
Но обращаю ваше внимание на то, что результатом все равно будет текст.
Но такое хитрое выражение сразу превратит текст «333» в число 333:
= — (- A1)
Вы можете видеть, что все числа выровнены по левому краю. Дело в том, что удаленные значения представляют собой текстовые строки и нам нужны числа. Чтобы исправить это, вы можете умножить вырезанные значения на 1 (чтобы умножить все значения за один раз, используйте опцию Специальная вставка> Умножить).
Более элегантное решение — заключить функцию TRIM в VALUE, например:
= ЗНАЧЕНИЕ (ОБРЕЗАТЬ (A2))
Выражение выше удаляет все начальные и конечные пробелы, если они есть, и преобразует полученное значение в число, как показано на следующем снимке экрана:
А также, если вам просто нужно число, вам не нужно беспокоиться об удалении лишних символов перед числами. Выражение
= ЗНАЧЕНИЕ (A2)
он сделает это за вас, и вы сразу получите выходное число, с которым вы сможете выполнять различные математические операции.
В качестве альтернативы вы можете использовать функцию Excel TRIM SPACE для удаления только ведущих пробелов, сохраняя их все неизменными в центре текстовой строки. Пример формулы здесь: Как удалить только ведущие пробелы?
Как посчитать лишние пробелы в ячейке.
Чтобы получить количество лишних пробелов в ячейке, узнайте общую длину текста с помощью функции DLSTR, затем вычислите длину строки без лишних пробелов и вычтите последнее из первого:
= DLSTR (A2) -LSTR (TRIM (A2))
На изображении ниже показана приведенная выше формула в действии:
Примечание. Выражение возвращает количество дополнительных пробелов в ячейке, например, начало, конец и более одного последовательного интервала между словами, но не учитывает отдельные пробелы в середине текста. Если вы хотите получить общее количество пробелов в ячейке, используйте эту формулу замены.
Как выделить ячейки с лишними пробелами?
Когда дело доходит до конфиденциальной или конфиденциальной информации, вы можете не решаться удалить что-либо, не видя, что именно вы удаляете. В этом случае вы можете сначала выделить ячейки, содержащие лишние пробелы, а затем безопасно удалить их.
Для этого создайте правило условного форматирования со следующей формулой:
= DLSTR ($ A2)> DLSTR (TRIM ($ A2))
Где A2 — это самый верхний адрес с данными, которые вы хотите выделить.
Мы говорим Excel выделить ячейки, в которых общая длина строки больше, чем длина вырезанного текста.
Чтобы создать правило условного форматирования, выберите все ячейки, которые вы хотите выделить без заголовков столбцов, перейдите на вкладку «Главная» и нажмите «Условное форматирование»> «Новое правило»> «Использовать формулу…».
Если вы еще не знакомы с условным форматированием Excel, вы найдете подробные инструкции здесь: Как создать правило условного форматирования на основе формул .
Как показано на скриншоте ниже, результат полностью подтверждается числами, полученными в соседнем столбце:
Как видите, использовать функцию ОБРЕЗАТЬ в Excel довольно просто.
Если не работает …
Функция TRIM предназначена для удаления только символа пробела, представленного значением кода 32 в 7-битном наборе символов ASCII. Набор Unicode содержит так называемые неразрывные пробелы, обычно используемые на веб-страницах как & nbsp; html… Имеет десятичное значение 160, и функция TRIM не может удалить его сама. Когда вы копируете текст с веб-страниц или Word, вы можете вставить его в свою электронную таблицу.
Иногда такие неразрывные пробелы вставляются специально, чтобы предложение в ячейке не разбивалось на несколько строк. Например, будет некрасиво, если инициалы оторвать от фамилии и перенести на следующую строку. В Microsoft Word такой специальный символ вводится с помощью сочетания клавиш Ctrl + Shift + Space.
Итак, если ваш набор данных содержит одно или несколько пробелов, которые наши стандартные методы не могут удалить, используйте функцию REPLACE, чтобы преобразовать неразрывные пробелы в обычные пробелы, а затем удалите их. Предполагая, что текст находится в A1, выражение выглядит так:
= ОБРЕЗАТЬ (ПОДСТАВИТЬ (A2; СИМВОЛ (160); » «))
В качестве дополнительной меры предосторожности вы можете включить функцию НАЖАТЬ, чтобы очистить ячейку от любых непечатаемых символов:
= ОБРЕЗАТЬ (ПЕЧАТЬ (ЗАМЕНИТЬ (A2; CH (160); » «)))
Это изображение показывает разницу:
Если вышеперечисленные параметры также не работают, вероятно, ваши данные содержат определенные непечатаемые символы с кодовыми значениями, отличными от 32 и 160.
В этом случае используйте одну из следующих формул, чтобы узнать код символа, где A1 — это проблемная ячейка:
Перед текстом: = CODE (LEFT (A1; 1))
После текста: = КОД (ПРАВО (A1; 1))
Внутри ячейки (где n — позиция проблемного символа в текстовой строке):
= КОД (MID (A1; n; 1)))
Затем передайте код найденного проблемного символа в TRIM (SUBSTITUTE ()), как описано выше.
Например, если КОД возвращает 9, то есть горизонтальную карточку, используйте следующую формулу, чтобы удалить ее:
= ОБРЕЗАТЬ (ПОДСТАВИТЬ (A2; СИМВОЛ (9); » «))
Инструмент Trim Spaces — удаляйте лишние пробелы одним щелчком мыши.
Если вам сложно применять формулы, вам может понравиться этот метод удаления пробелов в Excel одним щелчком мыши. Надстройка Ultimate Suite, среди множества полезных инструментов, таких как изменение регистра, разделение текста и извлечение из него нужных символов, предлагает опцию «Удалить пробелы» .
Если Ultimate Suite установлен в Excel, удалить пробелы будет очень просто:
- Выберите одну или несколько ячеек, в которых вы хотите удалить пробелы.
- Нажмите кнопку «Вырезать промежутки» на ленте.
- Выберите один, несколько или все следующие варианты:
- Вырежьте начальные и конечные пробелы.
- Удалите лишние пробелы между словами, кроме одного.
- Убрать неразрывные пробелы;)
- Удалите ненужные разрывы строк до и после значений, оставьте только одно из значений.
- Удалите все разрывы строк в ячейке.
- Нажмите кнопку «Вырезать).
это все! Все лишние пробелы удаляются в мгновение ока.
В этом примере мы удаляем все ненужное, оставляя только пробел между словами.
Задача, с которой не справятся формулы Excel, находится на расстоянии одного клика! Вот результат:
А вот и обзор других возможностей работы с текстовыми данными: изменить регистр символов, добавить текст от начала до конца, до или после выбранных символов, удалить непечатаемые символы, извлечь часть текста по позиции или после указанных символов извлекать числа из текста, разбивать текст на столбцы или строки и выполнять другие полезные операции.