TEXT (функція TEXT)

Співавтори: Кріс "Смітті" Сміт (Chris "Smitty" Smith) Дейв Людвіг (Dave Ludwig)

Функція 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 (на комп’ютері Mac – Зображення піктограми кнопки Command на комп’ютері MAC +1), а потім у діалоговому вікні Формат клітинок на вкладці Число виберіть потрібний формат.

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

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

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

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

Переглянути всі наявні коди форматів можна в діалоговому вікні Формат клітинок.

  1. Щоб відкрити діалогове вікно Формат клітинок, натисніть клавіші Ctrl+1 (на комп’ютері Mac – Зображення піктограми кнопки Command на комп’ютері MAC +1).

  2. На вкладці Число виберіть потрібний формат.

  3. Виберіть пункт (усі формати).

  4. Потрібний код формату відобразиться в полі Тип. Виділіть увесь вміст поля Тип, крім крапки з комою (;) і символу @. У прикладі нижче виділено та скопійовано лише код dd.mm.yyyy.

  5. Натисніть клавіші Ctrl+C, щоб скопіювати код формату, а потім – кнопку Скасувати, щоб закрити діалогове вікно Формат клітинок.

  6. Тепер залишилося натиснути клавіші Ctrl+V, щоб вставити код формату у функцію TEXT, наприклад =TEXT(B2;"dd.mm.yyyy"). Обов’язково візьміть скопійований код формату в лапки ("код формату"), інакше в Excel з’явиться повідомлення про помилку.

Приклад використання діалогового вікна "Формат > Формат клітинок > Число > (усі формати)" для створення рядків формату в Excel

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

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

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

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

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

Приклади використання функції TEXT для форматування нулів на початку. =TEXT(A2;"00000")

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

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

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

Приклади функції TEXT із роздільником розрядів

Примітки : 

  • Роздільник груп розрядів залежить від регіональних параметрів. Для України це пробіл, а в інших країнах і регіонах може використовуватися кома або крапка.

  • Роздільник груп розрядів можна застосовувати в числових, грошових і фінансових форматах.

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

Приклади функції TEXT із числовими, грошовими та фінансовими форматами

Щоб отримати код формату для певної грошової одиниці, спочатку натисніть клавіші Ctrl+1 (на комп’ютері Mac – Зображення піктограми кнопки Command на комп’ютері MAC +1) і виберіть потрібний формат, а потім у розкривному списку Позначення виберіть символ.

Символ грошової одиниці, вибраний у діалоговому вікні "Формат клітинок"

Після цього в розділі Числові формати ліворуч виберіть пункт (усі формати) і скопіюйте код формату разом із символом грошової одиниці.

Функція TEXT – спеціальна грошова одиниця із символом

Примітка : Функція TEXT не підтримує форматування за допомогою кольору. Якщо скопіювати в діалоговому вікні "Формат клітинок" код формату, у якому використовується колір, наприклад # ##0,00_₴;[Червоний]-# ##0,00_₴, то функція TEXT прийме його, але колір не відображатиметься.

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

Коди форматів для місяця, дня та року

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

Мінда Трісі (Mynda Treacy), партнер зі статусом Excel MVP

Мінда рекомендує…

Якщо ви надаєте спільний доступ до файлів і звітів Excel користувачам із різних країн, швидше за все, дані потрібно надавати різними мовами. Мінда Трісі (Mynda Treacy), партнер зі статусом Excel MVP, пропонує чудове рішення у своїй статті про відображення дат 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

Додаткові формати залежать від регіональних параметрів. Якщо додаткові формати недоступні для вашого регіону або не підходять для ваших потреб, ви можете створити власний формат, вибравши в діалоговому вікні Формат клітинок пункт (усі формати).

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

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

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

Приклад об’єднання тексту без використання функції TEXT

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

Приклад об’єднання тексту за допомогою функції TEXT

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

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

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

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

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

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

Приклад використання функції TEXT з функцією CHAR(10) для вставлення розриву рядка. ="Сьогодні: "&CHAR(10))&TEXT(TODAY();"DD.MM.YY")

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

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

Мінда Трісі (Mynda Treacy), партнер зі статусом Excel MVP

Мінда рекомендує…

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

Див. також

Створення та видалення спеціального числового формату

Перетворення тексту в числа

Усі функції Excel (за категорією)

Отримуйте нові функції раніше за інших
Приєднайтеся до оцінювачів Office

Ця інформація корисна?

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

Дякуємо за відгук! Схоже, вам може стати в нагоді допомога одного з наших спеціалістів служби підтримки Office, з яким ми вас можемо з’єднати.

×