Как поставить 0 перед числом в Excel

Обновлено: 03.07.2024

Это может раздражать, когда вы пытаетесь ввести ноль перед значением или числом в Excel, а он просто исчезает.

В этом блоге мы расскажем о простых способах управления своими нулями.

Как добавить 0 перед числом при вводе данных

Вариант 1: ключ апострофа

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

Давайте рассмотрим пример в Excel.

Здесь у нас есть число 90 без начальных нулей.

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

Нажмите Enter.

Теперь перед вашим номером должен стоять 0.

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

Предупреждение сделано для того, чтобы предупредить вас об этом изменении, поскольку оно может иметь некоторые последствия для работы в Excel, но иногда это не важно для вашего рабочего листа.

Вы можете проигнорировать предупреждение, выбрав стрелку раскрывающегося списка на предупреждающем знаке и выбрав "Игнорировать ошибку".

Вариант 2. Изменить числовой формат

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

На вкладке "Главная" щелкните стрелку раскрывающегося списка параметров "Числовой формат" (как показано в примере ниже) и выберите "Текст".

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

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

Вариант 1: Текстовая функция

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

Давайте рассмотрим пример в Excel.

Столбец A содержит числа, которые уже существуют на листе, и вы хотите добавить нули перед этими числами.

Значение в A5 возвращается в ячейку B5 с помощью функции Text.

Функция Text позволяет вам взять значение и установить для него определенный числовой формат.

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

В этом примере после запятой в кавычках ("0000") стоят четыре нуля.

Это говорит Excel о том, что вам нужно четыре символа в этом значении.

90 в ячейке A5 — это два из этих символов.

Нажмите Enter.

Число 90 теперь должно иметь два начальных нуля (0090).

Это означает, что перед числом 288 будет добавлен только один ноль, и получится 0288.

Но что, если вы хотите добавить четыре нуля перед каждым числом в списке?

Вариант 2: Функция CONCAT

Функция CONCAT позволяет объединять различную информацию.

Дополнительная информация: функция СЦЕПИТЬ похожа на функцию СЦЕПИТЬ. Однако функция СЦЕПИТЬ является более старой версией этой функции и по-прежнему доступна в Office 365 и более новых версиях Excel. Если вы используете старую версию Excel, замените CONCAT на CONCATENATE.

Давайте рассмотрим другой пример в Excel.

В этом примере к исходному числу (90 в ячейке A5) будут добавлены четыре начальных нуля.

В ячейке C5 нажмите клавишу равенства и введите СЦЕП.

Это должно дать вам =CONCAT(

Полезный совет. Когда вы начнете вводить СЦЕП, прежде чем вы закончите, Excel выдаст список того, что, по его мнению, вы хотите ввести. Если слово, которое вы хотите написать, выделено, вы можете нажать клавишу Tab, и слово будет автоматически заполнено за вас.

Теперь внутри кавычек введите столько нулей, сколько хотите добавить перед исходным номером. В данном примере это четыре («0000»).

Нажмите клавишу с запятой (,), выберите A5 и закройте квадратную скобку.

Это должно дать вам формулу =CONCAT("0000",A5)

Нажмите клавишу Enter.

Информация из A5 и нули теперь должны быть вместе, давая вашему номеру ведущие нули.

Вариант 3. Амперсанд

Символ амперсанда (&) позволяет объединять информацию из разных ячеек.

В ячейке, которую вы хотите ввести (в данном примере это ячейка C8), нажмите клавишу равенства (=) и в кавычках введите столько ведущих нулей, сколько хотите. В данном примере это четыре («0000»).

Теперь нажмите клавишу амперсанда (Shift + 7) и выберите исходное число (87 в ячейке A8).

Это должно дать вам формулу =”0000”&A8

Нажмите Enter.

Теперь у вас должно быть число с четырьмя ведущими нулями перед ним.

Вариант 4. Форматирование чисел

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

Для этого выделите числа, в которых должны быть ведущие нули.

Перейдите на вкладку "Главная" и нажмите стрелку раскрывающегося списка в области чисел.

Нажмите «Другие числовые форматы».

Должно появиться диалоговое окно «Формат ячеек».

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

Это означает, что если у вас есть двузначное число (например, 90), оно будет иметь 4 начальных нуля, а трехзначное число (например, 288) будет иметь 3 начальных нуля.

Нажмите "ОК".

Теперь все ваши числа должны быть предварительно установлены на шесть символов.

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

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

Был ли этот блог полезен? Дайте нам знать в комментариях ниже.

Если вам понравился этот пост, ознакомьтесь с похожими постами ниже.

Вы когда-нибудь пытались ввести данные, например 000123, в Excel?

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

Это может сильно раздражать, если вы хотите, чтобы в ваших данных были начальные нули, и вы не знаете, как заставить Excel сохранить их.

К счастью, есть несколько способов дополнить числа нулями в начале.

В этом посте я покажу вам 9 способов добавить или оставить начальные нули в ваших числах.

Видеоруководство

Форматировать как текст

Есть возможность изменить формат диапазона на текстовый.

При этом все данные, которые вы вводите, будут рассматриваться как текстовые значения, даже если они являются числами. Это приведет к тому, что Excel сохранит начальные нули в ваших числах.

Измените формат с «Общий» на «Текст».

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

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

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

Вы можете добавить собственное форматирование для форматирования чисел с ведущими нулями.

Однако они будут иметь только начальные нули. Базовые данные не будут преобразованы в текст с добавленными нулями.

< /p>

Добавьте собственный формат для отображения лидирующих нулей.

1. Выберите диапазон ячеек, к которым вы хотите добавить ведущие нули, и откройте диалоговое окно «Формат ячеек».
   • Нажмите правой кнопкой мыши и выберите "Формат ячеек".
   • Используйте сочетание клавиш Ctrl + 1.
2. Перейдите на вкладку "Число".
3. Выберите «Пользовательский» в параметрах категории.
4. Добавьте новый пользовательский формат в поле ввода "Тип". Если вы хотите, чтобы общее количество цифр, включая начальные нули, равнялось 6, добавьте 000000 в качестве пользовательского формата.
5. Нажмите кнопку ОК.

< /p>

После применения пользовательского формата вы заметите, что данные на самом деле не изменились.

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

Это также означает, что если вы скопируете и вставите данные как значения, вы потеряете начальные нули.

Ведущий апостроф

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

Это означает, что вы сможете оставить нули впереди при вводе данных.

Этот метод позволяет быстро и легко вводить данные. Просто введите символ ' перед любым числом. Это сообщит Excel, что данные должны быть текстовыми, а не числовыми.

При нажатии Enter начальные нули останутся видимыми на листе. ' не будет отображаться на листе, но все еще будет отображаться в строке формул, когда курсор активной ячейки находится в ячейке.

Функция ТЕКСТ

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

• Значение. Это значение, которое вы хотите преобразовать в текст и применить к нему форматирование.
• Формат. Это применяемое форматирование.

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

ВПРАВО

Еще один способ поставить нули перед формулой — использовать функцию ПРАВИЛЬНО. Вы можете соединить строку нулей с числом, а затем отрезать лишнее с помощью функции ПРАВИЛЬНО.

Функция RIGHT извлечет из текстового значения N самых правых символов.

• Текст. Это текст, из которого вы хотите извлечь символы.
• Число (Необязательно) – это количество символов, которое нужно извлечь из текста.Если этот аргумент не указан, будет извлечен только первый символ.

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

БАЗОВАЯ функция

Вы заметите, что в этой статье описывается 9 способов добавления лидирующих нулей, но в моем видео на YouTube показано только 8 способов.

Это потому, что я не знал, что вы можете использовать функцию BASE для добавления ведущих нулей, пока кто-то не упомянул об этом в комментариях к видео.

Функции BASE позволяют преобразовать число в текстовое представление с заданным основанием.

Обычно вы используете числа с основанием 10, но вы можете использовать эту функцию для преобразования числа с основанием 10 в представление с основанием 2 (двоичное).

• Число. Это число, которое вы хотите преобразовать в текстовое значение в другом формате.
• База – это база, в которую вы хотите преобразовать значение.
• MinLength (Необязательно) — это минимальная длина символов преобразованного текстового значения.

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

Определенно крутое новое использование функции, которую я никогда не использую.

Power Query и функция Text.PadStart

Power query — это преобразование данных, поэтому неудивительно, что вы можете преобразовывать свои числа, добавляя ведущие нули в power query.

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

< бр />

После импорта данных в power query вы можете добавить новый столбец с приведенной выше формулой, чтобы создать ведущие нули.

1. Перейдите на вкладку "Добавить столбец".
2. Выберите «Пользовательский столбец» на ленте.
3. Создайте новое имя для пользовательского столбца и добавьте приведенную выше формулу в поле формулы пользовательского столбца.
4. Нажмите кнопку ОК.

Это создаст новый столбец, в каждой строке которого будет не менее 6 символов, дополненных нулями, если это необходимо.

Сводная таблица и показатель DAX

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

< бр />

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

Обратите внимание, что функция FORMAT DAX точно такая же, как функция TEXT в Excel, но меры DAX должны быть объединены в одно значение, поэтому вам нужно поместить их в функцию CONCATENATEX.

< бр />

Когда вы добавите новую меру в область "Значения" сводной таблицы, это приведет к созданию списка всех чисел, разделенных запятыми, с добавленными ведущими нулями.

Вычисляемый столбец Power Pivot

Есть еще один способ добавить нули в сводную таблицу. Вы можете добавить их в вычисляемый столбец в Power Pivot.

Таким образом, вы можете использовать новый столбец в области "Фильтр", "Строки" или "Столбец" сводной таблицы.

В надстройке Power Pivot вы можете добавить новый столбец и использовать функцию ФОРМАТ, чтобы создать ведущие нули в столбце с формулой, как показано выше.

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

Теперь вы можете использовать этот новый столбец в области строк сводной таблицы, как и любой другой столбец данных!

Выводы

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

Как видите, существует множество способов убедиться, что все эти нули остаются в ваших данных.

Каковы бы ни были ваши требования, для вас обязательно найдется хорошее решение.

Есть ли у вас любимый способ, который я пропустил?

Об авторе

Джон МакДугалл

Джон — Microsoft MVP, внештатный консультант и тренер, специализирующийся на Excel, Power BI, Power Automate, Power Apps и SharePoint. Другие интересные статьи Джона можно найти в его блоге или на канале YouTube.

Подписаться

Похожие сообщения

7 способов переименовать лист в Microsoft Excel

Важно поддерживать порядок в электронной таблице! Хорошо организованная электронная таблица.

8 способов объединения ячеек в Microsoft Excel

Объединенные ячейки — один из самых популярных вариантов, используемых начинающими электронными таблицами.

6 способов подсчета цветных ячеек в Microsoft Excel [иллюстрированное руководство]

Возможно, вы использовали цветовое кодирование в данных Excel или видели его в a.

Комментарии

4 комментария

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

мой столбец данных имеет общий формат и содержит CS000000035678. (последние 5 цифр различаются) я заменяю все, чтобы удалить CS0000 из всех значений в столбце. конечно, ведущие нули удалены, и я получаю 35678. это данность.

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

Я форматирую столбец в текст и заменяю все, снова получается 35678.

Я редактирую ячейку в столбце и удаляю CS0000, а нули остались, и я получаю 00035678. (Похоже, у меня устанут пальцы после ручного обновления 4000 ячеек)

Огромное спасибо, отстойный продукт microsft. (Мне за 50, и я столько раз видел это с продуктами M$. Почему я не мог получить работу, ориентированную на яблоки! LOL) M$ делает много замечательных вещей, но затем они терпят неудачу в крошечных вещах, которые должны работать. В замене есть что-то, что даже в столбце с текстовым форматированием удаляет нули.

Я попробую использовать апостроф, но сомневаюсь, что мой процесс импорта примет его.

ответьте мне, если у вас есть подсказка/решение для меня. Спасибо, диджей

Вы можете импортировать в Power Apps как есть, а затем взять последние 8 символов с помощью функции RIGHT в Power Apps.

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

Отличное решение для меня и других.

Существует так много методов. Вы также можете использовать функцию REPT & LEN.

Например, вам нужно не менее 14 символов. Ячейка, которую вы читаете, может иметь любую длину. Вы можете написать следующую формулу.

Вы когда-нибудь импортировали или вводили в Excel данные, содержащие ведущие нули, например 00123, или большие числа, например 1234 5678 9087 6543? Примерами этого являются номера социального страхования, номера телефонов, номера кредитных карт, коды продуктов, номера счетов или почтовые индексы. Excel автоматически удаляет начальные нули и преобразует большие числа в экспоненциальное представление, например 1,23E+15, чтобы с ними могли работать формулы и математические операции. В этой статье рассказывается, как сохранить данные в исходном формате, который Excel обрабатывает как текст.

Преобразование чисел в текст при импорте текстовых данных

Используйте возможности Excel Get & Transform (Power Query), чтобы отформатировать отдельные столбцы как текст при импорте данных. В данном случае мы импортируем текстовый файл, но шаги преобразования данных одинаковы для данных, импортированных из других источников, таких как XML, Web, JSON и т. д.

Перейдите на вкладку "Данные", затем "Из текста/CSV" рядом с кнопкой "Получить данные". Если вы не видите кнопку «Получить данные», выберите «Новый запрос» > «Из файла» > «Из текста», перейдите к текстовому файлу и нажмите «Импорт».

Excel загрузит ваши данные на панель предварительного просмотра. Нажмите "Изменить" в области предварительного просмотра, чтобы загрузить редактор запросов.

Если какие-либо столбцы необходимо преобразовать в текст, выберите столбец для преобразования, нажав на заголовок столбца, затем перейдите в раздел Главная > Преобразовать > Тип данных > выберите Текст.

Совет. Вы можете выбрать несколько столбцов, нажав Ctrl+щелчок левой кнопкой мыши.

Далее нажмите «Заменить текущий» в диалоговом окне «Изменить тип столбца», и Excel преобразует выбранные столбцы в текст.

Когда вы закончите, нажмите "Закрыть и загрузить", и Excel вернет данные запроса на ваш лист.

Если ваши данные изменятся в будущем, вы можете выбрать Данные > Обновить, и Excel автоматически обновит ваши данные и применит ваши преобразования.

В Excel 2010 и 2013 существует два метода импорта текстовых файлов и преобразования чисел в текст. Рекомендуемый метод — использовать Power Query, который доступен после загрузки надстройки Power Query. Если вы не можете загрузить надстройку Power Query, вы можете использовать мастер импорта текста. В данном случае мы импортируем текстовый файл, но шаги преобразования данных одинаковы для данных, импортированных из других источников, таких как XML, Web, JSON и т. д.

Перейдите на вкладку Power Query на ленте, затем выберите Получить внешние данные > Из текста.

Excel загрузит ваши данные на панель предварительного просмотра. Нажмите "Изменить" в области предварительного просмотра, чтобы загрузить редактор запросов.

Если какие-либо столбцы необходимо преобразовать в текст, выберите столбец для преобразования, нажав на заголовок столбца, затем перейдите в раздел Главная > Преобразовать > Тип данных > выберите Текст.

Совет. Вы можете выбрать несколько столбцов, нажав Ctrl+щелчок левой кнопкой мыши.

Далее нажмите «Заменить текущий» в диалоговом окне «Изменить тип столбца», и Excel преобразует выбранные столбцы в текст.

Когда вы закончите, нажмите "Закрыть и загрузить", и Excel вернет данные запроса на ваш лист.

Если ваши данные изменятся в будущем, вы можете выбрать Данные > Обновить, и Excel автоматически обновит ваши данные и применит ваши преобразования.

Используйте собственный формат, чтобы оставить ведущие нули

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

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

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

Как сохранить ведущие нули в Excel при вводе

Для начала давайте посмотрим, как поставить 0 перед числом в Excel, например, ввести 01 в ячейку. Для этого просто измените формат ячейки на Текст:

• Выберите ячейки, в которых вы хотите добавить к числам префикс 0.
• Перейдите на вкладку Главная в группу Число и выберите Текст в поле Числовой формат.< /li>

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

На следующем снимке экрана показан результат:

Еще один способ оставить начальные нули в Excel – добавить перед числом апостроф ('). Например, вместо ввода 01 введите '01. В этом случае вам не нужно менять формат ячейки.

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

Как отобразить начальные нули в Excel, используя собственный числовой формат

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

1. Выберите ячейку (ячейки), в которой вы хотите отобразить ведущие нули, и нажмите Ctrl+1, чтобы открыть диалоговое окно Формат ячеек.
2. В разделе Категория выберите Пользовательский.
3. Введите код формата в поле Тип.

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

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

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

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

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

Итог: Этот метод лучше всего использовать в ситуациях, когда вы работаете с числовым набором данных, и результаты должны быть числами, а не текстом. Он изменяет только отображение числа, но не само число: в ячейках отображаются ведущие нули, фактическое значение отображается в строке формул. Когда вы ссылаетесь на такие ячейки в формулах, расчеты ароматизируются исходными значениями. Пользовательские форматы можно применять только к числовым данным (числам и датам), и результат также является числом или датой.

Как добавить ведущие нули в Excel с помощью функции ТЕКСТ

В то время как в пользовательском числовом формате ноль отображается перед числом без фактического изменения базового значения, функция ТЕКСТ в Excel дополняет числа нулями, "физически" вставляя ведущие нули в ячейки.

Чтобы добавить ведущие нули с формулой ТЕКСТ(значение, format_text), используйте те же коды формата, что и в настраиваемых числовых форматах. Однако результатом функции ТЕКСТ всегда является текстовая строка, даже если она очень похожа на число.

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

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

Обратите внимание, что функция ТЕКСТ требует заключения кодов форматов в кавычки. А вот как результаты будут выглядеть в Excel:

Дополнительную информацию о текстовых формулах см. в статье Использование функции ТЕКСТ в Excel.

Итог: Функция ТЕКСТ Excel всегда возвращает текстовую строку, а не число, и поэтому вы не сможете использовать результаты в арифметических вычислениях и других формулах, если вам не нужно сравнивать вывод с другими текстовыми строками.

Как добавить ведущие нули в текстовые строки

В предыдущих примерах вы узнали, как добавить ноль перед числом в Excel. Но что, если вам нужно поставить нули перед текстовой строкой, например 0A102? В этом случае ни ТЕКСТ, ни пользовательский формат не будут работать, поскольку они работают только с числовыми значениями.

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

Формула 1. Добавление ведущих нулей с помощью функции ПРАВИЛЬНО

Самый простой способ поставить ведущие нули перед текстовыми строками в Excel — использовать функцию ПРАВИЛЬНО:

• "0000" – это максимальное количество нулей, которое вы хотите добавить. Например, чтобы добавить 2 нуля, введите "00".
• Ячейка – это ссылка на ячейку, содержащую исходное значение.
• Длина_строки — количество символов, которое должна содержать результирующая строка.

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

Что делает формула, так это добавляет 6 нулей к значению в A2 ("000000"&A2), а затем извлекает правильные 6 символов. В результате он вставляет ровно столько нулей, сколько необходимо для достижения заданного общего предела строки:

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

В зависимости от вашей бизнес-логики вы можете указать разное количество нулей и общее количество символов, например:

В результате вы получите строки переменной длины, содержащие до двух ведущих нулей:

Формула 2. Дополнение ведущими нулями с помощью функций ПОВТОР и ДЛСТР

Еще один способ вставить начальные нули перед текстовой строкой в ​​Excel — это сочетание функций ПОВТОР и ДЛСТР:

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

Как работает эта формула:

Зная, что функция ПОВТОР повторяет заданный символ заданное количество раз, а ДЛСТР возвращает общую длину строки, логику формулы легко понять:

• LEN(A2) получает общее количество символов в ячейке A2.
• REPT(0, 6-LEN(A2)) добавляет необходимое количество нулей. Чтобы вычислить, сколько нулей нужно добавить, вы вычитаете длину строки в A2 из максимального количества нулей.
• Наконец, вы объединяете нули со значением A2 и получаете следующий результат:

Итог: эта формула может добавлять ведущие нули как к числам, так и к текстовым строкам, но результатом всегда будет текст, а не число.

Как добавить фиксированное количество предшествующих нулей

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

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

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

Таким же образом вы можете вставить 2 начальных нуля (00), 3 нуля (000) или любое количество нулей перед числами и текстовыми строками.

Итог. Результатом этой формулы также является текстовая строка, даже если вы объединяете нули с числами.

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

Метод, который вы используете для удаления лидирующих нулей в Excel, зависит от того, как эти нули были добавлены:

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

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

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

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

Удалить ведущие нули, преобразовав текст в число

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

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

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

Где A2 – это ячейка, из которой вы хотите удалить предшествующие нули.

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

На следующем снимке экрана показаны две формулы:

• Формула текста в ячейке B2 добавляет нули к значению в ячейке A2 и
• Формула Value в C2 удаляет начальные нули из значения в B2.

Как скрыть нули в Excel

Если вы не хотите отображать нулевые значения на листе Excel, у вас есть два следующих варианта:

Для этого выберите ячейки, в которых вы хотите скрыть нули, нажмите Ctrl+1, чтобы открыть диалоговое окно Формат ячеек, выберите Пользовательский в разделе Категория и введите приведенный выше код формата в поле Тип.

На снимке экрана ниже показано, что ячейка B2 содержит нулевое значение, но оно не отображается в ячейке:

Легкий способ добавления и удаления нулей в Excel

Наконец, хорошие новости для пользователей Ultimate Suite for Excel: выпущен новый инструмент, специально разработанный для работы с нулями! Приветствуем добавление/удаление начальных нулей.

Как обычно, мы постарались сократить количество ходов до абсолютного минимума :)

Чтобы добавить ведущие нули, выполните следующие действия:

1. Выберите целевые ячейки и запустите инструмент Добавить/удалить ведущие нули
2. Укажите общее количество отображаемых символов.
3. Нажмите Применить.

Для удаления ведущих нулей шаги очень похожи:

1. Выберите ячейки со своими номерами и запустите надстройку.
2. Укажите, сколько символов должно отображаться. Чтобы получить максимальное количество значащих цифр в выбранном диапазоне, нажмите Получить максимальную длину
3. Нажмите Применить.

Надстройка может добавлять ведущие нули как к числам, так и к строкам:

• Для чисел задается пользовательский числовой формат, т. е. изменяется только визуальное представление числа, а не базовое значение.
• Буквенно-цифровые строки начинаются с ведущих нулей, т. е. нули физически вставляются в ячейки.

Вот как вы можете добавлять, удалять и скрывать нули в Excel. Чтобы лучше понять методы, описанные в этом руководстве, вы можете загрузить образец книги. Я благодарю вас за чтение и надеюсь увидеть вас в нашем блоге на следующей неделе!

Читайте также:

  
• Как настроить графический планшет для Paint Tool sai
  
• Что такое плагины в After Effects
  
• Программное обеспечение для дизайна мебели для iphone
  
• По умолчанию для всех ячеек таблицы ms excel установлен формат ввода данных
  
• Информация, представленная в компьютере в виде двоичного кода, представляет собой импульсный программный алгоритм данных