Работа с кодами символов в Excel
Чтобы узнать числовой код символа в ячейке, используйте функцию =CODE("текст"), а чтобы получить символ по его номеру — =CHAR(номер). Эти инструменты необходимы для очистки данных от невидимых знаков, создания специальных разделителей и конвертации текстовых массивов, полученных из других систем.
Принцип работы функций
Функции CHAR и CODE работают с набором символов ANSI (коды от 1 до 255), который является стандартом для большинства версий Excel в среде Windows.
- CODE(текст) — возвращает числовое значение первого символа в строке. Если в ячейке написано "Привет", функция вернет код буквы "П".
- CHAR(число) — преобразует числовой код (от 1 до 255) обратно в видимый или невидимый символ.
Важно про версии Excel
Функции CHAR и CODE ограничены диапазоном 1–255. Для работы с эмодзи, редкими иероглифами и символами Unicode (коды выше 255) в современных версиях (Excel 2013 и новее) используйте пары UNICHAR и UNICODE.
Практические примеры использования
1. Быстрая проверка кода
Чтобы понять, какой скрытый символ мешает формуле, проверьте его код.
- Формула:
=CODE(A1) - Результат: Число (например, 65 для буквы «A»).
2. Вставка невидимых разделителей
Часто требуется собрать текст из нескольких ячеек, разделив их переносом строки или табуляцией, которые нельзя просто напечатать клавиатурой внутри формулы.
- Перенос строки:
=CHAR(10) - Табуляция:
=CHAR(9) - Пример объединения:
=A1 & CHAR(10) & B1(текст в одной ячейке будет на двух строках).
3. Сборка строки из массива кодов
Если у вас есть столбец с числами (кодами), их можно превратить в читаемое слово. Допустим, в ячейках A1:A5 находятся числа: 72, 101, 108, 108, 111.
- Формула:
=TEXTJOIN(""); TRUE; CHAR(A1); CHAR(A2); CHAR(A3); CHAR(A4); CHAR(A5)) - Результат: "Hello".
Лайфхак для старых версий
Если у вас нет функции TEXTJOIN, используйте сцепку через амперсанд: =CHAR(A1)&CHAR(A2)&CHAR(A3)... или примените пользовательскую функцию VBA для перебора диапазона.
Очистка данных от мусорных символов
Самая частая задача для этих функций — удаление непечатных символов, которые попадают в таблицу при копировании из веба или старых баз данных.
- Поиск проблемы: Используйте
=CODE(MID(A1; 1; 1)), чтобы проверить первый символ. Если код меньше 32 (кроме стандартных пробелов и переносов), это управляющий символ. - Удаление: Функция
CLEANудаляет все непечатные символы (коды 0–31).- Формула:
=CLEAN(A1)
- Формула:
- Замена конкретного символа: Если нужно заменить конкретный код (например, неразрывный пробел код 160 на обычный пробел код 32):
- Формула:
=SUBSTITUTE(A1; CHAR(160); CHAR(32))
- Формула:
Таблица часто используемых кодов
Знание основных кодов ускоряет работу с формулами.
| Код | Символ | Описание | Где применяется |
|---|---|---|---|
| 9 | ↹ | Табуляция | Выравнивание столбцов в тексте |
| 10 | ↵ | Перенос строки (LF) | Разделение строк внутри ячейки |
| 13 | ↵ | Возврат каретки (CR) | Часто встречается в парах с LF из старых систем |
| 32 | ␣ | Обычный пробел | Стандартное разделение слов |
| 34 | " | Кавычка | Для добавления кавычек внутрь текста формулой |
| 160 | ⍽ | Неразрывный пробел | Частый "мусор" при копировании из браузеров |
Частые ошибки
- Ошибочный диапазон: Попытка использовать
CHAR(300)вернет ошибку#ЗНАЧ!, так как функция работает только до 255. Для больших значений нуженUNICHAR. - Игнорирование первого символа: Функция
CODEсчитывает код только самого первого знака в строке. Если нужно проверить второй символ, используйте связку сMID:=CODE(MID(A1; 2; 1)). - Разница платформ: Коды некоторых символов могут отличаться в Excel для Mac и Windows из-за разных системных кодировок. Всегда тестируйте файлы на целевом устройстве.
FAQ
Как вставить галочку или специальный знак через формулу?
Найдите код нужного символа в таблице символов (например, галочка в шрифте Wingdings имеет свой код, но проще использовать UNICHAR(10003) для стандартной галочки ✔).
Почему формула показывает квадратики вместо символов? Это означает, что в ячейке стоит символ, который не может отобразить текущий шрифт. Поменяйте шрифт ячейки на универсальный, например, Arial или Segoe UI Symbol.
Можно ли расшифровать весь текст сразу?
Да, если текст разбит по символам в столбце. Протяните формулу =CHAR(A1) вниз для всего столбца, а затем объедините результаты функцией TEXTJOIN или CONCAT.