Як додати провідні нулі в Excel

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

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

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

Додавання провідних нулів в Excel

По суті, є 2 методи, які ви можете використовувати для додавання нулів на початку: один, відформатуйте свій номер як «текст»; по-друге, використовуйте користувацьке форматування для додавання провідних нулів. Спосіб, який ви хочете використовувати, може залежати від того, що ви хочете зробити з числом.

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

Існує кілька способів додавати або додавати нулі перед числами, форматуючи їх як текст:

Зміна формату комірки на Текст
Додавання апострофа (‘)
Використання функції ТЕКСТ
Використання функції REPT/LEN
Використовуйте функцію CONCATENATE/оператор амперсанда (&)
Використання функції ПРАВО

Зміна формату комірки на текст

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

Виберіть комірку(и), до якої потрібно додати нулі. Перейдіть на вкладку «Головна», клацніть спадне вікно «Формат» у групі «Числа» та виберіть «Текст» із параметрів формату.

Тепер, коли ви вводите свої числа, Excel не видалятиме з нього жодний провідний нуль.

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

Щоб видалити повідомлення про помилку, виділіть клітинку(и), натисніть на попереджувальний знак, а потім виберіть зі списку «Ігнорувати помилку».

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

Використання Leading апостроф (')

Інший спосіб додати нулі в Excel – це додати апостроф (‘) на початку числа. Це змусить Excel ввести число у вигляді тексту.

Просто введіть апостроф перед будь-якими цифрами та натисніть «Enter». Excel залишить початкові нулі недоторканими, але (‘) не буде видно на аркуші, якщо ви не виберете клітинку.

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

Наведений вище метод додає нулі до чисел під час їх введення, але якщо у вас уже є список чисел і ви хочете розставити перед ними нулі, то функція TEXT є правильним методом для вас. Функція TEXT дозволяє перетворювати числа в текстові рядки, застосовуючи користувацьке форматування.

Синтаксис функції TEXT:

= ТЕКСТ (значення, формат_текст)

де,

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

За допомогою функції ТЕКСТ ви можете вказати, скільки цифр має містити ваш номер. Наприклад, якщо ви хочете, щоб ваші числа складалися з 8 цифр, введіть 8 нулів у другий аргумент функції: «00000000». Якщо у вас є 6-значне число в клітинці, то функція вручну додасть 2 провідних нулі, а якщо у вас є 2-значне число, наприклад 56, решта буде нулями (00000056).

Наприклад, щоб додати нулі на початку і зробити числа шестизначними, скористайтеся цією формулою:

=ТЕКСТ(A2,"000000")

Оскільки ми маємо 6 нулів у другому аргументі формули, функція перетворює числовий рядок у текстовий рядок і додає 5 нулів на початку, щоб рядок мав 6 цифр.

Примітка: Будь ласка, не забудьте взяти коди формату в подвійні лапки у функції.

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

Функція TEXT завжди повертає значення як текстовий рядок, а не число, тому ви не зможете використовувати їх в арифметичних обчисленнях, але все одно можете використовувати їх у формулах пошуку, таких як VLOOKUP або INDEX/MATCH, щоб отримати деталі продукт за допомогою ідентифікаторів продуктів.

Використання функції CONCATENATE/оператора амперсанда (&)

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

Синтаксис функції CONCATENATE:

=КОНКАТЕНАТИ (текст1, [текст2], ...)

де,

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

текст2 - Оригінальний номер або посилання на клітинку

Синтаксис оператора Амперсанд:

=Значення_1 і Значення_2

Де,

Значення_1 — це нулі, які слід вставити перед числом, а значення_2 — це число.

Наприклад, щоб додати лише два нулі перед числом, скористайтеся однією з формул:

=CONCATENATE("00",A2)

Першим аргументом є два нулі («00»), тому що ми хочемо додати два нулі перед числом в A2 (яке є другим аргументом).

або,

="00"&A2

Тут перший аргумент — це 2 нулі, за яким слідує оператор «&», а другий аргумент — число.

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

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

Використання функції REPT/LEN

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

=REPT(текст, кількість_разів)

Де «текст» — це символ, який ми хочемо повторити (у нашому випадку «0»), а аргумент «число_разів» — це кількість разів, коли ми хочемо повторити цей символ.

Наприклад, щоб створити п’ять нулів перед числами, формула буде виглядати так:

=REPT(0,5)&A2

Формула повторює 5 нулів, об’єднує рядок чисел в A2 і повертає результат. Потім формула застосовується до клітинки B2:B6 за допомогою маркера заповнення.

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

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

Синтаксис:

=REPT(текст, число_раз-LEN(текст))&клітка

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

=REPT(0,5-LEN(A2))&A2

Тут «LEN(A2)» отримує загальну довжину рядка/чисел у клітинці A2. «5» — це максимальна довжина рядка/чисел, які повинна мати клітинка. А частина «REPT(0,5-LEN(A2))» додає кількість нулів, віднімаючи довжину рядка в A2 від максимальної кількості нулів (5). Потім ряд 0 об’єднується перед значенням A2, щоб утворити рядок фіксованої довжини.

Використання функції ПРАВО

Інший спосіб додавання провідних нулі перед рядком у Excel - це використання функції ПРАВО.

Функція ВПРАВО може додати кількість нулів до початку числа та витягти із значення N символів крайнього правого краю.

Синтаксис:

= ВПРАВО (текст, кількість_символів)

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

Для цього методу ми об’єднуємо максимальну кількість нулів із посиланням на клітинку, яка містить рядок у аргументі «текст».

Щоб створити 6-значне число на основі числового рядка в A з провідними нулями, спробуйте цю формулу:

=ВПРАВО("0000000"&A2,6)

Перший аргумент (текст) формули додає 7 нулів до значення в A2 (“0000000”&A2), а потім повертає 7 крайніх правих символів, що призводить до деяких нулів на початку.

Додавання початкових нулів за допомогою спеціального форматування чисел

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

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

Щоб змінити форматування чисел клітинок, виконайте такі дії:

Виберіть клітинку або діапазон комірок, у яких потрібно відобразити провідні нулі. Потім клацніть правою кнопкою миші в будь-якому місці вибраного діапазону та виберіть опцію «Формат клітинок» у контекстному меню. Або натисніть комбінацію клавіш Ctrl + 1.

У вікні Формат комірок перейдіть на вкладку «Число» та виберіть «Спеціальний» під параметрами категорії.

Введіть кількість нулів у полі «Тип:», щоб вказати загальну кількість цифр, які потрібно відобразити в клітинці. Наприклад, якщо ви хочете, щоб число складалося з 6 цифр, введіть «000000» як код спеціального формату. Потім натисніть «ОК», щоб застосувати.

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

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

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

0 – Це заповнювач цифри, який відображає додаткові нулі. Він відображає примусові цифри 0-9, незалежно від того, чи відповідає цифра значенню. Наприклад, якщо ви введете 2.5 з кодом формату 000.00, то відображатиметься 002.50.
# – Це заповнювач цифр, який відображає додаткові цифри і не містить зайвих нулів. Наприклад, якщо ви введете 123 з кодом форматування 000#, він відобразить 0123.

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

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

Код форматування в діалоговому вікні Формат клітинок:

Результат:

Давайте застосуємо цей код форматування в наступному прикладі:

##0000

Як бачите, «0» додасть додаткові нулі, а «#» не додасть незначні нулі:

Ви також можете використовувати попередньо визначені коди формату в розділі «Спеціальні формати» діалогового вікна Формат клітинок для поштових індексів, номерів телефонів та номерів соціального страхування.

У наступній таблиці показано числа з провідними нулями, де до різних стовпців застосовуються різні коди «Спеціального» формату:

Видалення початкових нулів в Excel

Тепер, коли ви дізналися, як додавати перші нулі в Excel, давайте подивимося, як видалити перші нулі з числа рядків. Іноді, коли ви імпортуєте дані із зовнішнього джерела, числа можуть мати префікс нулів і форматуватися як текст. У таких випадках вам потрібно видалити перші нулі та перетворити їх назад у числа, щоб ви могли використовувати їх у формулах.

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

Видалити провідні нулі, змінивши форматування клітинок

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

Щоб видалити нулі з префіксом, виділіть клітинки з початковими нулями, клацніть поле «Формат числа» та виберіть параметр форматування «Загальний» або «Числовий».

Тепер провідні нулі зникли:

Видаліть початкові нулі, перетворивши текст на числа

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

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

Виберіть ці клітинки, і ви побачите жовте попередження у верхній правій частині виділення. Потім натисніть опцію «Перетворити в число» у спадному меню.

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

Видалення провідних нулів шляхом Помножити/Поділити на 1

Ще один простий і найкращий спосіб видалити початок – це помножити або поділити числа на 1. Ділення або множення значення не змінює значення, воно просто перетворює значення назад у число та видаляє нулі на початку.

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

Потім застосуйте цю формулу до інших клітинок за допомогою маркера заповнення.

Ви можете досягти тих же результатів, використовуючи команду «Спеціальна вставка». Ось як:

Введіть числове значення «1» у клітинку (скажімо, в B2) і скопіюйте це значення.

Далі виділіть клітинки, в яких потрібно видалити провідні нулі. Потім клацніть правою кнопкою миші на виділенні, а потім виберіть опцію «Спеціальна вставка».

У діалоговому вікні «Спеціальна вставка» в розділі «Операція» виберіть «Помножити» або «Поділити» і натисніть «ОК».

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

Видалення провідних нулів за допомогою формул

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

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

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

Іноді ви можете видалити перші нулі, але залишити числа в текстовому форматі. У таких випадках потрібно використовувати функції TEXT() і VALUE () разом, наприклад:

=ТЕКСТ(ЗНАЧЕННЯ(A1),"#")

Функція VALUE перетворює значення в A1 в число. Але другий аргумент «#» перетворює значення назад у текстовий формат без зайвих нулів. В результаті ви отримаєте числа без провідних нулів, але все ще у текстовому форматі (вирівнювання по лівому краю).

Видаліть початкові нулі за допомогою функції «Текст у стовпці» в Excel

Ще один спосіб вилучити нулі на початку — це використовувати функцію «Текст до стовпців» у Excel.

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

Далі перейдіть на вкладку «Дані» та натисніть кнопку «Текст у стовпці» в групі «Інструменти даних».

З’явиться майстер «Перетворити текст у стовпці». На кроці 1 з 3 виберіть «З роздільниками» та натисніть «Далі».

На кроці 2 з 3 зніміть прапорці з усіх роздільників і натисніть «Далі».

На останньому кроці залиште параметр «Формат даних стовпця» як «Загальний» і виберіть пункт призначення (першу клітинку діапазону), де ви хочете отримати числа без передніх нулів. Потім натисніть «Готово»

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

Це все.