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

В этом руководстве рассматриваются различные методы добавления или сохранения ведущих нулей, а также удаления ведущих нулей в Excel.

Когда вы вводите или импортируете числа с одним или несколькими ведущими нулями, например 000652, Excel автоматически удаляет эти нули, и в ячейках отображается только само число (652). Это связано с тем, что ведущие нули не нужны для вычислений и не учитываются.

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

Добавление ведущих нулей в Excel

По сути, есть 2 метода, которые вы можете использовать для добавления начальных нулей: один, отформатируйте номер как «Текст»; во-вторых, используйте настраиваемое форматирование, чтобы добавить начальные нули. Метод, который вы хотите использовать, может зависеть от того, что вы хотите делать с номером.

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

Есть несколько способов добавить нули перед числами, отформатировав их как текст:

• Изменение формата ячейки на текст
• Добавление апострофа (‘)
• Использование функции ТЕКСТ
• Использование функции REPT / LEN
• Используйте функцию CONCATENATE / оператор амперсанда (&)
• Использование функции ВПРАВО

Изменение формата ячейки на текст

Это один из самых простых способов добавить начальные нули к вашим числам. Если вы собираетесь просто вводить числа и хотите, чтобы при вводе оставались ведущие нули, то этот метод для вас. Изменив формат ячейки с общего или числового на текстовый, вы можете заставить Excel обрабатывать ваши числа как текстовые значения, и все, что вы вводите в ячейку, останется неизменным. Вот как это сделать:

Выделите ячейки, в которые вы хотите добавить ведущие нули. Перейдите на вкладку «Главная», щелкните раскрывающееся поле «Формат» в группе «Числа» и выберите «Текст» в параметрах формата.

Теперь, когда вы вводите свои числа, Excel не удаляет из них начальный ноль.

Вы можете увидеть небольшой зеленый треугольник (индикатор ошибки) в верхнем левом углу ячейки, и когда вы выберете эту ячейку, он покажет вам предупреждающий знак, указывающий, что вы сохранили число в виде текста.

Чтобы удалить сообщение об ошибке, выберите ячейку (я), нажмите на предупреждающий знак, а затем выберите «Игнорировать ошибку» из списка.

Вы также можете вводить телефонные номера с пробелом или дефисом между числами, Excel автоматически обрабатывает эти номера как текст.

Использование интерлиньяжа Апостроф (‘)

Еще один способ добавить начальные нули в Excel - это добавить апостроф (‘) в начале числа. Это заставит Excel ввести число в виде текста.

Просто введите апостроф перед любыми числами и нажмите «Enter». Excel оставит ведущие нули нетронутыми, но (‘) не будет отображаться на листе, пока вы не выберете ячейку.

Использование текстовой функции

Вышеупомянутый метод добавляет нули к числам по мере их ввода, но если у вас уже есть список чисел и вы хотите добавить перед ними начальные нули, то функция ТЕКСТ - правильный метод для вас. Функция ТЕКСТ позволяет преобразовывать числа в текстовые строки, применяя настраиваемое форматирование.

Синтаксис функции ТЕКСТ:

= ТЕКСТ (значение; формат_текст)

Где,

• ценность - Это числовое значение, которое вам нужно преобразовать в текст и применить форматирование.
• format_text - формат, который вы хотите применить.

С помощью функции ТЕКСТ вы можете указать, сколько цифр должен содержать ваш номер. Например, если вы хотите, чтобы ваши числа состояли из 8 цифр, введите 8 нулей во второй аргумент функции: «00000000». Если у вас есть шестизначное число в ячейке, функция вручную добавит 2 ведущих нуля, а если у вас двухзначные числа, например 56, остальные будут нулями (00000056).

Например, чтобы добавить ведущие нули и сделать числа длиннее 6-значного, используйте эту формулу:

= ТЕКСТ (A2; «000000»)

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

Примечание: Не забудьте заключить коды формата в двойные кавычки в функции.

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

Функция ТЕКСТ всегда будет возвращать значение в виде текстовой строки, а не числа, поэтому вы не сможете использовать их в арифметических вычислениях, но вы все равно можете использовать их в формулах поиска, таких как ВПР или ИНДЕКС / ПОИСКПОЗ, чтобы получить подробную информацию о продукт, используя идентификаторы продукта.

Использование функции CONCATENATE / оператора амперсанда (&)

Если вы хотите добавить фиксированное количество ведущих нулей перед всеми числами в столбце, вы можете использовать функцию СЦЕПИТЬ или оператор амперсанда (&).

Синтаксис функции СЦЕПИТЬ:

= СЦЕПИТЬ (текст1; [текст2]; ...)

Где,

текст 1 - Количество нулей, которые нужно вставить перед числом.

текст2 - Исходный номер или ссылка на ячейку

Синтаксис оператора амперсанда:

= Значение_1 и значение_2 

Где,

Значение_1 - это начальные нули, которые нужно вставить перед числом, а Значение_2 - это число.

Например, чтобы добавить только два нуля перед числом, используйте любую из следующих формул:

= СЦЕПИТЬ ("00"; A2)

Первый аргумент - это два нуля («00»), потому что мы хотим добавить два нуля перед числом в A2 (которое является вторым аргументом).

Или,

= "00" & A2

Здесь первый аргумент - это 2 нуля, за которым следует оператор «&», а второй аргумент - это число.

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

Обе эти формулы соединяют определенное количество нулей перед исходными числами и сохраняют их в виде текстовых строк.

Использование функции REPT / LEN

Если вы хотите добавить начальные нули к числовым или буквенно-цифровым данным и преобразовать строку в текст, используйте функцию ПОВТОР. Функция ПОВТОР используется для повторения символа (ов) определенное количество раз. Эту функцию также можно использовать для вставки фиксированного числа ведущих нулей перед числом.

= ПОВТОР (текст; число_раз)

Где «text» - это символ, который мы хотим повторить (в нашем случае «0»), а аргумент «number_times» - это количество раз, которое мы хотим повторить этот символ.

Например, чтобы получить пять нулей перед числами, формула будет выглядеть так:

= ПОВТОР (0,5) & A2

Формула повторяет 5 нулей и объединяет числовую строку в A2 и возвращает результат. Затем формула применяется к ячейке B2: B6 с помощью маркера заполнения.

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

Если вы хотите добавить начальные нули везде, где это необходимо для создания строк с определенным символом (фиксированной длины), вы можете использовать функции REPT и LEN вместе.

Синтаксис:

= ПОВТОР (текст; число_раз-ДЛИН (текст)) & ячейка

Например, чтобы добавить нули с префиксом к значению в A2 и создать длинную строку из 5 символов, попробуйте следующую формулу:

= ПОВТОР (0,5-LEN (A2)) & A2

Здесь «LEN (A2)» - это общая длина строки / чисел в ячейке A2. «5» - это максимальная длина строки / чисел, которые должна иметь ячейка. Часть «REPT (0,5-LEN (A2))» добавляет количество нулей путем вычитания длины строки в A2 из максимального количества нулей (5). Затем перед значением A2 добавляется число нулей, чтобы получилась строка фиксированной длины.

Использование функции ВПРАВО

Другой способ дополнить начальные нули перед строкой в ​​Excel - использовать функцию ВПРАВО.

Функция ВПРАВО может добавлять несколько нулей в начало числа и извлекать из значения N крайних правых символов.

Синтаксис:

= ВПРАВО (текст; число_символов)

• текст это ячейка или значение, из которого вы хотите извлечь символы.
• num_chars - количество символов, извлекаемых из текста. Если этот аргумент не указан, будет извлечен только первый символ.

Для этого метода мы объединяем максимальное количество нулей со ссылкой на ячейку, которая содержит строку в аргументе «текст».

Чтобы создать 6-значное число на основе числовой строки в A с ведущими нулями, попробуйте следующую формулу:

= ВПРАВО ("0000000" & A2,6)

Первый аргумент (текст) формулы добавляет 7 нулей к значению в A2 («0000000» и A2), а затем возвращает 7 крайних правых символов, что приводит к некоторым ведущим нулям.

Добавление ведущих нулей с помощью пользовательского форматирования чисел

Если вы воспользуетесь любым из вышеперечисленных методов, чтобы поставить начальные нули перед числами, вы всегда получите текстовую строку, а не число. И от них не будет много пользы в расчетах или в числовых формулах.

Лучший способ добавить начальные нули в Excel - это применить настраиваемое форматирование чисел. Если вы добавляете ведущие нули, добавляя к ячейке произвольный числовой формат, это не изменяет значение ячейки, а только способ его отображения. Значение по-прежнему останется в виде числа, а не текста.

Чтобы изменить числовое форматирование ячеек, выполните следующие действия:

Выберите ячейку или диапазон ячеек, в которых вы хотите отобразить ведущие нули. Затем щелкните правой кнопкой мыши в любом месте выбранного диапазона и выберите параметр «Форматировать ячейки» в контекстном меню. Или нажмите сочетания клавиш Ctrl + 1.

В окне «Формат ячеек» перейдите на вкладку «Число» и выберите «Пользовательский» в разделе «Параметры категории».

Введите количество нулей в поле «Тип:», чтобы указать общее количество цифр, которое вы хотите отображать в ячейке. Например, если вы хотите, чтобы номер состоял из 6 цифр, введите «000000» в качестве кода настраиваемого формата. Затем нажмите «ОК», чтобы применить.

Это покажет ведущие нули перед числами, и если число меньше 6 цифр, оно добавит ноль перед ним.

Числа будут иметь только ведущие нули, в то время как базовое значение останется неизменным. Если вы выберете ячейку с настраиваемым форматированием, в строке формул отобразится исходный номер.

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

• 0 – Это заполнитель цифры, который отображает лишние нули. Он отображает принудительные цифры 0–9 независимо от того, соответствует ли цифра значению. Например, если вы введете 2,5 с кодом формата 000.00, отобразится 002.50.
• # – Это заполнитель цифры, который отображает необязательные цифры и не включает лишние нули. Например, если вы наберете 123 с кодом форматирования 000 #, отобразится 0123.

Кроме того, любой знак препинания или другой символ, который вы включили в код формата, будет отображаться как есть. Вы можете использовать такие символы, как дефис (-), запятая (,), косая черта (/) и т. Д.

Например, вы также можете форматировать номера как телефонные номера, используя настраиваемый формат.

Код формата в диалоговом окне Формат ячеек:

Результат:

Давайте применим этот код форматирования в следующем примере:

##0000

Как видите, «0» добавит лишние нули, а «#» не добавит незначащих нулей:

Вы также можете использовать предопределенные коды формата в разделе «Специальные форматы» диалогового окна «Форматирование ячеек» для почтовых индексов, номеров телефонов и номеров социального страхования.

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

Удаление ведущих нулей в Excel

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

Есть несколько способов удалить ведущие нули в Excel, и мы будем видеть их один за другим.

Удаление ведущих нулей путем изменения форматирования ячеек

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

Чтобы удалить нули с префиксом, выберите ячейки с ведущими нулями, щелкните поле «Числовой формат» и выберите вариант форматирования «Общее» или «Числовое».

Теперь начальные нули пропали:

Удаление ведущих нулей путем преобразования текста в числа

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

Вы можете использовать этот метод, если ваши числа выровнены по левому краю, а в ваших ячейках есть маленький зеленый треугольник (индикатор ошибки) в верхнем левом углу ячеек. Это означает, что числа отформатированы как текст.

Выберите эти ячейки, и вы увидите желтое предупреждение в верхней правой части выделения. Затем в раскрывающемся списке выберите «Преобразовать в число».

Ваши нули будут удалены, а числа будут преобразованы обратно в числовой формат (с выравниванием по правому краю).

Удаление ведущих нулей с помощью Умножение / деление на 1

Еще один простой и лучший способ удалить интерлиньяж - это умножить или разделить числа на 1. Деление или умножение значения не меняет значения, оно просто преобразует значение обратно в число и удаляет ведущие нули.

Для этого введите в ячейку формулу из приведенного ниже примера и нажмите клавишу ВВОД. Начальные нули будут удалены, а строка будет преобразована обратно в число.

Затем примените эту формулу к другим ячейкам с помощью маркера заполнения.

Вы можете добиться тех же результатов, используя команду «Специальная вставка». Вот как:

Введите числовое значение «1» в ячейку (скажем, в B2) и скопируйте это значение.

Затем выберите ячейки, в которых вы хотите удалить ведущие нули. Затем щелкните выделенный фрагмент правой кнопкой мыши и выберите параметр «Специальная вставка».

В диалоговом окне «Специальная вставка» в разделе «Операция» выберите вариант «Умножить» или «Разделить» и нажмите «ОК».

Вот и все, ваши ведущие нули будут удалены, а строки останутся числами.

Удалите ведущие нули с помощью формул

Еще один простой способ удалить нули с префиксом - использовать функцию ЗНАЧЕНИЕ. Этот метод может быть полезен независимо от того, добавлялись ли начальные нули с помощью другой формулы или апострофа или с помощью настраиваемого форматирования.

= ЗНАЧЕНИЕ (A1)

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

Иногда вам может потребоваться удалить ведущие нули, но сохранить числа в текстовом формате. В таких случаях вы должны использовать функции ТЕКСТ () и ЗНАЧЕНИЕ () вместе следующим образом:

= ТЕКСТ (ЗНАЧЕНИЕ (A1); "#")

Функция ЗНАЧЕНИЕ преобразует значение A1 в число. Но второй аргумент, «#», преобразует значение обратно в текстовый формат без каких-либо дополнительных нулей. В результате вы получите числа без начальных нулей, но все же в текстовом формате (с выравниванием по левому краю).

Удалите ведущие нули с помощью функции Excel Text to Columns

Еще один способ удалить ведущие нули - использовать функцию Excel Text to Columns.

Выделите диапазон ячеек, в которых числа с нулями в начале.

Затем перейдите на вкладку «Данные» и нажмите кнопку «Текст в столбцы» в группе «Инструменты для работы с данными».

Откроется мастер «Преобразовать текст в столбцы». На шаге 1 из 3 выберите «С разделителями» и нажмите «Далее».

На шаге 2 из 3 снимите все флажки с разделителей и нажмите «Далее».

На последнем шаге оставьте для параметра формата данных столбца значение «Общий» и выберите место назначения (первую ячейку диапазона), в котором вы хотите, чтобы числа были без начальных нулей. Затем нажмите «Готово».

И вы получите числа с удаленными ведущими в отдельном столбце, как показано ниже.

Вот и все.