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

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

Сьогодні ми розберемо, як видалити їх вручну (все відразу).

Тож почнемо!

Чому Excel не завжди видаляє їх автоматично?

Отже, ви знаєте, що Excel автоматично видаляє всі нулі на початку чисел. Наприклад, якщо ви пропишете в порожній осередок 00123, він автоматично видалить перші два нулі.

Але так не завжди.

Причини, з яких Excel може не чіпати ці нулі:

  • Значення осередку записано у текстовому форматі;
  • Форматування осередку налаштовано так, що вона завжди показує певну кількість чисел. Наприклад, п'ять. У такому разі, якщо записана цифра не складається з п'яти чисел, а складається, наприклад, з трьох чисел, Excel автоматично додасть два нулі на початок значення.

Щоб вибрати спосіб, яким ми видалятимемо ці нулі, спочатку потрібно зрозуміти з якої з причин вони з'явилися в осередку.

Видаляємо ці нулі

Отже, далі ми розглянемо методи видалення нулів на початку чисел.

Змінюємо формат осередку з текстового на числовий

Припустимо, ми визначили, що Excel не видалив нулі через те, що дані в осередку записані в текстовому форматі. У такому разі нам потрібно змінити формат осередку, тоді Excel автоматично видалить ці нулі.

Звичайно, це найпоширеніша і водночас проста проблема.

Ось приклад такої проблеми:

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

Перед усіма числами стоїть знак “'“, що робить значення осередків звичайним текстом. Як це виправити?

Покрокова інструкція:

  • Виділимо потрібні осередки та натиснемо на піктограму помилки;

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

  • Натискаємо «Перетворити на число»;

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

Готово! Ось результат:

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

Ми змінили формат осередків, а Excel видалив усі нулі.

Змінюємо формат осередку на «Загальний»

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

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

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

Гаразд, давайте продовжимо. Ось приклад такої проблеми:

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

Як усунути ці нулі?

Покрокова інструкція:

  • Виділяємо осередки та клацаємо на «Головна»;

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

  • Далі клацніть на полі, де написано “(всі формати)”;

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

  • І встановіть формат “Загальний”;

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

Готово! Ми видалили форматування, яке будь-який користувач вказав вручну. Тепер Excel автоматично видалить нулі.

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

Додамо нуль до числа

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

Допустимо, у нас є така табличка:

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

Давайте приберемо ці нулі!

Покрокова інструкція:

  1. Копіюємо будь-яку комірку з порожнім значенням;
  2. Виділіть осередки з нулями, клацніть правою кнопкою мишки та натисніть «Спеціальна вставка…»;

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

  • Виберіть “Скласти”;

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

  • Підтвердіть.

Що сталося? Ви додали нуль до значення кожного осередку, що автоматично скасувало попереднє форматування.

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

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

Але так можна зробити тільки в тому випадку, якщо в осередку є одні числа. Якщо там є літери або символи — Excel видасть вам помилку.

За допомогою функції ЗНАЧЕНИЙ

Дуже добрий спосіб.

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

Допустимо, у нас є така табличка:

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

Формула для обчислення значення осередку А2:

=ЗНАЧЕНИЙ(A2)

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

Якщо після використання функції, нулі все ще на місці – поміняйте формат осередку на Загальний.

За допомогою функції “Текст по стовпцях”

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

Допустимо, у нас є така табличка:

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

Давайте вилучимо нулі!

Покрокова інструкція:

  • Виділіть осередки, в яких потрібно видалити нулі та клацніть на «Дані»;

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

  • Далі – “Текст по стовпцях”;

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

  • У вікні, що відкрилося:
    • “З роздільниками”;

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

  • Заберіть усі галочки та клацніть «Далі > >»;

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

  • Виберіть формат Загальний;

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

Готово!

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

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

Але, що робити, якщо в осередках знаходяться не тільки числа, а й текст?

Вихід є, читайте далі.

Допустимо, у нас є така табличка:

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

Ми можемо використовувати таку функцію:

=ПРАВСИМВ(A2;ДЛСТР(A2)-ЗНАЙТИ(ЛЕВСИМВ(ПІДСТАВИТИ(A2; “0”;””);1);A2)+1)

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

Що вона робить?

Функція ПІДСТАВИТИ в цій формулі, замінює нулі на порожнє місце.

Функція ЛЕВСІМВ витягує найлівіший символ.

Функція ЗНАЙТИ шукає «адресу» цього лівого символу щодо початку рядка.

Далі число 1 буде додано до результату, здобутого функцією ЗНАЙТИ. Щоб переконатися, що ми перевірили весь рядок і нічого не забули.

Далі з результату роботи функції ДЛСТР віднімається результат роботи ЗНАЙТИ.

А далі ПРАВСІМВ витягує весь рядок без нулів.

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

Ось вона:

В(СЖПРОБІЛИ(A2);ДЛСТР(СЖПРОБІЛИ(A2))-ЗНАЙТИ(ЛЕВСІМВ(ПІДСТАВИТИ(СЖПРОБІЛИ(A2); “0”;””);1);СЖПРОБІЛИ(A2))+1)

От і все! Я показав вам кілька способів (яких мені вистачає) для видалення нулів на початку числа.

Сподіваюся, ця стаття виявилася корисною для вас!

Читайте також
nBook - найцікавіше зі світу IT, Hi-Tech