Функція TEXT

Функція TEXT дає змогу змінити спосіб відображення числа, застосувавши до нього форматування з кодами форматів. Це корисно, коли потрібно відобразити числа в зручному для читання форматі або об’єднати їх із текстом чи символами.

Технічні відомості

Синтаксис

TEXT(значення;формат)

Синтаксис функції TEXT має такі аргументи:

Ім’я аргументу	Опис
значення	Числове значення, яке потрібно перетворити на текст.
формат	Текстовий рядок, що визначає формат, який потрібно застосувати до вказаного значення.

Огляд

У найпростішому випадку функція TEXT має такий вигляд:

• =TEXT(значення, яке потрібно відформатувати;"код формату, який потрібно застосувати")

Нижче наведено популярні приклади, які можна скопіювати прямо до Excel, щоб поекспериментувати самостійно. Зверніть увагу, що коди форматів беруться в лапки.

Формула	Опис
=TEXT(1234,567;"###0,00 ₴")	Грошовий формат із роздільником груп розрядів і двома розрядами дробової частини, наприклад 1234,57 ₴. Зверніть увагу, що Excel округлює значення до двох розрядів дробової частини.
=TEXT(TODAY();"DD.MM.YY")	Сьогоднішня дата у форматі ДД.ММ.РР, наприклад 14.03.12.
=TEXT(TODAY();"DDDD")	Сьогоднішній день тижня, наприклад понеділок.
=TEXT(NOW();"HH:MM")	Поточний час, наприклад 13:29.
=TEXT(0,285;"0,0%")	Відсотковий формат, наприклад 28,5%.
=TEXT(4.34;"# ?/?")	Дробовий формат, наприклад 13/3.
=TRIM(TEXT(0,34;"# ?/?"))	Дробовий формат, наприклад 1/3. Зверніть увагу, що функція TRIM використовується, щоб видалити пробіл перед дробовою частиною.
=TEXT(12200000;"0,00E+00")	Експоненційний формат, наприклад 1,22E+07.
=TEXT(1234567898;"[<=9999999]####-#####;( ####) ####-###")	Додатковий формат (номер телефону), наприклад (123) 456-7898.
=TEXT(1234;"0000000")	Додавання нулів на початку, наприклад 0001234.
=TEXT(123456;"##0° 00' 00''")	Спеціальний формат (широта або довгота).

Завантажте наші приклади

За бажання можна завантажити книгу з усіма прикладами застосування функції TEXT, які наведено в цій статті, і кількома іншими. Ви можете скористатися цими прикладами або створити власні коди форматів для функції TEXT.

Завантаження прикладів застосування функції TEXT у програмі Excel

Інші доступні коди форматів

Усі наявні коди форматів можна знайти в діалоговому вікні " Формат клітинок ".

1. Натисніть клавіші Ctrl+1 (+1 на комп'ютері Mac), щоб відкрити діалогове вікно «Формат клітинок».
2. На вкладці " Число " виберіть потрібний формат.
3. Виберіть параметр "Інший ".
4. Потрібний код формату відобразиться в полі Тип. Виділіть увесь вміст поля Тип, крім крапки з комою (;) і символу @. У прикладі нижче виділено та скопійовано лише код dd.mm.yyyy.
5. Натисніть сполучення клавіш Ctrl+C , щоб скопіювати код формату, а потім натисніть кнопку "Скасувати ", щоб закрити діалогове вікно " Формат клітинок ".
6. Тепер залишилося натиснути клавіші Ctrl+V, щоб вставити код формату у функцію TEXT, наприклад =TEXT(B2;"dd.mm.yyyy"). Обов'язково візьміть скопійований код формату в лапки ("код формату"), інакше в Excel з'явиться повідомлення про помилку.

Коди форматів за категоріями

Нижче наведено кілька прикладів того, як можна застосувати різні числові формати до значень у діалоговому вікні "Формат клітинок" і скопіювати ці коди форматів у функцію TEXT, вибравши "Настроюваний".

Вибір числового формату

Нулі на початку

Чому програма Excel видаляє нулі на початку?

Excel сприймає послідовність цифр, введену в клітинку, як число, а не як цифровий код, наприклад артикул або номер SKU. Щоб зберегти нулі на початку послідовностей цифр, застосуйте до відповідного діапазону клітинок текстовий формат, перш ніж вставляти або вводити значення. Виділіть стовпець або діапазон, де ви будете розміщувати значення, а потім натисніть клавіші CTRL+1 , щоб відкрити діалогове вікно «Формат > клітинок », і на вкладці «Число» виберіть «Текст». Тепер Excel зберігатиме початкові нулі.

Якщо ви вже ввели дані, і програма Excel видалила початкові нулі, їх можна повернути за допомогою функції TEXT . Для верхньої клітинки діапазону зі значеннями можна використовувати формулу =TEXT(значення;"00000"), де кількість нулів у формулі відповідає потрібному числу символів. Потім скопіюйте формулу та застосуйте її до іншої частини діапазону.

Якщо знадобиться перетворити текстові значення знову на числа, можна помножити їх на 1 (наприклад, =D4*1) або скористатися подвійним унарним оператором (--), наприклад =--D4.

Відображення роздільників розрядів

В Excel групи розрядів розділяються пробілом, якщо код формату містить пробіл, оточений знаками номера (#) або нулями. Наприклад, якщо використовується код формату "# ####", число 12200000 відображається в Excel як 12 200 000.

Пробіл після заповнювача цифри задає ділення числа на 1000. Наприклад, якщо використовується код формату "# ###,0 ", число 12200000 відображається в Excel як 12 200,0.

Числові, грошові та фінансові формати

Нижче наведено приклади форматів стандартного числа (роздільник розрядів і десяткових знаків), грошового й фінансового форматів. У вибраному форматі можна вставити вибраний символ грошової одиниці та вирівняти його відносно значення, а у фінансовому форматі символ грошової одиниці вирівнюється ліворуч від клітинки, а значення – справа. Зверніть увагу на різницю між кодом грошової одиниці та фінансового формату, де в бухгалтерському обліку використовується зірочка (*) для розділення символу та значення.

Щоб знайти код формату символу грошової одиниці, спочатку натисніть клавіші Ctrl+1 (або +1 на комп'ютері Mac), виберіть потрібний формат, а потім виберіть символ із розкривного списку символів:

" Потім виберіть значення «Спеціальний » ліворуч у розділі «Категорія » та скопіюйте код формату разом із символом грошової одиниці.

Дати

Спосіб відображення дат можна змінювати, використовуючи поєднання символів "D" (для дня), "M" (для місяця) і "Y" (для року).

У функції TEXT регістр кодів форматів не враховується, тому можна використовувати символи "D" і "d", "M" і "d", "Y" і "y".

	Мінда рекомендує… Якщо ви надаєте спільний доступ до файлів і звітів Excel користувачам із різних країн, швидше за все, дані потрібно надавати різними мовами. Мінда Трісі (Mynda Trecy), MVP з Excel , має чудове рішення в цій статті Excel Dates Displayed in Different Languages . У ній також є зразок книги, який можна завантажити.

Значення часу

Спосіб відображення часу можна змінити за допомогою поєднання символів "H" (для годин), "M" (для хвилин) і "S" (для секунд). Крім того, для подання часу в 12-годинному форматі можна використовувати символи "AM/PM".

Якщо не вказувати символи "AM/PM", час відображатиметься у 24-годинному форматі.

Оскільки «m» може означати місяці або хвилини, для відображення хвилин необхідно зробити це у форматі «h:m» або «hh:mm», щоб хвилини відображалися правильно. Якщо пропустити букву "h", програма Excel відобразить значення місяця. Щоб відобразити лише хвилини, скористайтеся функцією RIGHT, як показано в прикладі.

У функції TEXT регістр кодів форматів не враховується, тому допускається використання символів "H" і "h", "M" і "m", "S" і "s", "AM/PM" і "am/pm".

Якщо навколо символу "h" ("[h]), програма Excel не має скидати відлік годин кожні 24 години. Іншими словами, квадратні дужки дають змогу відобразити загальний проміжок часу. Квадратні дужки можна використовувати для відображення проміжку часу в годинах, хвилинах і секундах.

Відсотки

Для відображення десяткових значень можна використовувати різні відсоткові (%) формати.

Дроби

Десяткові числа можна відображати у вигляді дробів, використовуючи коди форматів "?/?".

Експоненційний формат

Експоненційний формат – це спосіб відображення значення у вигляді десяткового числа від 1 до 10, помноженого на 10 у певній степені. Цей формат часто використовується, щоб скоротити великі числа.

Додаткові формати

В Excel доступні чотири додаткові формати:

• "Поштовий індекс" ("00000");
• "Індекс + 4" ("00000-0000");
• Номер телефону – "[<=9999999]####-####;( ###) ###-####"
• "Табельний номер" ("000-00-0000").

Спеціальні формати відрізнятимуться залежно від локалізації, але якщо спеціальних форматів для вашої мови немає або вони вам не підходять, ви можете створити власний формат за допомогою діалогового вікна "Формат клітинок>".

Типовий сценарій

Функція TEXT рідко використовується окремо, а частіше застосовується в поєднанні з чимось іще. Припустімо, ви хочете об'єднати текст і числове значення, наприклад, щоб отримати рядок "Звіт надруковано 14.03.12" або "Щотижневий дохід: 66 348,72 $". Такі рядки можна ввести вручну, але Excel може зробити це за вас. На жаль, у разі об'єднання тексту та форматованих чисел, наприклад дат, значень часу, грошових сум тощо, Excel прибирає форматування, оскільки невідомо, у якому вигляді їх потрібно відобразити. Тут дуже здобула функція TEXT , яка дає змогу змусити програму Excel форматувати значення належним чином за допомогою коду формату, наприклад "ДД.ММ.РР" для формату дати.

У наведеному нижче прикладі показано, що відбувається, якщо спробувати об'єднати текст і число без використання функції TEXT . У цьому випадку ми використовуємо амперсанд (&), щоб об'єднати текстовий рядок, пробіл (" ") і значення: =A2&" "&B2.

Як бачите, значення дати, взяте з клітинки B2, не відформатовано. У наступному прикладі показано, як застосувати потрібне форматування за допомогою функції TEXT.

Ось оновлена формула:

• клітинка C2:=A2&" "&TEXT(B2;"dd.mm.yy") – формат дати

Запитання й відповіді

Як перетворити числа на текст, наприклад 123 на "сто двадцять три"?

На жаль, ви не можете зробити цього за допомогою функції TEXT; потрібно використовувати код Visual Basic for Applications (VBA). За наступним посиланням є метод: Як перетворити числове значення на слова англійською мовою в Excel.

Чи можна змінити регістр тексту?

Так, для цього можна скористатися функціями UPPER, LOWER та PROPER. Наприклад, формула =UPPER("привіт") повертає результат "ПРИВІТ".

Чи можна за допомогою функції TEXT додати новий рядок (розрив рядка) до клітинки так само, як за допомогою сполучення клавіш Alt+Enter?

Так, але це займе кілька кроків. Спочатку виділіть одну або кілька клітинок, де це має відбутися, і за допомогою сполучення клавіш Ctrl+1 відкрийте діалогове вікно «Формат > клітинок», потім елемент керування «Вирівнювання > тексту» перевірте параметр «Перенесення тексту».> Далі настройте готову функцію TEXT , включивши функцію ASCII CHAR(10) замість місця розриву рядка. Можливо, знадобиться змінити ширину стовпця залежно від того, як вирівнюється кінцевий результат.

У цьому прикладі використано формулу: ="Сьогодні: "&CHAR(10)&TEXT(TODAY();"dd.mm.yy")

Чому Excel перетворює введені числа на щось на кшталт "1,22E+07"?

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

Відображення дат різними мовами

	Мінда рекомендує… Якщо ви надаєте спільний доступ до файлів і звітів Excel користувачам із різних країн, швидше за все, дані потрібно надавати різними мовами. Мінда Трісі (Mynda Trecy), MVP з Excel , має чудове рішення в цій статті Excel Dates Displayed in Different Languages . У ній також є зразок книги, який можна завантажити.