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

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

Грубо кажучи, потрібно щоб числа прописувалися так само, як на малюнку нижче.

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

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

Кожен спосіб, природно, має свої плюси і мінуси, докладніше про це я розповім у самій статті.

Спосіб 1: змінюємо формат комірки

у нас є «ID» товарів магазину, як на малюнку нижче.

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

І нам потрібно записувати ці самі ID, наприклад, як 00001, а Excel автоматично переписує його в 1.

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

Справа в тому, що Excel намагається привести числа до простого вигляду. Він порівнює числа, наприклад, 00002 і 2, розуміє що вони однакові (саме числа) і перетворює 00002 в простий вид 2.

Але нам все одно необхідно записати 00002, тоді ми можемо змінити формат осередку на текстовий.

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

  • Виділіть осередки з числами, які потрібно записати з нулями;
  • Клацніть у розділі «Головна» та у форматі виберіть «Текстовий»;

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

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

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

Спосіб 2: вказуємо формат відображення чисел

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

В чому суть:

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

Таким чином, вміст осередку не зміниться.

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

  • Виділіть потрібні осередки;
  • Клацніть «Головна» та натисніть на стрілку, що дивиться вниз, у розділі «Число»;

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

  • У вікні, у вкладці «Число», клацніть на «(всі формати)»;

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

  • У полі “Тип” (де зараз написано “Основний”) напишіть “00000”;

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

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

Отже, що сталося: ми змінили вигляд відображення всіх чисел у виділених осередках на п'ятизначний. Це означає, що всі числа в цих осередках відображатимуться мінімум у п'ятизначному форматі. Наприклад, число “2” буде відображатися як “00002”. А число “372847” буде відображатися як “372847”.

Загалом тут все залежить від вашої ситуації, нулів у полі «Тип» можна вписати стільки, скільки вам потрібно. Можна зробити десятизначний формат відображення тощо.

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

Спосіб 3: за допомогою функції тексту

Отже, важливо розуміти, що функція ТЕКСТ змінює формат самого значення, а не всієї комірки.

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

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

Формула:

= Текст (A2, “00000”)

Для числа, що зберігається в осередку “A2”.

Отже, число записуватиметься у п'ятизначному форматі (якщо число було “3”, то стане “00003”).

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

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

Спосіб 4: змінюємо довжину рядка

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

Але в такій ситуації можна використовувати метод з функціями ПОВТОР і ДЛСТР.

Формула:

=ПОВТОР(0;5-ДЛСТР(A2))&A2

При виклику такої функції довжина рядка зміниться на п'ятизначну. Це означає, що всі числа будуть записані у п'ятизначному форматі (наприклад, число “3” буде записано як “00003”).

Що робить ця функція:

  • Функція ДЛСТР(A2) повертає довжину рядка з числом.
  • Функція =ПОВТОР(0;5-ДЛСТР(A2)) вважає, скільки нулів необхідно додати до початку нашого числа. І так для кожного нового рядка.

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

Спосіб 5: використовуємо Visual Basic

Спосіб з використанням Visual Basic відмінно підійде тим, кому дуже часто потрібно записувати числа певним чином.

Цей код створює вашу власну функцію для такого завдання:

Function AddLeadingZeroes(ref As Range, Length As Integer) Dim i As Integer Dim Result As String Dim StrLen As Integer StrLen = Len(ref) For i = 1 To Length If i <= StrLen Then Result = Result & Mid(ref, i , 1) Else Result = "0" & Result End If Next i AddLeadingZeroes = Result End Function

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

А ще можна поділитись цією функцією з колегами.

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