Використовуйте функцію Excel TEXT, щоб перетворити будь-які дані (наприклад, числа, дати тощо) у текст у форматі, визначеному користувачем.
Функція TEXT класифікується як функція рядка/тексту, яка перетворює числове значення в текстовий рядок у форматі, визначеному користувачем. Наприклад, якщо ви хочете перетворити дату у форматі «15/03/2020» у формат 15 березня 2020 року, ви використовуєте для цього функцію ТЕКСТ.
У цьому посібнику пояснюється, як використовувати функцію TEXT в Excel за допомогою деяких формул і прикладів.
Синтаксис
Загальний синтаксис функції TEXT:
=ТЕКСТ(значення, формат_текст)Функція TEXT вимагає двох аргументів/параметрів:
значення– Число, яке потрібно перетворити в текстовий рядок. Це значення може бути числовим значенням, датою або посиланням на клітинку чисельного значення.формат_текст– Код формату, який потрібно застосувати до певного значення. Його завжди слід брати в подвійні лапки.
TEXT Коди формату функцій
Функція Text має лише два аргументи. Перший аргумент вимагає лише значення, яке ви хочете перетворити, це легко. Але ви повинні вставити правильний код формату, який дасть вам вихідний номер у потрібному форматі. Наступна таблиця містить найбільш поширені та часто використовувані формати.
| Код формату | Опис | Приклад |
|---|---|---|
0 | Нуль – це заповнювач цифр, який показує лише цифри без десяткових знаків. | #.0 – завжди відображає 1 знак після коми. Якщо ви введете 5,50 у клітинку, на яку посилається, вона відобразиться як 5,5. |
# | Відображає цифру без зайвих нулів. | #.## – показує до двох знаків після коми. коли ви вводите 3.777, він повертає 3.78. |
? | Відображає лише цифру без десяткових знаків. Зазвичай він використовується для вирівнювання числових значень у стовпці через десятковий знак. | #.? – відображатиметься один десятковий знак і вирівнюється десяткова кома. |
. | Десяткова кома | |
, | Роздільник тисяч. | ###,### – відображатиметься роздільник тисяч. Якщо ви введете 195200, він поверне 195200 |
0% | Відображає числа у відсотках. | Якщо ви введете 0,285, він поверне 28,5 |
Окрім наведеного вище коду формату, ви також можете додати будь-який із наступних символів у код формату формули, і вони відображатимуться точно так, як було введено.
| Символл | Опис |
|---|---|
+ і - | Знаки плюс і мінус |
() | Ліва і права дужки |
: | Товста кишка |
^ | Каретка |
' | Апостроф |
{} | Фігурні дужки |
<> | Знаки менше і більше |
= | Знак рівності |
/ | Коса смуга вперед |
! | Знак оклику |
& | Амперсанд |
~ | Тільда |
| Космічний персонаж |
Коди формату текстових функцій для дат і часу
Якщо ви хочете перетворити дату та час за допомогою функції TEXT, використовуйте будь-який із наведених нижче кодів формату.
| Код формату | Опис та приклади |
|---|---|
d | Визначає день місяця в одно- або двоцифровому числі без переднього нуля (наприклад, від 2 до 25) |
dd | Визначає день місяця у двозначному представленні з нулем на початку (наприклад, від 02 до 25) |
ддд | Визначає день тижня трибуквеною абревіатурою (наприклад, з понеділка по нд) |
дддд | Вказує повну назву дня тижня. (наприклад, понеділок, середа) |
м | Визначає місяць року в одно- або двозначному числі без нуля (наприклад, від 02 до 12) |
мм | Визначає місяць у двозначному представленні з нулем на початку. (наприклад, 01, 12) |
ммм | Визначає місяць у вигляді абревіатури з трьох літер (наприклад, січень, листопад) |
мммм | Вказує повну назву місяця. (наприклад, січень, листопад) |
yy | Визначає рік двозначним числом (наприклад, 08 означає 2008, 19 означає 2019) |
yyyy | Визначає рік у вигляді чотиризначного числа (наприклад, 2008, 2019) |
ч | Вказує годину в одно- або двоцифровому представленні без переднього нуля (наприклад, 6, 12) |
hh | Вказує годину у двозначному представленні з нулем на початку (від 06 до 12) |
м | Визначає хвилини в одно- або двозначному числі без нуля (наприклад, 5, 45) |
мм | Визначає хвилини в одно- або двозначному числі з нулем на початку (наприклад, 05, 45) |
с | Вказує секунди в одно- або двоцифровому числі без нуля на початку (наприклад, 5, 45) |
сс | Вказує секунди в одно- або двоцифровому числі з нулем на початку (наприклад, 05, 45) |
AM / PM | Вказує, що час має відображатися як 12-годинний годинник, за яким слідує «AM» або «PM» |
Як використовувати функцію TEXT в Excel
Ви дізналися синтаксис і коди формату функції TEXT, тепер давайте розглянемо, як використовувати цю функцію в Excel за допомогою кількох прикладів.
Використовуйте текстову формулу, щоб відобразити ціле число до числа в клітинці A1.
Для цього ми можемо використовувати текстову формулу як:
=ТЕКСТ(A1,"0")
Використовуйте цю формулу, щоб відобразити один десятковий знак:
=ТЕКСТ(A1,"0,0")
У таблиці нижче показано текстові формули з різними кодами формату для застосування різних типів форматування до різних числових значень. Ви можете скопіювати ці формули безпосередньо в електронну таблицю, щоб спробувати самостійно.
| Значення | Формула | Відформатовано значення |
|---|---|---|
| 4963.34 | =ТЕКСТ(A2,"0,000") | 4963.340 |
| 5300.52 | =ТЕКСТ(A3,"#,##0") | 5,301 |
| 5.12 | =TEXT(A4,"# ?/?") | 5 1/8 |
| 0.4963 | =ТЕКСТ(A5,"#%") 50% | 50% |
| 9600.60 | =TEXT(A6,"$#,##0.0") | $9,600.6 |
| 20 | =TEXT(A7,"~#!") ~20! | ~20! |
| 5656 | =ТЕКСТ(A8,"00000000") | 00005656 |
Наведені вище формули застосовуються до стовпця C електронної таблиці, як показано на знімку екрана нижче:
TEXT Функція з формулами
Ви також можете використовувати інші формули та функції як всередині, так і за межами функції TEXT.
Припустимо, що у вас є сума валових і витрат, і ви хочете розрахувати чистий прибуток і відобразити прибуток у клітинці A9 з рядком «Ваш чистий прибуток». Для цього можна використовувати формулу нижче:
="Ваш чистий прибуток становить "&TEXT(C6-C7,"$#,###.00")Формула спочатку обчислює прибуток за формулою (C6-C7) у функції TEXT і об’єднує відформатовану величину з рядком «Ваш чистий прибуток є» за допомогою формули конкатенації (&), і, нарешті, відображає результат у клітинці A9.
Форматуйте номер мобільного телефону за допомогою функції TEXT
Зазвичай, коли ви вводите в електронній таблиці будь-яке число, що перевищує 11 цифр, наприклад, номери мобільних телефонів, Excel автоматично перетворює його на наукову нотацію. І ви можете правильно використовувати ці наукові позначення, тому ви можете перетворити їх на звичайні числові значення. Ви можете використовувати функцію TEXT, щоб перетворити ці дратівливі наукові позначення в мобільні номери.
Як показано на знімку екрана нижче, коли ви вводите номери мобільних телефонів (довжиною 12 цифр, включаючи код країни) в Excel, він автоматично перетворює ці номери мобільних телефонів у формат наукового позначення.
За допомогою функції ТЕКСТ ви можете відформатувати цей формат наукового позначення в читабельні номери мобільних пристроїв.
Зазвичай номер мобільного телефону складається з 12 цифр (у деяких країнах це може відрізнятися). Перші дві цифри – це код країни, а решта 10 цифр – номери мобільного телефону.
Отже, щоб перетворити ці вищенаведені наукові позначення в мобільні номери, скористайтеся цією формулою:
=TEXT(A1,"+############")Ми використовуємо «############» як код формату для цього прикладу для перетворення позначення в номер мобільного телефону:
Тепер давайте зробимо його більш читабельним, відокремивши код країни від номера мобільного телефону. Для цього поставте дефіс (-) після двох хешів.
=TEXT(A1,"+##-###########")
Формат дати за допомогою функції TEXT
За замовчуванням Excel зберігає дату як серійні номери. Порядковий номер для 1 січня 1900 року — 1, а 1 січня 2001 — 36892, оскільки з 1 січня 1900 року 36891 день.
Оскільки більшість функцій автоматично перетворюють значення дати в серійні номери, важко відобразити їх у форматі для читання. Але за допомогою функції Excel TEXT можна легко конвертувати їх у текстові значення та відображати їх у потрібному форматі.
Наприклад, якщо ви хочете взяти дату з клітинки A1 (05-03-2015) і показати її у стандартному форматі дати, як-от «5 березня 2015» у клітинці B1, ви використовуєте таку формулу:
=TEXT(A1,"mmm d,yyyy")мммвказує на 3 букви скорочений місяцьdвказує день місяця однією або двома цифрамиyyyyвизначає відображення чотиризначних чисел року.
На знімку екрана нижче показано, як можна застосувати різні типи форматування до однієї дати за допомогою текстової формули:
Дата і текст конкатенації
Скажімо, ми хочемо об’єднати ім’я (стовпець A) і дату народження (стовпець B) і відобразити їх у стовпці C. Ви отримаєте такий результат:
Якщо ми безпосередньо об’єднаємо текст у клітинці A1 і дату в клітинці B1, Excel об’єднає текст і серійний номер для дати, а не фактичної дати.
Щоб об’єднати текст і дату і правильно відобразити дату в потрібному форматі, використовуйте функцію ТЕКСТ з функцією CONCAT.
Формула:
=CONCAT(A2;"-",TEXT(B2;"дд/м/рр"))Результат:
Тепер ми приєднаємо текстовий рядок «народжено» до виводу та відформатуємо дату в іншому форматі.
Потім формула копіюється в комірку A2:A5 за допомогою маркера заповнення.
В іншому прикладі ми використовуємо СЬОГОДНІ() функція, щоб отримати поточну дату та об’єднати дату з деяким пов’язаним текстом.
Тепер ви можете легко конвертувати будь-яке значення (наприклад, числа, дати тощо) у текст у бажаному форматі.