Используйте функцию ТЕКСТ Excel для преобразования любых данных (например, чисел, дат и т. Д.) В текст в указанном пользователем формате.
Функция ТЕКСТ классифицируется как функция строки / текста, которая преобразует числовое значение в текстовую строку в указанном пользователем формате. Например, если вы хотите преобразовать дату в формате «15.03.2020» в формат «15 марта 2020 года», используйте для этого функцию ТЕКСТ.
В этом руководстве объясняется, как использовать функцию ТЕКСТ в Excel, с помощью некоторых формул и примеров.
Синтаксис
Общий синтаксис функции ТЕКСТ:
= ТЕКСТ (значение; формат_текст)Функция ТЕКСТ требует двух аргументов / параметров:
ценность- Числовое значение, которое вы хотите преобразовать в текстовую строку. Это значение может быть числовым значением, датой или ссылкой на ячейку числового значения.format_text- Код формата, который вы хотите применить к определенному значению. Его всегда следует заключать в двойные кавычки.
Коды формата функции ТЕКСТ
Функция Text имеет только два аргумента. Первый аргумент требует только значения, которое вы хотите преобразовать, это просто. Но вы должны вставить правильный код формата, который даст вам выходной номер в желаемом формате. В следующей таблице приведены наиболее распространенные и часто используемые форматы.
| Код формата | Описание | Пример |
|---|---|---|
0 | Ноль - это цифра-заполнитель, которая показывает только цифры без десятичных знаков. | # .0 - всегда отображает 1 знак после запятой. Если вы введете 5.50 в указанную ячейку, она будет отображаться как 5.5. |
# | Отображает цифру без лишних нулей. | #. ## - отображается с точностью до двух десятичных знаков. при вводе 3,777 возвращается 3,78. |
? | Отображает только цифру без десятичных знаков. Обычно он используется для выравнивания числовых значений в столбце по десятичным разрядам. | #.? - он отобразит один десятичный знак и выровняет десятичную точку. |
. | Десятичная точка | |
, | Разделитель тысяч. | ###, ### - отобразит разделитель тысяч. Если вы наберете 195200, он вернет 195 200 |
0% | Отображает числа в процентах. | Если вы введете 0,285, он вернет 28,5 |
В дополнение к приведенному выше коду формата вы также можете добавить любой из следующих символов в код формата формулы, и они будут отображаться точно так, как они были введены.
| Symboл | Описание |
|---|---|
+ и - | Знаки плюс и минус |
() | Левая и правая круглые скобки |
: | Двоеточие |
^ | Карет |
' | Апостроф |
{} | Фигурные скобки |
<> | Знаки "меньше" и "больше" |
= | Знак равенства |
/ | Косая черта |
! | Восклицательный знак |
& | Амперсанд |
~ | Тильда |
| Космический персонаж |
Коды форматов текстовых функций для даты и времени
Если вы хотите преобразовать дату и время с помощью функции ТЕКСТ, используйте любой из приведенных ниже кодов формата.
| Код формата | Описание и примеры |
|---|---|
d | Указывает день месяца в виде одно- или двузначного числа без нуля в начале (например, от 2 до 25). |
дд | Указывает день месяца в виде двузначного числа с нулем в начале (например, с 02 по 25). |
ддд | Указывает день недели в виде трехбуквенного сокращения (например, с понедельника по воскресенье). |
дддд | Задает полное название Дня недели. (например, понедельник, среда) |
м | Задает месяц года в виде одно- или двузначного числа без нуля в начале (например, от 02 до 12). |
мм | Задает месяц в виде двузначного числа с нулем в начале. (например, 01, 12) |
М-м-м | Указывает месяц в виде трехбуквенного сокращения (например, январь, ноябрь). |
мммм | Задает полное название месяца. (например, январь, ноябрь) |
гг | Указывает год в виде двузначного числа (например, 08 означает 2008 г., 19 означает 2019 г.) |
гггг | Указывает год в виде четырехзначного числа (например, 2008, 2019). |
час | Задает час в одно- или двузначном представлении без нуля в начале (например, 6, 12). |
чч | Задает час в двузначном представлении с нулем в начале (от 06 до 12). |
м | Указывает минуты в виде одно- или двузначного числа без нуля в начале (например, 5, 45). |
мм | Указывает минуты в виде одно- или двузначного числа с нулем в начале (например, 05, 45) |
s | Указывает секунды в виде одно- или двузначного числа без нуля в начале (например, 5, 45) |
SS | Указывает секунды в виде одно- или двузначного числа, начиная с нуля (например, 05, 45) |
ЯВЛЯЮСЬ / ВЕЧЕРА | Указывает, что время должно отображаться в 12-часовом формате, за которым следует «AM» или «PM». |
Как использовать функцию ТЕКСТ в Excel
Вы узнали синтаксис и коды формата функции ТЕКСТ, а теперь давайте рассмотрим, как использовать функцию в Excel, с помощью нескольких примеров.
Используйте текстовую формулу для отображения целого числа в число в ячейке A1.
Для этого мы можем использовать текстовую формулу как:
= ТЕКСТ (A1; «0»)
Используйте эту формулу для отображения одного десятичного знака:
= ТЕКСТ (A1; «0,0»)
В таблице ниже показаны текстовые формулы с разными кодами формата для применения разных типов форматирования к разным числовым значениям. Вы можете скопировать эти формулы прямо в свою электронную таблицу, чтобы попробовать их самостоятельно.
| Ценность | Формула | Форматированное значение |
|---|---|---|
| 4963.34 | = ТЕКСТ (A2; «0,000») | 4963.340 |
| 5300.52 | = ТЕКСТ (A3; "#, ## 0") | 5,301 |
| 5.12 | = ТЕКСТ (A4; «#? /?») | 5 1/8 |
| 0.4963 | = ТЕКСТ (A5; "#%") 50% | 50% |
| 9600.60 | = ТЕКСТ (A6; «$ #, ## 0,0») | $9,600.6 |
| 20 | = ТЕКСТ (A7, "~ #!") ~ 20! | ~20! |
| 5656 | = ТЕКСТ (A8; «00000000») | 00005656 |
Приведенные выше формулы применяются в столбце C электронной таблицы, как показано на снимке экрана ниже:
ТЕКСТ Функция с формулами
Вы также можете использовать другие формулы и функции как внутри, так и вне функции ТЕКСТ.
Предположим, у вас есть сумма брутто и расходов, и вы хотите рассчитать чистую прибыль и отобразить прибыль в ячейке A9 со строкой «Your Net Profit is». Для этого вы можете использовать формулу ниже:
= "Ваша чистая прибыль равна" & ТЕКСТ (C6-C7, "$ #, ###. 00")Формула сначала вычисляет прибыль по формуле (C6-C7) в функции ТЕКСТ, затем объединяет форматированное значение со строкой «Ваша чистая прибыль» с помощью формулы конкатенации (&), и, наконец, отображает результат в ячейке A9.
Форматирование мобильного номера с помощью функции ТЕКСТ
Обычно, когда вы вводите в электронную таблицу любое число, длина которого превышает 11 цифр, например номера мобильных телефонов, Excel автоматически переводит его в экспоненциальную нотацию. И вы можете правильно использовать эти научные обозначения, поэтому вы можете преобразовать их в обычные числовые значения. Вы можете использовать функцию ТЕКСТ, чтобы преобразовать эти надоедливые научные обозначения в мобильные числа.
Как показано на снимке экрана ниже, когда вы вводите мобильные номера (состоящие из 12 цифр, включая код страны) в Excel, он автоматически преобразует эти мобильные номера в формат экспоненциальной записи.
С помощью функции ТЕКСТ вы можете отформатировать этот формат экспоненциальной записи в удобочитаемые мобильные числа.
Обычно номер мобильного телефона состоит из 12 цифр (в некоторых странах это может быть разным). Первые две цифры - это код страны, а оставшиеся 10 цифр - это мобильные номера.
Итак, чтобы преобразовать эти научные обозначения в мобильные числа, используйте эту формулу:
= ТЕКСТ (A1; «+ ############»)Мы используем «############» в качестве кода формата для этого примера, чтобы преобразовать нотацию в номер мобильного телефона:
Теперь давайте сделаем его более читабельным, отделив код страны от номера мобильного телефона. Для этого поставьте дефис (-) после двух хешей.
= ТЕКСТ (A1; «+ ## - ##########»)
Форматирование даты с помощью функции ТЕКСТ
По умолчанию Excel хранит дату как серийные номера. Серийный номер на 1 января 1900 года - 1, а на 1 января 2001 года - 36892, потому что с 1 января 1900 года прошло 36891 день.
Поскольку большинство функций автоматически конвертируют значения даты в серийные номера, отображать их в удобочитаемом формате непросто. Но с помощью функции Excel TEXT можно легко преобразовать их в текстовые значения и отобразить их в желаемом формате.
Например, если вы хотите взять дату из ячейки A1 (05-03-2015) и отобразить ее в стандартном формате даты, например «5 марта 2015 года» в ячейке B1, используйте следующую формулу:
= ТЕКСТ (A1; «ммм д, гггг»)М-м-мобозначает сокращенный до 3 букв месяцdуказывает день месяца одной или двумя цифрамиггггуказывает на отображение четырехзначных чисел года.
На снимке экрана ниже показано, как можно применить разные типы форматирования к одной и той же дате, используя текстовую формулу:
Дата и текст конкатенации
Допустим, мы хотим объединить имя (столбец A) и дату рождения (столбец B) и отобразить его в столбце C. Результат будет примерно таким:
Если мы напрямую объединим текст в ячейке A1 и дату в ячейке B1, Excel объединит текст и серийный номер для даты, а не фактической даты.
Чтобы объединить текст и дату и правильно отобразить дату в желаемом формате, используйте функцию ТЕКСТ с функцией СЦЕП.
Формула:
= СЦЕПИТЬ (A2; «-»; ТЕКСТ (B2; «дд / м / гг»))Результат:
Теперь мы присоединяем текстовую строку «родилась» к выводу и отформатируем дату в другом формате.
Затем формула копируется в ячейку A2: A5 с помощью маркера заполнения.
В другом примере мы используем СЕГОДНЯ() функция, чтобы получить текущую дату, и соединить дату с некоторым связанным текстом.
Теперь вы можете легко преобразовать любое значение (например, числа, даты и т. Д.) В текст в желаемом формате.