Как добавить ведущие нули в Excel

Microsoft Excel автоматически удаляет ведущие нули для чисел, так как нули не нужны для вычислений или точности. Однако бывают случаи, когда эти первые нули необходимы, например, при вводе идентификаторов продуктов, почтовых индексов или номеров социального страхования. В таких случаях вам необходимо переопределить поведение форматирования Excel по умолчанию. Настройка пользовательского числового формата в диалоговом окне «Формат ячеек» отображает ведущие нули и гарантирует, что Excel по-прежнему распознает цифры как числа. Однако вы также можете использовать форматирование текста с помощью диалогового окна «Формат ячеек», функций «Текст» или «Объединение» или функции импорта текста, чтобы добавить ведущие нули.

Пользовательские числовые форматы

Щелкните правой кнопкой мыши выбранные ячейки, строки или столбцы и выберите «Формат ячеек». На вкладке «Числа» выберите категорию «Пользовательский» и введите нули в поле «Тип»; количество вводимых нулей соответствует количеству цифр, которое должно отображаться; если ячейка содержит меньше цифр, чем настроенное вами количество нулей, начальные нули добавляются. Вы также можете включить символы форматирования, такие как знаки доллара, запятые, дефисы, апострофы и десятичные точки. Например, если вы настроили «000-00-000» (без кавычек) для номера социального страхования, а ячейка содержала только «9», вывод будет «000-00-009». Еще одно соображение заключается в том, что Excel допускает только 15 значащих цифр перед округлением числа, поэтому, если ваше число превышает 15 цифр, за исключением начальных нулей, вы должны использовать текстовый формат.

Текстовый формат

Текстовые форматы позволяют вводить любые данные в ячейку, включая ведущие нули, без изменения значений Excel. Просто выберите категорию «Текст» в диалоговом окне «Формат ячеек». Однако текстовый формат не будет возвращать ведущие нули, которые Excel уже удалил. Если вам нужно добавить ведущие нули к тексту или числам, повторно введите значение как текст или используйте функцию «Текст» или «Объединить». Вы также можете отформатировать число как текст в отдельной ячейке, добавив апостроф перед числом, например:'000-12-345. Числа, отформатированные как текст, по-прежнему могут использоваться в вычислениях, если они не включают дополнительный текст. элементы, такие как символы или дефисы.

Текстовая функция

Функция «Текст» позволяет ссылаться на текст или числа, а затем указывать формат вывода. Эта функция использует структуру "=Текст(значение,формат)". Значением может быть любая ссылка на ячейку или число, а формат следует той же структуре, что и настройка пользовательских чисел, за исключением того, что формат заключен в кавычки. Например, следующая формула преобразует число в ячейке A1 в 9-значный номер социального страхования:

=Текст(A1;"000-00-000")

Объединение чисел

Пользовательские числовые форматы и функция «Текст» задают определенное количество цифр, но количество начальных нулей зависит от количества введенных цифр, за исключением начальных нулей. Однако функция Concatenate позволяет добавить определенное количество начальных нулей независимо от того, сколько цифр содержится в исходной ячейке. Используйте структуру "=Concatenate(zeros,A1)" и введите нули в кавычках. Например, чтобы добавить три нуля к тексту или числу в A1, используйте формулу:

=Объединить("000",A1)

Вы также можете объединить амперсанд со структурой "=zeros&A1", например:

="000"&A1

Импорт текста

Когда вы открываете текстовый файл, Excel автоматически запускает мастер импорта текста. Используя настройки по умолчанию, Excel распознает числа и удаляет ведущие нули. Чтобы предотвратить такое поведение, выберите числовые столбцы на шаге 3 и выберите «Текст» в поле «Формат данных столбца». Это заставляет Excel обрабатывать числа как текст, прежде чем он начнет импортировать данные, что означает сохранение начальных нулей.