Я знаю, нулі на початку чисел багатьох дратують. І начебто Excel автоматично видаляє їх, але не завжди.
Сьогодні ми розберемо, як видалити їх вручну (все відразу).
Тож почнемо!
Чому Excel не завжди видаляє їх автоматично?
Отже, ви знаєте, що Excel автоматично видаляє всі нулі на початку чисел. Наприклад, якщо ви пропишете в порожній осередок 00123, він автоматично видалить перші два нулі.
Але так не завжди.
Причини, з яких Excel може не чіпати ці нулі:
- Значення осередку записано у текстовому форматі;
- Форматування осередку налаштовано так, що вона завжди показує певну кількість чисел. Наприклад, п'ять. У такому разі, якщо записана цифра не складається з п'яти чисел, а складається, наприклад, з трьох чисел, Excel автоматично додасть два нулі на початок значення.
Щоб вибрати спосіб, яким ми видалятимемо ці нулі, спочатку потрібно зрозуміти з якої з причин вони з'явилися в осередку.
Видаляємо ці нулі
Отже, далі ми розглянемо методи видалення нулів на початку чисел.
Змінюємо формат осередку з текстового на числовий
Припустимо, ми визначили, що Excel не видалив нулі через те, що дані в осередку записані в текстовому форматі. У такому разі нам потрібно змінити формат осередку, тоді Excel автоматично видалить ці нулі.
Звичайно, це найпоширеніша і водночас проста проблема.
Ось приклад такої проблеми:
Перед усіма числами стоїть знак “'“, що робить значення осередків звичайним текстом. Як це виправити?
Покрокова інструкція:
- Виділимо потрібні осередки та натиснемо на піктограму помилки;
- Натискаємо «Перетворити на число»;
Готово! Ось результат:
Ми змінили формат осередків, а Excel видалив усі нулі.
Змінюємо формат осередку на «Загальний»
Часта проблема – ви отримали файл таблиці від будь-кого, а там ці нулі. Визначили, що вся справа у форматі осередків. Але він не текстовий, а користувальницький. Тобто людина, яка робила таблицю, встановила такий формат осередків, у якому присутні ці нулі, через що ми тепер маємо таку проблему.
Ця людина могла зробити це просто для того, щоб у всіх осередках числа були однаковою довжиною.
Наприклад, вам потрібно зробити так, щоб у всіх осередках було п'ять цифр. А якщо ви запишете якесь число, в якому менше п'яти цифр, Excel автоматично додасть нулі на початку цих чисел.
Гаразд, давайте продовжимо. Ось приклад такої проблеми:
Як усунути ці нулі?
Покрокова інструкція:
- Виділяємо осередки та клацаємо на «Головна»;
- Далі клацніть на полі, де написано “(всі формати)”;
- І встановіть формат “Загальний”;
Готово! Ми видалили форматування, яке будь-який користувач вказав вручну. Тепер Excel автоматично видалить нулі.
І так, є невелике застереження. Цей спосіб спрацює тільки в тому випадку, якщо нулі з'явилися через те, що так було зазначено при форматуванні осередків.
Додамо нуль до числа
Цей спосіб спрацює в тому випадку, якщо дані в осередках знаходяться в текстовому форматі або нулі з'явилися через те, що так було вказано при форматуванні осередків (форматування користувача).
Допустимо, у нас є така табличка:
Давайте приберемо ці нулі!
Покрокова інструкція:
- Копіюємо будь-яку комірку з порожнім значенням;
- Виділіть осередки з нулями, клацніть правою кнопкою мишки та натисніть «Спеціальна вставка…»;
- Виберіть “Скласти”;
- Підтвердіть.
Що сталося? Ви додали нуль до значення кожного осередку, що автоматично скасувало попереднє форматування.
Суть у тому, що форматування копіюється з того осередку, який ви скопіювали у першому кроці (з порожнім значенням). Таким чином, значення осередків не змінюється, а нулі забираються.
Але так можна зробити тільки в тому випадку, якщо в осередку є одні числа. Якщо там є літери або символи — Excel видасть вам помилку.
За допомогою функції ЗНАЧЕНИЙ
Дуже добрий спосіб.
Функція приймає на вхід дані (або посилання на комірку), які необхідно обробити і віддати тільки значення, без форматування.
Допустимо, у нас є така табличка:
Формула для обчислення значення осередку А2:
=ЗНАЧЕНИЙ(A2)
Якщо після використання функції, нулі все ще на місці – поміняйте формат осередку на Загальний.
За допомогою функції “Текст по стовпцях”
Ця функція спочатку ділить текст на кілька стовпчиків за допомогою зазначеного критерію. Але насправді вона може бути використана в різних ситуаціях для самих різних цілей.
Допустимо, у нас є така табличка:
Давайте вилучимо нулі!
Покрокова інструкція:
- Виділіть осередки, в яких потрібно видалити нулі та клацніть на «Дані»;
- Далі – “Текст по стовпцях”;
- У вікні, що відкрилося:
- “З роздільниками”;
- Заберіть усі галочки та клацніть «Далі > >»;
- Виберіть формат Загальний;
Готово!
Як видалити нулі на початку значення осередку (з текстом)
Усі попередні розділи статті присвячені ситуаціям, коли у осередках містяться лише числа.
Але, що робити, якщо в осередках знаходяться не тільки числа, а й текст?
Вихід є, читайте далі.
Допустимо, у нас є така табличка:
Ми можемо використовувати таку функцію:
=ПРАВСИМВ(A2;ДЛСТР(A2)-ЗНАЙТИ(ЛЕВСИМВ(ПІДСТАВИТИ(A2; “0”;””);1);A2)+1)
Що вона робить?
Функція ПІДСТАВИТИ в цій формулі, замінює нулі на порожнє місце.
Функція ЛЕВСІМВ витягує найлівіший символ.
Функція ЗНАЙТИ шукає «адресу» цього лівого символу щодо початку рядка.
Далі число 1 буде додано до результату, здобутого функцією ЗНАЙТИ. Щоб переконатися, що ми перевірили весь рядок і нічого не забули.
Далі з результату роботи функції ДЛСТР віднімається результат роботи ЗНАЙТИ.
А далі ПРАВСІМВ витягує весь рядок без нулів.
Якщо у ваших осередках можуть зустрітися прогалини на початку чи наприкінці значень, то використовуйте формулу, яку покажу далі.
Ось вона:
В(СЖПРОБІЛИ(A2);ДЛСТР(СЖПРОБІЛИ(A2))-ЗНАЙТИ(ЛЕВСІМВ(ПІДСТАВИТИ(СЖПРОБІЛИ(A2); “0”;””);1);СЖПРОБІЛИ(A2))+1)
От і все! Я показав вам кілька способів (яких мені вистачає) для видалення нулів на початку числа.
Сподіваюся, ця стаття виявилася корисною для вас!