Вам колись доводилось імпортувати або вводити в Excel дані, що містять початкові нулі (наприклад, 00123) чи великі числа (наприклад, 1234 5678 9087 6543)? Це можуть бути номери соціального страхування, номери телефонів, кредитних карт або рахунків, коди продуктів чи поштові індекси. Excel автоматично вилучає початкові нулі та подає великі числа в експоненційному форматі (наприклад, 1,23E+15), щоб ви могли використовувати їх у формулах і математичних операціях. З цієї статті ви дізнаєтеся, як зберегти дані у вихідному форматі, який Excel інтерпретує як текст.
Перетворення чисел на текст під час імпорту текстових даних
Щоб відформатувати окремі стовпці як текст під час імпорту даних до 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 чи отримати підтримку в спільнотах.
