TEXT (функція TEXT)

Огляд

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

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

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

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

За бажання можна завантажити книгу з усіма прикладами застосування функції 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 видалила 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 додати новий рядок (розрив рядка) до клітинки так само, як за допомогою сполучення клавіш Alt+Enter?

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

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

Додаткові відомості

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

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

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