Як видалити провідні нулі в Excel (5 простих способів)

Багато людей мають стосунки любові і ненависті з провідними нулями в Excel.

Іноді тобі цього хочеться, а інколи ні.

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

У цьому підручнику Excel я покажу вам як видалити провідні нулі у ваших номерах в Excel.

Тож почнемо!

Можливі причини, чому у вас можуть бути провідні нулі в Excel

Як я вже згадував, Excel автоматично видаляє будь -які початкові нулі з чисел. Наприклад, якщо ви введете 00100 у клітинку в Excel, вона автоматично перетворить її на 100.

У більшості випадків це має сенс, оскільки ці провідні нулі насправді не мають сенсу.

Але в деяких випадках вам може цього захотіти.

Ось деякі можливі причини, які можуть призвести до того, що ваші числа збережуть провідні нулі:

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

Спосіб, який ми вибираємо для видалення провідних нулів, буде залежати від того, що його викликає.

Читайте також: Як додати провідні нулі в Excel

Отже, перший крок - визначити причину, щоб ми могли вибрати правильний метод видалення цих провідних нулів.

Як видалити провідні нулі з чисел

Існує кілька способів видалення початкових нулів з чисел.

У цьому розділі я покажу вам п'ять таких методів.

Перетворіть текст на числа за допомогою параметра перевірки помилок

Якщо причина початкових чисел полягає в тому, що хтось додав апостроф перед цими числами (для перетворення їх у текст), ви можете скористатися методом перевірки помилок, щоб перетворити їх назад у числа одним натисканням кнопки.

Це, мабуть, найпростіший спосіб позбутися початкових нулів.

Тут у мене є набір даних, де я маю числа, які мають апостроф перед цими, а також провідні нулі. Це також причина, чому ці цифри вирівнюються ліворуч (тоді як за замовчуванням цифри вирівнюються праворуч), а також мають провідні 0.

Нижче наведено кроки, щоб видалити ці провідні нулі з цих чисел:

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

Це воно! Вищезазначені кроки видалять апостроф і перетворюють ці текстові значення назад у числа.

А оскільки Excel запрограмовано видаляти провідні пробіли з будь -яких чисел за замовчуванням, ви побачите, що це автоматично видаляє всі провідні нулі.

Примітка: Цей метод не буде працювати, якщо провідні нулі були додані як частина користувацького форматування осередків. Щоб вирішити ці випадки, скористайтеся описаними нижче методами.

Змініть користувацьке форматування клітинок

Інша дійсно поширена причина, через яку ваші числа можуть відображатися з провідними нулями, це коли ваші клітинки були відформатовані так, щоб у кожному номері завжди відображалася певна кількість цифр.

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

Наприклад, якщо ви хочете, щоб усі числа відображалися як 5-значні числа, якщо у вас є лише тризначне число, Excel автоматично додасть до нього дві початкові нулі.

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

І спосіб позбутися цих провідних нулів - це просто видалити наявне форматування з комірок.

Нижче наведено кроки для цього.

1. Виділіть клітинки, які мають числа з початковими нулями
2. Натисніть вкладку Домашня сторінка
3. У групі Числа натисніть спадне меню Формат числа
4. Виберіть "Загальні"

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

Зауважте, що ця техніка буде працювати лише тоді, коли причиною початкових нулів стало форматування нестандартних чисел. Це не спрацювало б, якщо для перетворення чисел у текст використовувався апостроф (у цьому випадку слід скористатися попереднім методом)

Помножте на 1 (використовуючи спеціальну техніку вставки)

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

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

Нижче наведено кроки для цього

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

Наведені вище кроки додають 0 до діапазону вибраних клітинок, а також видаляють усі провідні нулі та апостроф.

Хоча це не змінює значення осередку, воно перетворює всі текстові значення в числа, а також копіює форматування з порожньої комірки, яку ви скопіювали (тим самим замінюючи існуюче форматування, яке викликало появу провідних нулів).

Цей метод впливає лише на цифри. Якщо у клітинці є текстовий рядок, він залишиться незмінним.

Використання функції VALUE

Ще один швидкий і простий метод видалення провідних нулів - це використання функції значення.

Ця функція бере один аргумент (це може бути текст або посилання на клітинку з текстом) і повертає числове значення.

Це також буде працювати в обох сценаріях, коли ваші провідні числа є результатом апострофа (використовується для перетворення чисел у текст) або користувацького форматування чисел.

Припустимо, у мене є набір даних, як показано нижче:

Нижче наведена формула, яка видалить провідні нулі:

= VALUE (A1)

Примітка: Якщо ви все ще бачите провідні нулі, вам потрібно перейти на вкладку "Домашня сторінка" та змінити формат комірки на Загальний (зі спадного меню Формат числа).

Використання тексту в стовпці

Хоча функціонал Text to Columns використовується для поділу комірки на кілька стовпців, ви також можете використовувати її для видалення початкових нулів.

Припустимо, у вас є набір даних, як показано нижче:

Нижче наведено кроки для видалення початкових нулів за допомогою тексту в стовпці:

1. Виберіть діапазон клітинок, які мають числа
2. Натисніть на вкладку «Дані»
3. У групі Інструменти даних натисніть "Текст у стовпці"
4. У майстрі "Перетворення тексту в стовпці" внесіть такі зміни:
   1. Крок 1 із 3: Виберіть "Розмежовано" і натисніть Далі
   2. Крок 2 з 3: Скасуйте вибір усіх роздільників і натисніть Далі
   3. Крок 3 з 3: Виберіть осередок призначення (в даному випадку В2) і натисніть кнопку Готово

Наведені вище кроки повинні видалити всі провідні нулі і дати вам лише числа. Якщо ви все ще бачите провідні нулі, вам потрібно змінити форматування клітинок на Загальне (це можна зробити на вкладці Домашня сторінка)

Як видалити провідні нулі з тексту

Хоча всі перераховані вище методи працюють чудово, вони призначені лише для тих клітин, які мають числове значення.

Але що, якщо у вас є буквено -цифрові або текстові значення, які також мають деякі провідні нулі.

Вищеописані методи не спрацювали б у такому випадку, але завдяки дивовижним формулам у Excel ви все одно зможете отримати цей час.

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

Нижче наведена формула для цього:

= ВПРАВО (A2, LEN (A2) -ЗНАЙТИ (ВЛІВО (ЗАМІНА (A2, "0", ""), 1), A2) +1)

Дозвольте мені пояснити, як працює ця формула

Частина формули SUBSTITUTE замінює нуль порожнім. Отже, для значення 001AN76 формула заміни дає результат як 1AN76

Потім формула LEFT витягує крайній лівий символ цього результуючого рядка, який у цьому випадку буде 1.

Потім формула FIND шукає цей крайній лівий символ, заданий формулою LEFT, і повертає її позицію. У нашому прикладі для значення 001AN76 це дасть 3 (це позиція 1 у вихідному текстовому рядку).

1 додається до результату формули FIND, щоб переконатися, що ми отримуємо весь текстовий рядок (крім початкових нулів)

Результат формули FIND потім віднімається від результату формули LEN (яка використовується для визначення довжини всього текстового рядка). Це дає нам довжину кільця тексту без провідних нулів.

Потім це значення використовується з функцією RIGHT для вилучення всього текстового рядка (крім початкових нулів).

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

Отже, нова формула з доданою функцією TRIM буде такою, як показано нижче:

= ВПРАВО (ТРИМ (A2), ЛІНІЯ (ОБЛИЧЧЕННЯ (A2))-ЗНАЙТИ (ВЛІВО (ЗАМІНА (ЗАМІНКА (А2), "0", ""), 1), ОБРІЗКА (А2))+1)

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

Сподіваюся, вам цей підручник був корисним!