Excel не убирает пробелы: проверьте код символа
Два кода могут выглядеть одинаково, но содержать разные символы. СЖПРОБЕЛЫ обрабатывает обычный пробел, но не неразрывный U+00A0. Сначала определите символ, затем применяйте только допустимые для ваших ID замены.
Неудачный поиск — повод для проверки
Если поиск не находит код, который вроде бы есть в таблице, сравните сам текст. Обычный, неразрывный и узкий неразрывный пробел бывает трудно различить на экране. Разберём эту причину на пяти вымышленных вариантах AB 007.
В нашем примере один обычный пробел между AB и 007 значим. По условию лишние пробелы с краёв, повторные обычные пробелы и два указанных неразрывных пробела можно привести к единому виду. У настоящих ID правила могут быть другими: выясните их до изменения данных.
Соберите пять примеров
На пустом листе подпишите A1:D1: Код, Длина, Третий символ, Очищено. Вставьте следующие пять строк в A2:A6. Каждая начинается с = и создаёт текст; не вставляйте весь блок в одну ячейку. Цифры 007 останутся частью текста.
Формулы для A2:A6
text
="AB 007"
="AB 007"
="AB"&ЮНИСИМВ(160)&"007"
="AB"&ЮНИСИМВ(8239)&"007"
=" AB 007 "Для русских имён функций. В строке 3 два обычных пробела внутри, в строке 6 — по два с каждого края.
Проверьте длину и подозрительную позицию
Введите =ДЛСТР(A2) в B2 и =UNICODE(ПСТР(A2;3;1)) в C2, затем протяните обе формулы до строки 6. Вторая показывает код Unicode третьего символа, а не всех пробелов в ячейке. Здесь разделитель стоит третьим в строках 2–5. В строке 6 из-за двух начальных пробелов третьей окажется буква A: результат 65. Для своего кода укажите позицию именно того символа, который вызывает подозрение.
Ожидаемые результаты
text
Строка Длина Третий символ
2 6 32
3 7 32
4 6 160
5 6 8239
6 10 6532 — обычный пробел U+0020; 160 — неразрывный U+00A0; 8239 — узкий неразрывный U+202F. Одинаковая длина не доказывает равенство текста.
Почему одной СЖПРОБЕЛЫ недостаточно
СЖПРОБЕЛЫ удаляет обычные пробелы по краям и сжимает их повторения внутри текста до одного. Неразрывный пробел U+00A0 она не удаляет. Второй неразрывный символ из примера, U+202F, в нашем рецепте тоже заменяется явно.
Не удаляйте все пробелы только ради успешного поиска: AB007 и AB 007 могут обозначать разные ID. Формула подходит лишь тогда, когда по правилам ваших данных перечисленные варианты считаются одним кодом.
Очистите вспомогательный столбец
text
=СЖПРОБЕЛЫ(ПОДСТАВИТЬ(ПОДСТАВИТЬ(A2;ЮНИСИМВ(160);" ");ЮНИСИМВ(8239);" "))Введите в D2 и протяните до D6. Между кавычками в каждой замене стоит один обычный пробел. Во всех пяти строках должен получиться AB 007 длиной 6 символов.
Что делает формула
Внутренняя ПОДСТАВИТЬ заменяет все U+00A0 обычным пробелом. Внешняя делает то же для U+202F. Затем СЖПРОБЕЛЫ упорядочивает обычные пробелы. Исходный столбец A остаётся для сравнения.
Это рецепт для двух конкретных символов Unicode, а не универсальная очистка невидимых знаков. Табуляция, переносы строк, символы нулевой ширины, различие текста и числа и похожие буквы требуют отдельной проверки. Не добавляйте замены, пока не определили различие.
Проверьте ключи до использования в поиске
- Примените одну и ту же допустимую очистку во вспомогательных столбцах обеих таблиц. Если очистить только искомое значение, отличие на другой стороне может остаться.
- Проверьте, у каких разных исходных значений получился одинаковый ключ. Отсортируйте всю таблицу по вспомогательному столбцу, сохранив соответствие полей в каждой строке, и изучите повторные ключи. Пять учебных примеров специально дают один ключ; в реальных записях это может скрыть конфликт.
- Сохраняйте исходные ID и нули в начале. Не преобразуйте коды в числа ради устранения пробелов. Если нули исчезли при прежнем импорте, эта формула их не восстановит.
- Используйте очищенный столбец для сопоставления после проверки правил равенства и дубликатов ключей. Если совпадения всё ещё нет, проверьте тип данных и другие позиции символов.
Если у вас английские имена функций
Соответствия: СЖПРОБЕЛЫ — TRIM, ПОДСТАВИТЬ — SUBSTITUTE, ЮНИСИМВ — UNICHAR, ДЛСТР — LEN, ПСТР — MID. UNICODE пишется одинаково. Для длины используйте =LEN(A2), для третьего символа — =UNICODE(MID(A2,3,1)). Ниже английская версия очистки. Разделители аргументов зависят от региональных настроек; здесь в английских формулах используются запятые, в русских — точки с запятой.
Та же очистка с английскими именами функций
text
=TRIM(SUBSTITUTE(SUBSTITUTE(A2,UNICHAR(160)," "),UNICHAR(8239)," "))Используйте имена функций, которые принимает ваша версия Excel.
Выберите следующий шаг
Учитывайте код символа и указанное правило для ID. Ответы доступны ниже и без JavaScript.
Ответы и пояснения
- Явно заменить U+00A0 и проверить очищенные ключи: Да. Замените известный символ обычным пробелом и проверьте, не появился ли один ключ у разных исходных значений.
- Применить только СЖПРОБЕЛЫ: СЖПРОБЕЛЫ сама по себе не удаляет неразрывный пробел U+00A0.
- Удалить все пробелы во всех кодах: Так исчезнет и значимый разделитель в AB 007. Нужен результат AB 007, а не AB007.