Как удалить ведущие нули в Excel [5 простых способов]

У многих людей отношения любви и ненависти к ведущим нулям в Excel.Иногда ты этого хочешь, иногда нет.

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

В этом уроке по Excel я покажу вам如何Удалите ведущие нули из чисел в Excel.

Итак, приступим!

Возможные причины ведущих нулей в Excel

Как я уже упоминал, Excel автоматически удаляет все ведущие нули из чисел.Например, если вы введете 00100 в ячейку Excel, оно автоматически преобразуется в 100.

В большинстве случаев это имеет смысл, поскольку начальные нули на самом деле не имеют смысла.

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

Вот несколько причин, по которым в ваших числах могут оставаться ведущие нули:

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

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

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

Как убрать лидирующие нули в числах

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

В этом разделе я покажу вам пять таких методов.

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

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

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

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

Вот шаги, чтобы удалить эти начальные нули из этих чисел:

1. Выберите число, из которого необходимо удалить начальные нули.Вы заметите желтый значок в правом верхнем углу выбора.
2. Щелкните желтый значок проверки ошибок
3. Нажмите «Преобразовать в число».

而已!Вышеуказанные шаги будутудалить апострофи преобразовать эти текстовые значения обратно в числа.

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

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

Изменение пользовательского числового формата ячейки

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

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

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

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

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

Вот шаги, чтобы сделать это:

1. Выберите ячейки с начальными нулевыми числами
2. Щелкните вкладку Главная
3. В группе «Числа» щелкните раскрывающееся меню «Числовой формат».
4. Выберите «Общие»

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

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

Умножить на 1 (используя специальную технику вставки)

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

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

Вот шаги, чтобы сделать это

1. Скопируйте все пустые ячейки на листе
2. Выберите ячейки с номерами, из которых вы хотите удалить начальные нули
3. Щелкните правой кнопкой мыши выделение и выберите Специальная вставка.Откроется диалоговое окно «Специальная вставка».
4. Нажмите на опцию «Добавить» (в группе «Действия»)
5. Нажмите ОК

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

Хотя это не изменяет значение ячейки, оно преобразует все текстовые значения в числа и копирует форматирование из скопированной вами пустой ячейки (тем самым заменяя существующее форматирование, из-за которого появляются начальные нули).

Этот метод влияет только на числа.Если в ячейке есть текстовая строка, она останется неизменной.

Используйте функцию ЗНАЧЕНИЕ

Еще один быстрый и простой способ удалить начальные нули — использовать функцию значения.

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

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

Предположим, у меня есть набор данных, который выглядит так:

Вот формула удаления лидирующих нулей:

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

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

Использовать текст в столбцах

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

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

Вот шаги по удалению начальных нулей с помощью Text to Columns:

1. Выделить диапазон ячеек с числами
2. Нажмите на вкладку Данные
3. В группе «Инструменты данных» нажмите «Текст в столбцы».
4. В мастере преобразования текста в столбцы внесите следующие изменения:
   1. Шаг 1 из 3. Выберите «Отдельно» и нажмите «Далее».
   2. Шаг 2 из 3. Отмените выбор всех разделителей и нажмите «Далее».
   3. Шаг 3 из 3. Выберите целевую ячейку (в данном случае B2) и нажмите «Готово».

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

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

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

Но что, если ваши буквенно-цифровые или текстовые значения также имеют начальные нули.

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

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

Вот формула для этого:

=RIGHT(A2,LEN(A2)-FIND(LEFT(SUBSTITUTE(A2,"0",""),1),A2)+1)

Позвольте мне объяснить, как работает эта формула.

Часть формулы ПОДСТАВИТЬ заменяет нули пробелами.Таким образом, для значения 001AN76 формула замены дает результат 1AN76.

Затем формула LEFT извлекает крайний левый символ этой результирующей строки, в данном случае это 1.

Затем формула НАЙТИ находит крайний левый символ, заданный формулой ЛЕВЫЙ, и возвращает его позицию.В нашем примере для значения 001AN76 это даст 3 (то есть позицию 1 в исходной текстовой строке).

1 добавляется к результату формулы НАЙТИ, чтобы гарантировать, что мы извлекли всю текстовую строку (кроме ведущих нулей).

Затем вычтите результат формулы НАЙТИ из результата формулы ДЛСТР, что дает длину всей текстовой строки.Это дает нам длину текстового цикла без начальных нулей.

Затем это значение используется с функцией ПРАВИЛЬНО для извлечения всей текстовой строки (кроме начальных нулей).

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

Поэтому новая формула с добавлением функции TRIM выглядит так:

=RIGHT(TRIM(A2),LEN(TRIM(A2))-FIND(LEFT(SUBSTITUTE(TRIM(A2),"0",""),1),TRIM(A2))+1)

Поэтому вы можете использовать эти простые методы для удаления ведущих нулей из наборов данных в Excel.

Я надеюсь, что вы нашли этот урок полезным!