Існує п’ять різних способів перетворити числа, відформатовані як текст, у фактичні числа в Excel.
Багато разів, наприклад, коли ми копіюємо дані з іншої програми, текстового файлу чи онлайн, Excel може зберігати числа як текст. Якщо ви спробуєте використати ці текстові значення в обчисленнях і формулах, ви отримаєте помилки. У таких ситуаціях вам потрібно перетворити ці числа, відформатовані як текст, назад у числові значення.
У цій статті ви дізнаєтеся про різні методи, які можна використовувати для перетворення тексту в числа в Excel.
Як перевірити, чи є значення числом або текстом
У Excel значення можуть виглядати як числа, але вони не складаються, і вони не працюють як числа, як показано нижче.
Існує кілька способів визначити, чи значення в Excel відформатовано як текст або число.
- У електронній таблиці Excel за замовчуванням числа вирівнюються ліворуч, а тексти — праворуч у клітинці.
- Якщо вибрано кілька комірок із числовими значеннями, рядок стану Excel внизу відображатиме значення Середнє, Кількість та СУММА, але якщо вибрано кілька комірок із текстовими значеннями, у рядку стану відображатиметься лише кількість.
- Іноді, якщо клітинка містить число, відформатований як текст, ви побачите маленький зелений трикутник у верхньому лівому куті клітинки (індикатор помилки), як показано вище.
- Коли вибрано клітинку з індикатором помилки, ви побачите знак застереження з жовтим знаком оклику. Наведіть курсор на цей знак, і Excel покаже вам можливу проблему з цією клітинкою: «Число в цій клітинці відформатовано як текст або передує апостроф».
- Крім того, коли ви намагаєтеся підсумувати числа, відформатовані як текст, вони не показують правильний підсумок, як показано вище.
Методи перетворення тексту в числа
У цій статті показано п’ять різних способів, які можна використовувати для перетворення тексту в числа, а саме:
- Використання функції тексту до числа
- Змінивши формат осередку
- Використання методу Paste Special
- Використання майстра тексту в стовпці
- Використання формул
Перетворення тексту в числа за допомогою функції тексту в число
Почнемо з простого і легкого методу, але цей метод не завжди доступний вам.
Функція «Текст до числа» доступна лише тоді, коли числа є відформатованим текстом у результаті імпортування даних із зовнішніх джерел або коли перед числом додається апостроф. У таких випадках ви побачите маленький зелений трикутник у верхньому лівому куті комірки, що є індикатором помилки.
Виберіть клітинку(и), які потрібно перетворити з тексту в числа, і клацніть жовту піктограму обережності, яка з’являється поруч із вибраною клітинкою або діапазоном. У спадному меню виберіть опцію «Перетворити в число».
Готово! Ваші числа перетворені.
Змініть текст на число, змінивши формат
Ще один швидкий і простий спосіб перетворити числа, відформатовані як текст, у числа – це змінити формат комірки.
Ось як ви це робите:
Виберіть усі клітинки, які потрібно перетворити. Ви можете вибрати весь стовпець, вибравши літеру стовпця або натиснувши Ctrl + пробіл. Перейдіть на вкладку «Головна», клацніть спадний список «Формат числа» у групі «Число» та виберіть параметр «Загальні» або «Число».
Вибір параметра «Число» дасть десяткові числа, тому параметр «Загальний» підходить. Або ви можете вибрати будь-які інші формати у спадному меню. Вибраний формат буде застосовано до ваших даних.
Після того, як ви виберете «Загальні», вибрані клітинки будуть відформатовані як числа, і вони будуть вирівняні праворуч від комірок.
Цей метод не завжди працює, в таких випадках скористайтеся наступними способами.
Змініть текст на номер за допомогою спеціального методу вставки
Третій метод є ще одним ефективним методом для перетворення тексту в числа. У цьому методі ви просто виконуєте арифметичну операцію над текстом за допомогою спеціального інструменту «Вставити». Ось як ви це робите:
Виберіть будь-яку порожню клітинку (яку Excel інтерпретує як 0) і скопіюйте її. Щоб скопіювати клітинку, виділіть клітинку та натисніть «Ctrl + Cабо клацніть правою кнопкою миші та виберіть «Копіювати» з контекстного меню. Потім виберіть клітинки, які потрібно перетворити на числа, клацніть правою кнопкою миші та виберіть опцію «Спеціальна вставка». Або натисніть кнопку «Ctrl + Alt + V’ ярлик, щоб зробити те ж саме.
У діалоговому вікні «Спеціальна вставка» виберіть «Значення» в розділі «Вставити» (зверху) і «Додати» в розділі «Операція» (унизу) і натисніть кнопку «ОК».
Це додасть «0» до кожної клітинки у вибраному діапазоні та перетворить числа, збережені як текст, у числа.
Результат:
Перетворення тексту в число за допомогою майстра тексту в стовпці
Ще одним методом перетворення тексту в числа є використання майстра тексту в стовпець. Це трохи тривалий процес, але він корисний для перетворення цілого стовпця значень. Ось як ви це робите:
Спочатку виділіть клітинки, які потрібно перетворити з тексту в числа, перейдіть на вкладку «Дані», натисніть кнопку «Текст у стовпці» в групі «Інструменти даних». Відкриється «Майстер перетворення тексту в стовпець».
На кроці 1 майстра виберіть «З роздільниками» у розділі «Вихідний тип даних» і натисніть «Далі».
На кроці 2 майстра переконайтеся, що прапорець «Вкладка» встановлено, і натисніть кнопку «Далі».
На останньому кроці майстра переконайтеся, що під форматом даних стовпця вибрано «Загальні». Для поля «Призначення» ви можете або вказати новий стовпець, у якому потрібно отримати результат, або залишити його таким, яким він є, і він замінить вихідний набір даних. Потім натисніть кнопку «Готово».
Ви, мабуть, помітили, що ви не змінювали параметр протягом усього процесу, і ми могли б перейти до «Готово» на кроці 1. Якщо ваші дані містять будь-які роздільники, такі як пробіли або крапки з комою, ви повинні вибрати їх на кроці 2 майстра.
Перетворення тексту в число за допомогою формул
Останнім методом перетворення тексту в числа є використання формул. Це простий і зрозумілий спосіб, який не вимагає багато ручного втручання. За допомогою формули ви можете автоматизувати перетворення.
Перетворіть рядок у число за допомогою функції VALUE
Excel пропонує спеціальну функцію «ЗНАЧЕННЯ» для перетворення тексту в число.
Особливість цієї функції полягає в тому, що вона може розпізнавати числове значення, оточене спеціальними символами, вона приймає як текстові рядки, так і посилання на клітинку, яка містить тексти, і створює чисту версію значення в іншій клітинці.
Синтаксис функції VALUE:
=ЗНАЧЕННЯ(текст)Наприклад, щоб перетворити число, відформатований як текст у форматі A1, скористайтеся наведеною нижче функцією VALUE:
=ЗНАЧЕННЯ(A2)Спочатку виділіть клітинку, у якій потрібно отримати результат, і введіть формулу вище.
Якщо ви хочете перетворити стовпець текстових значень у числа, вставте формулу в першу клітинку діапазону, а потім скористайтеся маркером заповнення, щоб скопіювати формулу до решти клітинок.
Функція Excel VALUE з текстовими функціями
Функцію VALUE можна використовувати в поєднанні з текстовими функціями Excel, наприклад, ЛІВО, ВПРАВО та СРЕДНІЙ, щоб витягнути число з текстового рядка.
=ЗНАЧЕННЯ(ВПРАВО(A1,5))У наведеній вище формулі функція VALUE витягує числове значення з текстового рядка в комірці A1 за допомогою функції RIGHT.
Змініть текст на число за допомогою арифметичних операцій в Excel
Ви також можете перетворити текст у число, виконавши просту математичну операцію, яка не змінює початкове значення.
Наприклад, щоб перетворити число, відформатований як текст, у клітинці A1, ви можете додати нуль, помножити або поділити 1 з цим текстовим значенням. Ці операції не змінюють значення, а перетворюють їх у числа.
=A1+0 (або) =A1*1 (або) =A1/1
У цій статті ми обговорили всі можливі методи, які можна використовувати для перетворення тексту в число в Excel.