Вам колись доводилось імпортувати або вводити в Excel дані, що містять початкові нулі (наприклад, 00123) чи великі числа (наприклад, 1234 5678 9087 6543)? Це можуть бути номери соціального страхування, номери телефонів, кредитних карт або рахунків, коди продуктів чи поштові індекси. Excel автоматично вилучає початкові нулі та подає великі числа в експоненційному форматі (наприклад, 1,23E+15), щоб ви могли використовувати їх у формулах і математичних операціях. З цієї статті ви дізнаєтеся, як зберегти дані у вихідному форматі, який Excel інтерпретує як текст.
Установлення автоматичних перетворень даних
Увага!: Ця функція доступна в програмах Excel для Microsoft 365, Excel для Microsoft 365 для Mac, Excel 2024, Excel 2024 для Mac.
Використовуйте функцію автоматичного перетворення даних Excel, щоб змінити стандартну поведінку Excel для таких автоматичних перетворень даних:
-
Видалення початкових нулів із числового тексту та перетворення на число.
-
Обтинання числових даних до 15 розрядів точності та перетворення на число, яке відображається в експоненційному нотації.
-
Перетворення числових даних навколо букви "Е" на наукову нотацію.
-
Перетворення безперервного рядка букв і чисел на дату.
Докладні відомості див. в статті Установлення автоматичних перетворень даних.
Перетворення чисел на текст під час імпорту текстових даних
Щоб відформатувати окремі стовпці як текст під час імпорту даних до Excel, скористайтеся набором функцій Завантажити та перетворити (Power Query). Так ви імпортуєте текстовий файл, проте дані пройдуть через етапи перетворення, передбачені для імпорту даних з інших джерел, як-от XML, Інтернет, JSON тощо.
-
Перейдіть на вкладку Дані та натисніть кнопку З тексту/CSV поруч із кнопкою Отримати дані . Якщо кнопка Отримати дані не відображається, виберіть Новий запит > З файлу > З тексту, а потім знайдіть потрібний текстовий файл і натисніть кнопку Імпорт.
-
Excel завантажить дані до області перегляду. Натисніть кнопку Редагувати в області перегляду, щоб відобразити область Редактор запитів.
-
Якщо певні стовпці потрібно перетворити на текст, виберіть їх, клацнувши заголовок, а потім на вкладці Головна в групі Перетворення виберіть Текст зі списку Тип даних.
Порада.: Щоб вибрати кілька стовпців, клацніть їх лівою кнопкою миші, утримуючи натиснутою клавішу Ctrl.
-
Потім у діалоговому вікні Змінення типу стовпця натисніть кнопку Замінити поточне, і Excel перетворить вибрані стовпці на текст.
-
Завершивши, натисніть кнопку Закрити & Завантажити, і Excel поверне дані запиту на аркуш.
Якщо в майбутньому ваші дані зміняться, на вкладці Дані натисніть кнопку Оновити все. Excel автоматично оновить і перетворить дані.
Використання спеціального формату для збереження початкових нулів
Якщо це завдання потрібно вирішити в межах книги, що не слугує джерелом даних для інших програм, можна скористатися спеціальним або додатковим форматом, щоб зберегти початкові нулі. Цей спосіб підходить для цифрових кодів, які містять менше 16 цифр. Цифрові коди можна також відформатувати з використанням дефісів або інших розділових знаків. Наприклад, щоб зробити телефонні номери зручнішими для читання, можна додати дефіси між міжнародним кодом, кодом області, префіксом та іншими цифрами.
|
Цифровий код |
Приклад |
Спеціальний числовий формат |
|---|---|---|
|
Соціальний безпека |
012345678 |
000-00-0000 012-34-5678 |
|
Телефон |
0012345556789 |
00-0-000-000-0000 00-1-234-555-6789 |
|
Поштові код |
00123 |
00000 грн. 00123 |
Інструкції
-
Виберіть клітинку або діапазон клітинок, які потрібно відформатувати.
-
Натисніть клавіші Ctrl+1, щоб відкрити діалогове вікно Формат клітинок.
-
Перейдіть на вкладку Число , а потім у списку Числові формати виберіть пункт Настроюваний , а потім у полі Тип введіть числовий формат, наприклад 000-00-0000 для коду соціального страхування, або 00000 для п'ятизначного поштового індексу.
Порада.: Ви також можете вибрати пункт Спеціальний, а потім вибрати поштовий індекс, поштовий індекс + 4, номер телефону або номер соціального страхування.
Докладні відомості про спеціальні формати див. в статті Створення та видалення спеціального числового формату.
Примітка.: Цей спосіб не дає змогу відновити початкові нулі, вилучені до форматування. Він впливає тільки на числа, введені після застосування формату.
Використання функції TEXT для форматування
Щоб подати дані в потрібному форматі, можна скористатися розташованим поруч із ними порожнім стовпцем і функцією TEXT.
|
Цифровий код |
Приклад (у клітинці A1) |
Функція TEXT і новий формат |
|---|---|---|
|
Соціальний безпека |
012345678 |
=TEXT(A1;"000-00-0000") 012-34-5678 |
|
Телефон |
0012345556789 |
=TEXT(A1;"00-0-000-000-0000") 00-1-234-555-6789 |
|
Поштові код |
00123 |
=TEXT(A1;"00000") 00123 |
Округлення номерів кредитних карт
Excel має максимальну точність 15 значущих цифр, тобто для будь-якого числа, яке містить 16 або більше цифр, наприклад номера кредитної картки, будь-які числа, розташовані після 15-ї цифри, округлюються до нуля. У випадку з числовими кодами, які містять 16 або більше цифр, потрібно використовувати текстовий формат. Для цього можна виконати одну з двох дій:
-
Форматування стовпця як тексту
Виділіть діапазон даних і натисніть клавіші Ctrl+1 , щоб відкрити діалогове вікно Формат клітинок . На вкладці Число натисніть кнопку Текст.
Примітка.: Цей спосіб не дає змогу змінити числа, введені раніше. Він впливає тільки на числа, введені після застосування формату.
-
Використання символу апострофа
Перед числом можна ввести апостроф (’), і Excel оброблятиме його як текст.
Потрібна додаткова довідка?
Ви завжди можете поставити запитання експерту в спільноті Tech у розділі Excel чи отримати підтримку в спільнотах.
