Excel убирает ведущие нули, потому что читает 00742 как число 742. Чтобы показать нули, задайте ячейкам пользовательский числовой формат 00000; чтобы превратить число в текст с нулями, используйте =ТЕКСТ(A2;"00000"), по-английски =TEXT(A2,"00000"). В таблицах формулы записаны по-английски, но их можно вводить и по-русски, с точкой с запятой.
| A | B | C | |
|---|---|---|---|
| 1 | Number | TEXT(A2,"00000") | Format 00000 |
| 2 | 742 | 00742 | 00742 |
| 3 | 15 | 00015 | 00015 |
| 4 | 31415 | 31415 | 31415 |
Оба столбца выглядят одинаково, но это не так: в столбце B текст, а в столбце C по-прежнему числа 742, 15 и 31415, которые только отображаются пятью цифрами. Каждый 0 в коде это цифра, которая показывается всегда, поэтому 00000 означает «не меньше пяти цифр».
Сохранить ведущие нули при вводе
Три способа ввести код, начинающийся с нуля, и сохранить нули:
- Пользовательский формат. Выделите ячейки, нажмите Ctrl+1 (на Mac Cmd+1), выберите «(все форматы)» и введите
00000в поле «Тип». Ячейки остаются числами, поэтому СУММ и сортировка работают. Подходит для чисел фиксированной длины, например почтовых индексов и табельных номеров. - Апостроф. Введите
'00742. Апостроф не отображается; он говорит Excel сохранить ввод как текст. - Текстовый формат. Выделите ячейки, выберите Главная > Числовой формат > Текстовый и вводите. Всё, что вы введёте потом, сохранится как текст, ровно так, как набрано.
| A | B | C | |
|---|---|---|---|
| 1 | Typed | Length | Is it text? |
| 2 | 00742 | 5 | TRUE |
| 3 | 742 | 3 | FALSE |
=ДЛСТР(A2)A2 введена как '00742 и сохраняет все пять символов. A3 введена как 00742 без апострофа, поэтому стала числом 742, и в ней 3 цифры. Используйте текст (апостроф или текстовый формат) для значений, которые являются кодами, а не количествами: телефонов, номеров счетов, артикулов и всего, что длиннее 15 цифр (Excel хранит только 15 значащих цифр числа, поэтому у 16-значного номера карты, введённого как число, последняя цифра превратится в 0).
Добавить ведущие нули формулой
ТЕКСТ дополняет число до фиксированного числа цифр. Для значения, которое уже является текстом или может содержать буквы, поставьте нули впереди и оставьте последние символы через ПРАВСИМВ:
| A | B | C | |
|---|---|---|---|
| 1 | Value | TEXT(A2,"000000") | RIGHT("000000"&A2,6) |
| 2 | 42 | 000042 | 000042 |
| 3 | 1234 | 001234 | 001234 |
| 4 | A17 | A17 | 000A17 |
ТЕКСТ нужно число: текст A17 в B4 она оставляет без изменений. Вариант с ПРАВСИМВ работает с обоими, потому что приписывает шесть нулей перед чем угодно и оставляет последние шесть символов. ПРАВСИМВ обрежет значение, которое уже длиннее шести символов. =ПОВТОР("0";МАКС(0;6-ДЛСТР(A2)))&A2 добавляет только недостающие нули и оставляет длинное значение целым.
| A | B | |
|---|---|---|
| 1 | Number | Employee ID |
| 2 | 4217 |
Ваша очередь: Табельные номера состоят из 6 цифр. В B2 верните номер из A2 как текст с ведущими нулями, например 004217.
Нули внутри более длинного кода
ТЕКСТ нужна и для того, чтобы вставить дополненное число в код или фразу. Если присоединить число напрямую, нули пропадут, даже когда ячейка показывает их благодаря пользовательскому формату:
| A | B | C | |
|---|---|---|---|
| 1 | Order | With TEXT | Without TEXT |
| 2 | 0007 | ORD-0007 | ORD-7 |
| 3 | 0315 | ORD-0315 | ORD-315 |
| A | B | |
|---|---|---|
| 1 | Number | Invoice |
| 2 | 87 |
Ваша очередь: В B2 соберите код счёта: INV-, а за ним номер из A2, дополненный до 5 цифр, например INV-00087.
Убрать ведущие нули и ловушка CSV
Чтобы превратить текст с ведущими нулями обратно в число, используйте =ЗНАЧЕН(A2) (подробнее о преобразовании текста в число). Для числа, показанного в формате 00000, верните формат «Общий».
Чаще всего нули теряются при открытии CSV-файла: Excel превращает 00742 в 742 уже при открытии, и при сохранении в файл запишется 742. Чтобы сохранить нули:
- Откройте пустую книгу, затем выберите Данные > Из текстового/CSV-файла и укажите файл.
- Нажмите Преобразовать данные, выделите столбец и задайте Тип данных «Текст».
- Нажмите Закрыть и загрузить.
В Microsoft 365 в разделе Файл > Параметры > Данные > Автоматическое преобразование данных есть параметр «Remove leading zeros and convert to a number» (удалять начальные нули и преобразовывать в число); снимите его, и Excel будет сохранять нули при вводе и при открытии CSV-файлов. В Google Таблицах задайте столбцу формат «Обычный текст» до того, как вводить или вставлять коды.
Часто задаваемые вопросы
Почему Excel убирает ведущие нули?
Excel читает 00742 как число 742, а у числа ведущих нулей нет. Задайте ячейкам текстовый формат до ввода, начните ввод с апострофа ('00742) или оставьте число и задайте ему пользовательский формат 00000.
Как добавить ведущие нули в Excel формулой?
=ТЕКСТ(A2;"00000") дополняет число до 5 цифр, поэтому 742 превращается в 00742. Для текста, в котором могут быть буквы, используйте =ПРАВСИМВ("00000"&A2;5).
Как сохранить ведущие нули при открытии CSV-файла?
Не открывайте CSV двойным щелчком. Выберите Данные > Из текстового/CSV-файла, нажмите «Преобразовать данные», задайте столбцу тип «Текст» и загрузите. В Microsoft 365 можно также отключить удаление начальных нулей в разделе Файл > Параметры > Данные > Автоматическое преобразование данных.
Как убрать ведущие нули в Excel?
Если в ячейке текст вроде '00742, =ЗНАЧЕН(A2) вернёт число 742. Если это число с форматом 00000, верните формат «Общий».
