Перейти до основного
Підтримка
Вхід
Вхід за допомогою облікового запису Microsoft
Увійдіть або створіть обліковий запис.
Вітаємо,
Виберіть інший обліковий запис.
У вас є кілька облікових записів
Виберіть обліковий запис, за допомогою якого потрібно ввійти.

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

Перетворення чисел на текст під час імпорту текстових даних

Щоб відформатувати окремі стовпці як текст під час імпорту даних до Excel, скористайтеся набором функцій Завантажити та перетворити (Power Query). Так ви імпортуєте текстовий файл, проте дані пройдуть через етапи перетворення, передбачені для імпорту даних з інших джерел, як-от XML, Інтернет, JSON тощо.

  1. Перейдіть на вкладку Дані й натисніть кнопку З текстового файлу або CSV-файлу біля кнопки Отримати дані. Якщо кнопка Отримати дані не відображається, виберіть Новий запит > З файлу > З тексту, а потім знайдіть потрібний текстовий файл і натисніть кнопку Імпорт.

  2. Excel завантажить дані до області перегляду. Натисніть кнопку Редагувати в області перегляду, щоб відобразити область Редактор запитів.

  3. Якщо певні стовпці потрібно перетворити на текст, виберіть їх, клацнувши заголовок, а потім на вкладці Головна в групі Перетворення виберіть Текст зі списку Тип даних.

    Power Query – дані після перетворення на текст

    Порада.: Щоб вибрати кілька стовпців, клацніть їх лівою кнопкою миші, утримуючи натиснутою клавішу Ctrl.

  4. Потім у діалоговому вікні Змінити тип стовпця натисніть кнопку Замінити поточне значення, і Excel перетворить вибрані стовпці на текст.

    "Завантажити та перетворити" > перетворення даних на текст
  5. Коли все буде готово, натисніть кнопку Закрити й завантажити, і Excel відобразить дані запиту на аркуші.

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

В Excel 2010 та Excel 2013 імпортувати текстові файли й перетворювати числа на текст можна двома способами. Радимо використовувати надбудову Power Query, яку можна завантажити тут. Якщо надбудову Power Query завантажити не вдається, можна скористатися майстром імпорту тексту. Так ви імпортуєте текстовий файл, проте дані пройдуть через етапи перетворення, передбачені для імпорту даних з інших джерел, як-от XML, Інтернет, JSON тощо.

  1. Перейдіть на вкладку Power Query й виберіть Отримати зовнішні дані > З тексту.

  2. Excel завантажить дані до області перегляду. Натисніть кнопку Редагувати в області перегляду, щоб відобразити область Редактор запитів.

  3. Якщо певні стовпці потрібно перетворити на текст, виберіть їх, клацнувши заголовок, а потім на вкладці Головна в групі Перетворення виберіть Текст зі списку Тип даних.

    Power Query – дані після перетворення на текст

    Порада.: Щоб вибрати кілька стовпців, клацніть їх лівою кнопкою миші, утримуючи натиснутою клавішу Ctrl.

  4. Потім у діалоговому вікні Змінити тип стовпця натисніть кнопку Замінити поточне значення, і Excel перетворить вибрані стовпці на текст.

    "Завантажити та перетворити" > перетворення даних на текст
  5. Коли все буде готово, натисніть кнопку Закрити й завантажити, і Excel відобразить дані запиту на аркуші.

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

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

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

Цифровий код

Приклад

Спеціальний числовий формат

Соціальних
Безпеки

012345678

000-00-0000
012-34-5678 

Телефон

0012345556789

00-0-000-000-0000
00-1-234-555-6789 

Поштові
Код

00123

00000 грн.
00123 

Інструкції    

  1. Виберіть клітинку або діапазон клітинок, які потрібно відформатувати.

  2. Натисніть клавіші Ctrl+1, щоб відкрити діалогове вікно Формат клітинок.

  3. Перейдіть на вкладку Число та зі списку Числові формати виберіть (усі формати), а потім у поле Тип введіть числовий формат, наприклад 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 чи отримати підтримку в спільнотах.

Потрібна додаткова довідка?

Потрібні додаткові параметри?

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

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

Чи ця інформація була корисною?

Наскільки ви задоволені якістю мови?
Що вплинуло на ваші враження?
Натиснувши кнопку "Надіслати", ви надасте свій відгук для покращення продуктів і служб Microsoft. Ваш ІТ-адміністратор зможе збирати ці дані. Декларація про конфіденційність.

Дякуємо за відгук!

×