Функція TEXT дає змогу змінити спосіб відображення числа, застосувавши до нього форматування з кодами форматів. Це корисно, коли потрібно відобразити числа в зручному для читання форматі або об’єднати їх із текстом чи символами.
Примітка.: Функція TEXT перетворює числа на текст, що може ускладнити посилання в подальших обчисленнях. Найкраще зберігати вихідне значення в одній клітинці, а потім використовувати функцію 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, але не тільки. Ви можете змінити формат без формули, натиснувши клавіші Ctrl+1 (або 
+1 на комп'ютері Mac), а потім виберіть потрібний формат у діалоговому вікні Формат клітинок > число .
Завантажте наші приклади
За бажання можна завантажити книгу з усіма прикладами застосування функції TEXT, які наведено в цій статті, і кількома іншими. Ви можете скористатися цими прикладами або створити власні коди форматів для функції TEXT.
Завантажити приклади застосування функції TEXT у програмі Excel
Інші доступні коди форматів
Щоб знайти інші доступні коди форматів, скористайтеся діалоговим вікном Формат клітинок .
-
Натисніть клавіші Ctrl+1 (
+1 на комп'ютері Mac), щоб відкрити діалогове вікно Формат клітинок . -
На вкладці Число виберіть потрібний формат.
-
Виберіть параметр Настроюваний .
-
Потрібний код формату відобразиться в полі Тип. Виділіть увесь вміст поля Тип, крім крапки з комою (;) і символу @. У прикладі нижче виділено та скопійовано лише код dd.mm.yyyy.
-
Натисніть клавіші Ctrl+C , щоб скопіювати код формату, а потім натисніть кнопку Скасувати , щоб закрити діалогове вікно Формат клітинок .
-
Тепер залишилося натиснути клавіші Ctrl+V, щоб вставити код формату у функцію TEXT, наприклад =TEXT(B2;"dd.mm.yyyy"). Переконайтеся, що ви вставляєте код формату в лапки ("код формату"), інакше Excel надасть вам повідомлення про помилку.
Коди форматів за категоріями
Нижче наведено кілька прикладів застосування різних числових форматів до значень за допомогою діалогового вікна Формат клітинок , а потім скористайтеся параметром Настроювані , щоб скопіювати ці коди форматів до функції TEXT .
Чому програма Excel видаляє нулі на початку?
Excel сприймає послідовність цифр, введену в клітинку, як число, а не як цифровий код, наприклад артикул або номер SKU. Щоб зберегти нулі на початку послідовностей цифр, застосуйте до відповідного діапазону клітинок текстовий формат, перш ніж вставляти або вводити значення. Виділіть стовпець або діапазон, у який потрібно помістити значення, натисніть клавіші Ctrl+1, щоб відкрити діалогове вікно Формат клітинок, і виберіть на вкладці Число пункт Текстовий. Тепер програма Excel не видалятиме нулі на початку.
Якщо ви вже ввели дані, а програма Excel видалила 0-х на початку, можна скористатися функцією TEXT , щоб додати їх назад. Ви можете посилатися на верхню клітинку зі значеннями та використовувати формулу =TEXT(значення;"00000"), де число 0 у формулі відповідає загальній кількості символів, а потім скопіюйте та вставте його до решти діапазону.
Якщо знадобиться перетворити текстові значення знову на числа, можна помножити їх на 1 (наприклад, =D4*1) або скористатися подвійним унарним оператором (--), наприклад =--D4.
В Excel групи розрядів розділяються пробілом, якщо код формату містить пробіл, оточений знаками номера (#) або нулями. Наприклад, якщо використовується код формату "# ###", число 12200000 відображається як 12 200 000.
Пробіл після заповнювача цифри задає ділення числа на 1000. Наприклад, якщо використовується код формату "# ###,0 ", число 12200000 відображається в Excel як 12 200,0.
Примітки.:
-
Роздільник груп розрядів залежить від регіональних параметрів. Для України це пробіл, а в інших країнах і регіонах може використовуватися кома або крапка.
-
Роздільник груп розрядів можна застосовувати в числових, грошових і фінансових форматах.
Нижче наведено приклади стандартних чисел (лише роздільники розрядів і десяткових розрядів), грошових і бухгалтерських форматів. Грошовий формат дає змогу вставити вибраний символ грошової одиниці та вирівняти його за значенням, а фінансовий формат вирівнює символ грошової одиниці за лівим краєм клітинки та значенням за правим краєм. Зверніть увагу на різницю між кодами форматів грошової одиниці та бухгалтерського обліку нижче, коли фінансовий формат використовує зірочку (*), щоб створити поділ між символом і значенням.
Щоб отримати код формату для певної грошової одиниці, спочатку натисніть клавіші Ctrl+1 (на комп’ютері Mac – +1) і виберіть потрібний формат, а потім у розкривному списку Позначення виберіть символ.
Після цього в розділі Числові формати ліворуч виберіть пункт (усі формати) і скопіюйте код формату разом із символом грошової одиниці.
Примітка.: Функція TEXT не підтримує форматування за допомогою кольору. Якщо скопіювати в діалоговому вікні "Формат клітинок" код формату, у якому використовується колір, наприклад # ##0,00_₴;[Червоний]-# ##0,00_₴, то функція TEXT прийме його, але колір не відображатиметься.
Спосіб відображення дат можна змінювати, використовуючи поєднання символів "D" (для дня), "M" (для місяця) і "Y" (для року).
У функції TEXT регістр кодів форматів не враховується, тому допускається використання символів "D" і "d", "M" і "m", "Y" і "y".
|
|
Мінда рекомендує… Якщо ви надаєте спільний доступ до файлів і звітів Excel користувачам із різних країн, швидше за все, дані потрібно надавати різними мовами. Excel MVP, Mynda Treacy має чудове рішення в цій статті Дати Excel, що відображаються різними мовами . У ній також є зразок книги, який можна завантажити. |
Спосіб відображення часу можна змінити за допомогою поєднання символів "H" (для годин), "M" (для хвилин) і "S" (для секунд). Крім того, для подання часу в 12-годинному форматі можна використовувати символи "AM/PM".
Якщо не вказувати символи "AM/PM", час відображатиметься у 24-годинному форматі.
У функції TEXT регістр кодів форматів не враховується, тому допускається використання символів "H" і "h", "M" і "m", "S" і "s", "AM/PM" і "am/pm".
Для відображення десяткових значень можна використовувати різні відсоткові (%) формати.
Спосіб відображення десяткових значень із дробом (?/?) можна змінити. Формати.
Експоненційний формат – це спосіб відображення значення у вигляді десяткового числа від 1 до 10, помноженого на 10 у певній степені. Цей формат часто використовується, щоб скоротити великі числа.
В Excel доступні чотири додаткові формати:
-
"Поштовий індекс" ("00000");
-
"Індекс + 4" ("00000-0000");
-
"Номер телефону" ("[<=9999999]###-####;(###) ###-####");
-
"Табельний номер" ("000-00-0000").
Додаткові формати залежать від регіональних параметрів. Якщо додаткові формати недоступні для вашого регіону або не підходять для ваших потреб, ви можете створити власний формат, вибравши в діалоговому вікні Формат клітинок пункт (усі формати).
Типовий сценарій
Функція TEXT рідко використовується окремо, а частіше застосовується в поєднанні з чимось іще. Припустімо, ви хочете об’єднати текст і числове значення, наприклад, щоб отримати рядок "Звіт надруковано 14.03.12" або "Щотижневий дохід: 66 348,72 ₴". Такі рядки можна ввести вручну, але Excel може зробити це за вас. На жаль, у разі об’єднання тексту та форматованих чисел, наприклад дат, значень часу, грошових сум тощо, Excel прибирає форматування, оскільки невідомо, у якому вигляді їх потрібно відобразити. Тут стане в пригоді функція TEXT, адже з її допомогою можна примусово відформатувати числа, задавши потрібний код формату, наприклад "DD.MM.YY" для дат.
У прикладі нижче показано, що відбувається, якщо спробувати об’єднати текст і число, не застосовуючи функцію TEXT. У цьому випадку ми використовуємо амперсанд (&), щоб об’єднати текстовий рядок, пробіл (" ") і значення: =A2&" "&B2.
Як бачите, значення дати, взяте з клітинки B2, не відформатовано. У наступному прикладі показано, як застосувати потрібне форматування за допомогою функції TEXT.
Ось оновлена формула:
-
клітинка C2:=A2&" "&TEXT(B2;"dd.mm.yy") – формат дати
Запитання й відповіді
На жаль, це не можна зробити за допомогою функції TEXT; потрібно використовувати код Visual Basic for Applications (VBA). Це посилання має метод: Перетворення числового значення на англійські слова в Excel.
Так, але це потрібно зробити кілька кроків. Спочатку виділіть клітинку або клітинки, у яких це має статися, і натисніть клавіші Ctrl+1 , щоб відкрити діалогове вікно Формат клітинок > клітинок , а потім виберіть Вирівнювання > Елемент керування текстом > встановіть прапорець Обтікання текстом . Потім відрегулюйте завершену функцію TEXT , щоб включити функцію ASCII CHAR(10), де потрібно розрив рядка. Можливо, знадобиться змінити ширину стовпця залежно від того, як вирівнюється остаточний результат.
У цьому прикладі використано формулу: ="Сьогодні: "&CHAR(10)&TEXT(TODAY();"dd.mm.yy")
Ця функція називається Експоненційною нотацією, і excel автоматично перетворює числа, довші за 12 цифр, якщо клітинки відформатовано як загальні, і 15 цифр, якщо клітинки відформатовано як число. Якщо потрібно вводити довгі числові рядки, але не потрібно перетворювати їх, відформатуйте потрібні клітинки як текст , перш ніж вводити або вставляти значення в програму Excel.
|
|
Мінда рекомендує… Якщо ви надаєте спільний доступ до файлів і звітів Excel користувачам із різних країн, швидше за все, дані потрібно надавати різними мовами. Excel MVP, Mynda Treacy має чудове рішення в цій статті Дати Excel, що відображаються різними мовами . У ній також є зразок книги, який можна завантажити. |
Додаткові відомості
Створення та видалення настроюваного числового формату
