TEXT (функція TEXT)

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

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

За бажання можна завантажити книгу з усіма прикладами застосування функції 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 видало свою програму 0, ви можете використовувати функцію Text , щоб додати їх назад. Ви можете посилатися на верхню клітинку за допомогою значень і використовувати текст = Text (Value, "00000"), де кількість 0 у формулі позначає загальну кількість символів, а потім скопіюйте та вставте до решти діапазону.

Приклади використання функції 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".

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

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

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

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

Функція 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 для ЗАСТОСУНКІВ (VBA). У наведеному нижче посиланні є метод: перетворення числового значення на англійські слова в програмі Excel

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

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

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

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

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

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

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

Див. також

Створення та видалення настроюваного числового формату

Перетворення чисел із текстового формату на числовий

Усі функції Excel (за категоріями)

Примітка.:  Цю сторінку перекладено за допомогою засобу автоматичного перекладу, тому вона може містити смислові, синтаксичні або граматичні помилки. Ми вважаємо, що цей вміст стане вам у пригоді. Повідомте нас, чи була ця інформація корисною. Для довідки цю статтю можна переглянути англійською мовою.

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

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

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

Дякуємо, що знайшли час і надіслали нам відгук! Можливо, у нас не буде часу відповісти на кожен коментар, але докладемо максимум зусиль, щоб переглянути їх усі. Вас цікавить, як ми використовуємо ваші відгуки?

×