Одне з частих завдань в Excel — перетворити числа на текст і навпаки. Якщо не знати чіткого порядку дій, на це можна витратити багато часу. Розберімося, як розв'язати обидві задачі різними способами.
Як перетворити число на текст
Кожна клітинка в Excel має певний формат, який визначає, як програма сприймає її вміст. Наприклад, якщо в клітинці записано цифри, але встановлено текстовий формат, Excel вважатиме їх звичайним текстом і не зможе виконувати з ними обчислення. Щоб Excel сприймав цифри саме як число, вони мають бути в клітинці із загальним або числовим форматом.
Спершу розглянемо, як перетворити числа на текст.
Спосіб 1: формат через контекстне меню
Найчастіше числа перетворюють на текст через контекстне меню.
- Виділіть клітинки, дані в яких треба перетворити на текст. На вкладці «Основне» (Home) у групі «Число» (Number) у полі формату видно, що ці клітинки мають загальний формат, тобто програма сприймає цифри в них як числа.
- Клацніть виділення правою кнопкою миші й виберіть «Формат клітинок…» (Format Cells).
- У вікні форматування перейдіть на вкладку «Число» (Number), якщо відкрито іншу. У списку «Числові формати» виберіть «Текстовий» (Text) і натисніть «ОК».
- Тепер у полі формату видно, що клітинки мають текстовий формат.
- Але якщо спробувати порахувати автосуму, вона все одно з'явиться в клітинці нижче. Це означає, що перетворення завершилося не повністю — така особливість Excel.
- Щоб завершити перетворення, по черзі двічі клацніть кожну клітинку діапазону й натисніть Enter. Замість подвійного клацання можна натискати F2.
- Після цього Excel сприйматиме дані в клітинках як текст, і автосума дорівнюватиме нулю. Крім того, лівий верхній кут клітинок може позначитися зеленим трикутником — ще одна ознака того, що числа збережено як текст. Утім, ця позначка з'являється не завжди.
Докладніше про формати — у статті як змінити формат клітинки в Excel.
Спосіб 2: інструменти на стрічці
Перетворити число на текст можна й інструментами на стрічці, зокрема через поле формату, про яке йшлося вище.
- Виділіть клітинки, дані в яких треба перетворити на текст. На вкладці «Основне» натисніть трикутник праворуч від поля формату в групі «Число».
- У списку виберіть «Текстовий» (Text).
- Далі, як і в попередньому способі, по черзі відкрийте кожну клітинку діапазону подвійним клацанням або клавішею F2 і натисніть Enter.
Дані перетворяться на текст.
Спосіб 3: функція TEXT
Ще один спосіб — функція TEXT (в українській версії Excel — ТЕКСТ). Він підходить насамперед тоді, коли числа як текст потрібно перенести в окремий стовпець, а також заощадить час на великих обсягах даних: клацати кожну клітинку в діапазоні з сотень чи тисяч рядків — не найкращий варіант.
- Поставте курсор у першу клітинку діапазону, куди виводитиметься результат, і натисніть значок «Вставити функцію» (Insert Function) біля рядка формул.
- Відкриється майстер функцій. У категорії «Текстові» (Text) виберіть TEXT і натисніть «ОК».
- Відкриється вікно аргументів функції. Її синтаксис:
=TEXT(значення; формат)У вікні два поля, що відповідають аргументам: «Значення» (Value) і «Формат» (Format_text).
У полі «Значення» вкажіть число або посилання на клітинку з ним — у нашому випадку посилання на першу клітинку числового діапазону.
У полі «Формат» задайте вигляд результату. Наприклад, формат «0» покаже текст без десяткових знаків, навіть якщо вони були у вихідному числі; «0,0» — з одним десятковим знаком, «0,00» — з двома тощо.
Указавши параметри, натисніть «ОК».
- Значення першої клітинки з'явиться в клітинці, яку ви виділили на першому кроці. Щоб перенести й інші значення, скопіюйте формулу в сусідні клітинки: наведіть курсор на правий нижній кут клітинки з формулою — він перетвориться на маркер заповнення у вигляді хрестика. Затисніть ліву кнопку миші й протягніть уздовж діапазону з вихідними даними.
- Тепер увесь стовпець заповнено, але клітинки містять формули. Виділіть цю область і натисніть «Копіювати» (Copy) на вкладці «Основне» в групі «Буфер обміну» (Clipboard).
- Якщо потрібно зберегти обидва діапазони (вихідний і перетворений), не знімайте виділення з області з формулами, клацніть її правою кнопкою миші й виберіть «Спеціальне вставлення» (Paste Special) → «Значення та формати чисел» (Values & Number Formatting).
Якщо ж потрібно замінити вихідні дані, виділіть вихідний діапазон і вставте так само.
- У будь-якому разі у вибраний діапазон буде вставлено дані в текстовому вигляді. Якщо ви вставили їх у вихідну область, клітинки з формулами можна очистити: виділіть їх, клацніть правою кнопкою миші й виберіть «Очистити вміст» (Clear Contents).
На цьому перетворення завершено.
Як перетворити текст на число
Тепер розберімо зворотне завдання — як перетворити текст на число в Excel.
Спосіб 1: значок помилки
Найпростіше й найшвидше перетворити текст на число за допомогою значка помилки — знака оклику в ромбі. Він з'являється, коли ви виділяєте клітинки із зеленою позначкою в лівому верхньому куті, про яку йшлося вище. Така позначка не обов'язково означає помилку: просто цифри в текстовому форматі викликають у програми підозру, що дані введено некоректно. На жаль, Excel показує ці позначки не завжди, тож цей спосіб підходить не для всіх випадків.
- Виділіть клітинку із зеленим індикатором можливої помилки й клацніть значок, що з'явився.
- У списку дій виберіть «Перетворити на число» (Convert to Number).
- Дані у виділеній клітинці одразу перетворяться на число.
Якщо таких текстових значень багато, процедуру можна пришвидшити.
- Виділіть увесь діапазон з текстовими даними. Значок з'явиться один для всієї області, а не для кожної клітинки окремо. Клацніть його.
- У знайомому списку знову виберіть «Перетворити на число».
Усі дані діапазону буде перетворено на числа.
Спосіб 2: вікно форматування
Як і для перетворення чисел на текст, у зворотному напрямку теж можна скористатися вікном форматування.
- Виділіть діапазон з числами в текстовому вигляді, клацніть його правою кнопкою миші й виберіть «Формат клітинок…».
- Перейдіть на вкладку «Число». У списку «Числові формати» виберіть «Загальний» (General) або «Числовий» (Number) — у будь-якому разі програма сприйматиме цифри в клітинках як числа. Для формату «Числовий» праворуч можна задати кількість десяткових знаків і роздільник груп розрядів. Натисніть «ОК».
- Тепер, як і під час перетворення числа на текст, по черзі відкрийте кожну клітинку й натисніть Enter.
Після цього всі значення діапазону перетворяться на числа.
Спосіб 3: інструменти на стрічці
Перетворити текст на числа можна й через поле формату на стрічці.
- Виділіть діапазон, перейдіть на вкладку «Основне», клацніть поле вибору формату в групі «Число» і виберіть «Числовий» або «Загальний».
- Далі вже знайомим способом по черзі відкрийте кожну клітинку діапазону клавішею F2 і натисніть Enter.
Значення в діапазоні стануть числами.
Спосіб 4: формула
Перетворити текстові значення на числа можна й формулою.
- У порожній клітинці поруч із першою клітинкою діапазону введіть знак «дорівнює» (=), два знаки «мінус» (--) і адресу першої клітинки діапазону, наприклад
=--A2. Так значення двічі множиться на −1, а «мінус» на «мінус» дає «плюс». Тобто в цільовій клітинці виходить те саме значення, але вже як число. Цей прийом називають подвійним унарним запереченням. - Натисніть Enter — отримаєте перетворене значення. Щоб застосувати формулу до інших клітинок діапазону, скористайтеся маркером заповнення, як для функції TEXT вище.
- Тепер у діапазоні формули. Виділіть його й натисніть «Копіювати» на вкладці «Основне» або Ctrl+C.
- Виділіть вихідну область, клацніть її правою кнопкою миші й виберіть «Спеціальне вставлення» → «Значення та формати чисел».
- Дані вставлено в потрібному вигляді. Допоміжний діапазон з формулою подвійного заперечення тепер можна очистити: виділіть його, клацніть правою кнопкою миші й виберіть «Очистити вміст».
До речі, замість подвійного множення на −1 можна використати будь-яку іншу арифметичну дію, яка не змінює значення: додати або відняти нуль, піднести до першого степеня тощо.
Спосіб 5: спеціальне вставлення
Цей спосіб схожий на попередній, але не потребує додаткового стовпця.
- У будь-яку порожню клітинку аркуша введіть 1. Виділіть її й натисніть «Копіювати» на стрічці.
- Виділіть діапазон, який треба перетворити, клацніть його правою кнопкою миші й виберіть «Спеціальне вставлення…» (відкрийте саме вікно параметрів спеціального вставлення).
- У блоці «Операція» (Operation) виберіть «Помножити» (Multiply) і натисніть «ОК».
- Усі значення виділеного діапазону стануть числами. Клітинку з одиницею тепер можна очистити.
Спосіб 6: інструмент «Текст за стовпцями»
Ще один спосіб — інструмент «Текст за стовпцями» (Text to Columns). Він знадобиться, коли десятковим роздільником у даних є крапка, а роздільником розрядів — апостроф. В англомовних налаштуваннях Excel такі значення сприймаються як числа, а у версіях, де десятковим роздільником є кома (зокрема українській), — як текст. Можна, звісно, перебити дані вручну, але якщо їх багато, це забере багато часу, а є значно швидший спосіб.
- Виділіть фрагмент аркуша, який треба перетворити, перейдіть на вкладку «Дані» (Data) і в групі «Знаряддя даних» (Data Tools) натисніть «Текст за стовпцями».
- Відкриється майстер. У першому вікні переконайтеся, що формат даних — «З роздільниками» (Delimited). За замовчуванням так і має бути, але перевірити не завадить. Натисніть «Далі» (Next).
- У другому вікні теж нічого не змінюйте й натисніть «Далі».
- У третьому вікні майстра натисніть «Додатково…» (Advanced).
- У вікні додаткових параметрів імпорту тексту в полі «Роздільник цілої та дробової частин» (Decimal separator) вкажіть крапку, а в полі «Роздільник груп розрядів» (Thousands separator) — апостроф. Натисніть «ОК».
- Повернувшись до третього вікна майстра, натисніть «Готово» (Finish).
- Числа набудуть звичного для ваших регіональних налаштувань вигляду — тобто водночас перетворяться з тексту на числа.
Спосіб 7: макрос
Якщо вам часто доводиться перетворювати великі обсяги даних з тексту на числа, варто записати для цього власний макрос. Для цього потрібно ввімкнути вкладку «Розробник» (Developer), якщо її ще немає на стрічці, і дозволити виконання макросів для власної книги (файл зберігайте у форматі з підтримкою макросів).
- Перейдіть на вкладку «Розробник» і натисніть «Visual Basic» у групі «Код» (Code).
- Відкриється редактор макросів. Вставте в нього такий код:
Sub TextToNumber() Selection.NumberFormat = "General" Selection.Value = Selection.Value End SubЗакрийте редактор кнопкою закриття у верхньому правому куті вікна.
- Виділіть фрагмент аркуша, який треба перетворити, і натисніть «Макроси» (Macros) на вкладці «Розробник» у групі «Код».
- У вікні зі списком макросів виберіть TextToNumber і натисніть «Виконати» (Run).
- Текстові значення одразу перетворяться на числа.
Докладніше про запис макросів — у статті як створити макрос в Excel.
Отже, перетворити в Excel числа на текст і навпаки можна багатьма способами. Вибір залежить від кількох чинників. Насамперед від завдання: наприклад, швидко перетворити текст з іноземними роздільниками на числа можна лише інструментом «Текст за стовпцями». Другий чинник — обсяг і частота перетворень: якщо ви робите це часто, варто записати макрос. І третій — особиста зручність.