Удаление пробелов с помощью TRIM

Данные, скопированные с веб-сайтов, из PDF-файлов или других систем, часто содержат лишние пробелы. Перед именем может оказаться пробел, между словами — два пробела, а пробелы в конце могут быть совершенно невидимыми.

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

TRIM удаляет пробелы из текста по простому правилу:

• Удаляет все пробелы в начале и конце
• Заменяет любую последовательность пробелов между словами одним пробелом

Поэтому TRIM(" Hello world ") возвращает очищенный текст Привет мир с ровно одним пробелом в середине и без пробелов по краям.

На практике Вы указываете для TRIM ячейку с лишними пробелами. Если в A2 находится   Джон Смит   со случайными пробелами, эта формула возвращает аккуратный вариант:

Результатом будет Джон Смит без начальных и конечных пробелов. Поместите формулу в новый столбец, затем скопируйте и вставьте значения обратно поверх исходных данных, если хотите сохранить очищенные данные.

Частый вопрос: удаляет ли TRIM все пробелы? Нет. Функция сохраняет одиночные пробелы между словами, поскольку они важны.

TRIM("New York") возвращает Нью-Йорк — три пробела сжимаются до одного. Предложения и имена остаются читаемыми, удаляются только лишние пробелы.

Конечный пробел невидим, но существует. Тексты Яблоко и Яблоко  (с конечным пробелом) выглядят одинаково, однако электронная таблица считает их разными значениями.

Из-за этого поиск может завершиться неудачей, а дубликаты могут остаться незамеченными. Применение TRIM к обоим столбцам перед сравнением устраняет это скрытое несоответствие.

Можно доказать наличие лишних пробелов в ячейке, сравнив её длину до и после применения TRIM.

Если LEN(A2) больше, чем LEN(TRIM(A2)), ячейка содержит пробелы, которые TRIM удалит. Эта формула показывает, сколько лишних пробелов скрывалось в ячейке.

Частое решение — обернуть значение для поиска в TRIM, чтобы случайный пробел не стал причиной отсутствия совпадения.

Здесь поисковый запрос в A2 очищается перед тем, как VLOOKUP начнёт искать его в таблице. Даже если в конце A2 были пробелы, поиск теперь найдёт очищенное значение в таблице.

TRIM обрабатывает только обычный пробел. Текст, полученный из интернета, иногда содержит неразрывный пробел — другой невидимый символ, который TRIM игнорирует.

Чтобы удалить такие пробелы, сначала замените их обычными пробелами с помощью SUBSTITUTE и кода символа 160, а затем примените TRIM. Сочетание этих двух функций очищает почти любые стойкие лишние пробелы.

Импортированный текст также может содержать непечатаемые символы, например переносы строк. Функция CLEAN удаляет их, а TRIM обрабатывает пробелы.

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

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

Для надёжности в любом из этих инструментов используйте приём с SUBSTITUTE и CHAR(160) — это надёжный способ очистить текст, вставленный из интернета.

Формула TRIM показывает очищенный текст, но исходная ячейка с лишними пробелами всё ещё существует. Чтобы заменить её окончательно, скопируйте ячейки с формулами, а затем выберите Специальная вставка → Значения поверх исходного столбца.

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

Вы научились исправлять лишние пробелы в тексте.

• TRIM(text) удаляет начальные и конечные пробелы
• Последовательности пробелов внутри текста сжимаются до одного пробела
• Конечные пробелы незаметно нарушают поиск и сопоставление
• Используйте LEN минус LEN(TRIM()), чтобы обнаруживать скрытые пробелы
• Для текста, вставленного из интернета, сочетайте TRIM с CLEAN и SUBSTITUTE(CHAR(160))

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