Функция ТЕКСТ (TEXT) — Справочник
Функция ТЕКСТ преобразует численное значение в текст и позволяет задать формат отображения с помощью специальных строк форматирования.
Описание функции ТЕКСТ
Функция ТЕКСТ преобразует численное значение в текст и позволяет задать формат отображения с помощью специальных строк форматирования. Эта функция полезна, если числа требуется отобразить в более удобном формате или если требуется объединить числа с текстом или символами.
Например, чтобы отформатировать число 23,5 как сумму в рублях, можно использовать указанную ниже формулу:
=ТЕКСТ(A1,"0,00р.")
Результат: 23,50р.
Числа также можно форматировать с помощью команд в группе Число, которая находится на вкладке ленты Главная. Однако эти команды работают лишь в том случае, если ячейка содержит только число. Если требуется отформатировать число и объединить его с другим текстом, лучше всего использовать функцию ТЕКСТ. Например, к предыдущей формуле можно добавить следующий текст:
=ТЕКСТ(A1,"0,00р.")& " в час"
")& " в час"Результат: 23,50р. в час.
Синтаксис
=ТЕКСТ(значение; формат)
Аргументы
значениеформат
Обязательный аргумент. Численное значение, формула, вычисление которой дает числовое значение, или ссылка на ячейку, содержащую численное значение.
Обязательный аргумент. Численный формат в виде текстовой строки в кавычках (например, «Д.М.ГГГГ» или «# ##0,00». Правила форматирования представлены ниже.
Форматы чисел
| Описание | |
|---|---|
| 0 (ноль) | Отображает незначащие нули, если число имеет меньше разрядов, чем количество нулей в строке форматирования. Например, формат #,00 позволяет отобразить значение 8,9 как 8,90. |
| # | Работает так же, как 0 (ноль). Но в этом случае функция Text не возвращает дополнительные нули, если число имеет слева или справа от десятичного разделителя меньше цифр, чем количество символов # в строке форматирования. Например, число 8,9 при использовании формата #,## будет отображаться как 8,9. |
| ? | Действует по тем же правилам, что и 0 (нуль). Однако, до и после запятой вместо незначащих нулей отображаются пробелы, чтобы десятичные разделители были выровнены в столбце. Например, формат 0,0? обеспечивает выравнивание запятых для чисел 8,9 и 88,99. (Правда, для чистоты эксперимента, следует выбрать моноширинный шрифт) |
| , (запятая) | Отображает символ десятичного разделителя. Поведение этого символа зависит от языка пользовательского формата. |
Если в дробной части числа больше цифр, чем заполнителей в формате, число округляется до количества разрядов, равного количеству заполнителей. Если в целой части числа больше цифр, чем заполнителей в формате, отображаются дополнительные цифры. Если формат содержит слева от запятой только знаки номера ( #), то числа, меньшие 1, будут начинаться с запятой, например ,47.
| Для отображения | В виде | Используемый формат |
|---|---|---|
| 1234,59 | 1234,6 | «####,#» |
| 8,9 | 8,900 | «#,000» |
| 0,631 | 0,6 | «0,#» |
| 12 | 12,0 1234,57 | «#,0#» |
| 44,398 102,65 2,8 | 44,398 102,65 2,8 (с выравниванием запятых) | «???,???» |
| 5,25 5,3 | 5 1/4 5 3/10 (с выравниванием дробных частей) | «# ???/???» |
Разделитель групп разрядов
Чтобы отображать пробел в качестве разделителя групп разрядов или представлять число в масштабе, кратном 1000, включите в числовой формат разделитель, указанный ниже.
| Для отображения | В виде | Используемый формат |
|---|---|---|
| 12000 | 12 000 | «# ###» |
| 12000 | 12 | «# « |
| 12200000 | 12,2 | «0,0 « |
Форматы даты и времени.
Дни, месяцы и годы. Для отображения чисел в формате даты (с указанием числа, месяца и года) в аргументе формат используются коды, указанные ниже.
| Формат | Описание |
|---|---|
| М | Отображение месяца в виде числа без начального нуля. |
| ММ | Отображение месяца в виде числа с начальным нулем, если он необходим. |
| МММ | Отображение сокращенного названия месяца (Янв–Дек). |
| ММММ | Отображение полного названия месяца (Январь–Декабрь). |
| МММММ | Отображение месяца в виде одной буквы (Я–Д). |
| Д | Отображение дня в виде числа без начального нуля. |
| ДД | Отображение дня в виде числа с начальным нулем, если он необходим. |
| ДДД | Отображение сокращенного названия дня недели (пн–вс). |
| ДДДД | Отображение полного названия дня недели (понедельник–воскресенье). |
| ГГ | Отображение года в виде двузначного числа. |
| ГГГГ | Отображение года в виде четырехзначного числа. |
| Для отображения | В виде | Используется форма |
|---|---|---|
| Месяцев | 1-12 | «М» |
| Месяцев | 01-12 | «ММ» |
| Месяцев | Янв-Дек | «МММ» |
| Месяцев | Январь-Декабрь | «ММММ» |
| Месяцев | Я-Д | «МММММ» |
| Дней | 1-31 | «Д» |
| Дней | 01-31 | «ДД» |
| Дней | пн-вс | «ДДД» |
| Дней | понедельник-воскресенье | «ДДДД» |
| Лет | 00-99 | «ГГ» |
| Лет | 1900-9999 | «ГГГГ» |
Часы, минуты и секунды
Для отображения чисел в формате времени (с указанием часов, минут и секунд) в аргументе формат используются коды, указанные ниже.
| Формат | Описание |
|---|---|
| ч | Отображение часов в виде числа без начального нуля. |
| [ч] | Отображение истекшего времени в часах. Если формула возвращает значение времени, в котором число часов превышает 24, используйте числовой формат наподобие следующего: [ч]:мм:сс. |
| чч | Отображение часов в виде числа с начальным нулем, если он необходим. Если формат содержит пометку AM или PM, часы отображаются на основе 12-часового цикла. В противном случае используется 24-часовой цикл. |
| м | Отображение минут в виде числа без начального нуля. Примечание. Код м или мм должен быть указан непосредственно после кода ч или чч или непосредственно перед кодом сс, иначе вместо минут отображается месяц. |
| [м] | Отображение истекшего времени в минутах. Если формула возвращает значение времени, в котором число минут превышает 60, используйте числовой формат наподобие следующего: [мм]:сс. |
| мм | Отображение минут в виде числа с начальным нулем, если он необходим. Примечание. Код м или мм должен быть указан непосредственно после кода ч или чч или непосредственно перед кодом сс, иначе вместо минут отображается месяц. |
| с | Отображение секунд в виде числа без начального нуля. |
| [с] | Отображение истекшего времени в секундах. Если формула возвращает значение времени, в котором число секунд превышает 60, используйте числовой формат наподобие следующего: [сс]. |
| сс | Отображение секунд в виде числа с начальным нулем, если он необходим. Для отображения долей секунды используйте числовой формат наподобие следующего: ч:мм:сс,00. |
| AM/PM, am/pm, A/P, a/p | Отображение часов на основе 12-часового цикла. Коды AM, am, A и a означают время суток от полуночи до полудня, а PM, pm, P и p — время от полудня до полуночи. |
| Для отображения | В виде | Используется форма |
|---|---|---|
| Часов | 0-23 | «ч» |
| Часов | 00-23 | «чч» |
| Минут | 0-59 | «м» |
| Минут | 00-59 | «мм» |
| Секунд | 0-59 | «с» |
| Секунд | 00-59 | «сс» |
| Времени | 4:00 | «ч:мм» |
| Времени | 16:04 | «чч:мм» |
| 3:32:05 | «ч:мм:сс» | |
| Времени | 3:32:05,75 | «ч:мм:сс,00» |
| Времени | 1:02 | «[ч]:мм» |
| Истекшего времени (в часах и минутах) | 63:23 | «[мм]:сс» |
| Истекшего времени (в часах и минутах) | 3456,70 | «[сс],00» |
Форматы денежных сумм, процентов и экспоненциального представления
Символы валют.
Чтобы указать перед числом знак доллара ($), введите его в начале аргумента формат (например, «$# ##0,00»). Чтобы включить в числовой формат один из указанных ниже символов валют, нажмите клавишу NUM LOCK и введите с цифровой клавиатуры код ANSI соответствующего символа.
| Символ | Сочетание клавиш |
|---|---|
| ¢ | ALT+0162 |
| £ | ALT+0163 |
| ¥ | ALT+0165 |
| Евро | ALT+0128 |
- Примечание. Необходимо использовать цифровую панель клавиатуры. При использовании клавиши ALT с цифрами верхнего ряда клавиш коды ANSI не генерируются.
- Процентные значения. Чтобы отображать числа в виде процентов — например, 0,08 как 8% или 2,8 как 280%, — включите в аргумент формат знак процента (%).
- Экспоненциальное представление. Для отображения чисел в экспоненциальном формате включите в аргумент формат код из числа указанных ниже.
Диакритический знак (крышка) ‘ Апостроф { Левая фигурная скобка < Знак «меньше» = Знак равенства — Знак «минус» / Косая черта ) Правая круглая скобка ! Восклицательный знак & Амперсанд ~ Тильда } Правая фигурная скобка > Знак «больше» Пробел Замечания
- Аргумент формат не может содержать звездочку (*).
- Функция ТЕКСТ преобразует число в форматированный текст, и результат больше не может быть использован в вычислениях в качестве числа. Чтобы отформатировать число и при этом оставить его числом, щелкните ячейку правой кнопкой мыши, выберите команду Формат ячеек и в диалоговом окне Формат ячеек на вкладке Число настройте нужные параметры форматирования. Для получения дополнительных сведений об использовании диалогового окна Формат ячеек нажмите кнопку справки (?) в верхнем правом углу окна.
Пример
Видео работы функции
Автонумерация внутри записи
Дополнительные материалы
Файл Excel с примерами использования функции ТЕКСТ (на английском языке)
Текст
Сортировка: дата создания дата изменения просмотры комментарии
Регистро НЕ чувствительность в Power Query (и Power BI)
Как победить регистрочувствительность в Power Query при фильтрации, удалении дубликатов и объединении таблиц с помощью функции Comparer.OrdinalIgnoreCase
Деление слипшегося текста функцией ФИЛЬТР.XML
Быстрый способ разделить слипшиеся данные на несколько ячеек с помощью функции ФИЛЬТР.
XML, превратив исходный текст в XML-код.Поиск ключевых слов в тексте
Как быстро найти в исходном тексте все ключевые слова из справочника и вывести их рядом с каждой ячейкой данных через заданный символ-разделитель. Попутно разбираемся с буферизацией запросов в Power Query с помощью функции Table.Buffer для ускорения обработки.
Массовая замена текста в Power Query функцией List.Accumulate
Как заменить множество одних фрагментов текста на другие по справочнику в Power Query с помощью хитрой функции List.Accumulate.
Массовая замена текста формулами
Как с помощью формулы произвести массовую замену одного текста (или его фрагмента) на другой по имеющейся таблице подстановок (справочнику).
Регулярные выражения (RegExp) в Power Query
Как добавить поддержку регулярных выражений (RegExp) в Power Query для реализации поиска и извлечения фрагментов текста по гибким шаблонам и маскам.
Нечёткий текстовый поиск в Power Query
Подробный разбор нового инструмента в Power Query — объединения таблиц с помощью нечёткого текстового поиска.
Дубликаты внутри ячейки
Как бороться с повторами в тексте внутри ячейки: обнаруживать их, выделять цветом или удалять. С помощью формул, макросов или запросов Power Query.
Суперсила Мгновенного заполнения (Flash Fill)
Подробный разбор вариантов применения одного из самых удивительных, но малоизвестных инструментов — Мгновенного заполнения (Flash Fill). Поможет для обработки текста: нарезки, склейки, исправления регистра, добавления или удаления слов, извлечения чисел из текста и многого другого.
Тонкости работы с переносами строк в Excel
Как работать с переносами строк (Alt+Enter) в Excel: удалять их, заменять на пробелы и другие символы, делить по ним данные на столбцы или строки.
Генератор фраз из заданных фрагментов
Как быстро сгенерировать все возможные фразы, состоящие из заданных наборов слов в любом порядке, используя Декартово произведение множеств с помощью формул или Power Query.
Нечеткий текстовый поиск с Fuzzy Lookup в Excel
Как использовать бесплатную надстройку Fuzzy Lookup для нечеткого поиска ближайших текстовых соответствий в двух таблицах на примере поиска совпадающих адресов.
Импорт данных из PDF в Excel через Power Query
Как перенести данные из таблицы PDF-документа на лист Microsoft Excel, используя только Word и Power Query.
Последнее слово
Как извлечь из текста последнее слово или фрагмент по заданному символу-разделителю. Разбор нескольких способов (формулы, макросы, Power Query).
Анализ текста регулярными выражениями (RegExp) в Excel
Как с помощью регулярных выражений (Regular Expression = RegExp) находить и извлекать из текста фрагменты по заданным сложным шаблонам.
Например, вытащить номер счета из описания платежа, город из адреса, телефон из контактов клиента и т.п.Превращение текста в число функцией Ч (N)
Как добавить невидимый текстовый комментарий к формуле в ячейке с помощью функции преобразованиия Ч(N).
Замена текста функцией ПОДСТАВИТЬ (SUBSTITUTE)
Подробный разбор функции замены текста ПОДСТАВИТЬ (SUBSTITUTE) и примеров ее использования для удаления неразрывных пробелов, подсчета количества слов, извлечения первых двух слов из текста.
Удаление лишних пробелов функцией СЖПРОБЕЛЫ (TRIM) и формулами
Как удалить из текста лишние пробелы в начале, в конце или между словами с помощью функции СЖПРОБЕЛЫ (TRIM) или формул.
Поиск точных совпадений с учетом регистра функцией СОВПАД (EXACT)
Большинство функций Excel не различают строчные и прописные буквы. Если же это необходимо, то сможет помочь функция СОВПАД (EXACT), умеющая сравнивать данные с учетом регистра.
Определение пола по имени
Несколько способов формулами определить пол людей в списке по их именам.
Преобразование чисел-как-текст в нормальные числа
Иногда Excel начинает воспринимать числа в некоторых ячейках как текст. Из за этого не работают формулы, нарушается сортировка и фильтрация и т.д. Как же исправить ситуацию?
Склеивание текста по условию
Как при помощи формул, макро-функций или надстройки Power Query склеить (сцепить) текст из нескольких ячеек, но только при выполнении заданного условия. Что-то похожее на функцию СУММЕСЛИ (SUMIF), но — для текста. Причем условия могут быть как точными, так и приблизительными или их может быть несколько.
Подсветка лишних пробелов
Как подсветить ячейки с лишними пробелами в полях ввода формы, намекнув пользователю, что он некорректно ввел данные.
Подробнее.
..Зачистка текста
Несколько способов очистить текстовые данные в Excel от ненужных символов, мешающих последующей обработке: лишних пробелов, непечатаемых символов, апострофов, латиницы и т.д.
Делим слипшийся текст на части
Несколько способов разделить текст из одной ячейки (столбца) — на несколько. Например, поделить столбец с адресом на три отдельных столбца с индексом, городом, улицей в каждом по-отдельности. Или разделить слипшееся ФИО без пробелов на отдельные слова.
Поиск символов латиницы в русском тексте
Как быстро найти в символы латиницы, ошибочно набранные вместо русских букв (английская «С» вместо русской «С») в большом списке?
Подробнее…
Проверка текста по маске
Как быстро проверить соответствует ли текст в ячейке заданной маске (с использованием символов *, ?, # и т.
д.) Очень полезно для поиска неправильно введенных адресов, номеров автомобилей, паспортов, артикулов и т.п.Заполнение бланков данными из таблицы
Как при помощи функции ВПР (VLOOKUP) и простого макроса быстро заполнять любой бланк (счет-фактура, платежка, приходно-кассовый ордер и т.д.) данными из таблицы.
3 способа склеить текст из нескольких ячеек
Как собрать текст из нескольких ячеек в одну с помощью функций СЦЕП, СЦЕПИТЬ, ОБЪЕДИНИТЬ и объединять ячейки без потери текста всех ячеек кроме верхней левой специальным макросом.
Поиск ближайшего похожего текста
Пользовательская функция на VBA для поиска наиболее похожего текста в списке (по максимальному количеству совпадений символов).
Количество слов в ячейке
Простая, но красивая формула для быстрого подсчета количества слов в ячейке.
Подробнее.
..Макрос-переводчик
Если Вам приходится делать многоязычные отчеты, то Вам пригодится этот макрос, умеющий переводить таблицы Excel с одного языка на другой. Всем работающим в иностранных компаниях посвящается…
Сумма прописью
Готовая пользовательская функция на VBA для перевода чисел в текст, т.е. в сумму прописью (123 = сто двадцать три).
Транслит
Небольшой макрос для быстрого преобразования русских символов в английские, т.е. кириллицы в транслит (Пупкин в Pupkin и т.п.)
Функция ТЕКСТ Excel с примерами формул
В этом учебном пособии объясняются особенности функции ТЕКСТ Excel и показаны некоторые умные способы использования формул текста в Excel.
Microsoft Excel — это все о числах, и в ваших рабочих листах вы, вероятно, будете иметь дело с числами большую часть времени. Однако бывают ситуации, когда вам нужно преобразовать числа в текст, и именно здесь вступает в дело функция ТЕКСТ.
0010 - Коды формата, используемые в функции ТЕКСТ
- Как использовать функцию ТЕКСТ в Excel — примеры формул
- Объединение текста и числа/даты в пользовательском формате
- Добавление ведущих нулей к числам
- Преобразование значений в телефонные номера в определенном формате
- Функция Excel TEXT не работает — причины и решения
- Другие текстовые функции Excel
Для получения дополнительных сведений об использовании диалогового окна Формат ячеек нажмите кнопку справки (?) в верхнем правом углу окна.
XML, превратив исходный текст в XML-код.
Например, вытащить номер счета из описания платежа, город из адреса, телефон из контактов клиента и т.п.
..
д.) Очень полезно для поиска неправильно введенных адресов, номеров автомобилей, паспортов, артикулов и т.п.
..
0010Функция ТЕКСТ в Excel – синтаксис и основное использование
По сути, ТЕКСТ в Excel используется для преобразования числового значения в текстовую строку в определенном формате.
Синтаксис функции ТЕКСТ Excel следующий:
ТЕКСТ(значение, формат_текст)
Где:
- Значение — числовое значение, которое необходимо преобразовать в текст. Это может быть число, дата, ссылка на ячейку, содержащую числовое значение, или другая функция, возвращающая число или дату.
- Format_text — формат, который вы хотите применить. Он предоставляется в виде кода формата, заключенного в кавычки, например. «мм/дд/гг».
Он предоставляется в виде кода формата, заключенного в кавычки, например. «мм/дд/гг».Функция ТЕКСТ доступна во всех версиях Excel 365, Excel 2021, Excel 2019, Excel 2016, Excel 2013, Excel 2010, Excel 2007 и более ранних версиях.
Как правило, формула Excel TEXT используется в следующих ситуациях:
- Для отображения чисел в более удобочитаемом виде или в формате, удобном для пользователей.
- Для отображения дат в определенном формате.
- Для объединения чисел или дат с определенным текстом или символами.
Например, если вы хотите извлечь дату из ячейки A2 и отобразить ее в другой ячейке в традиционном формате даты, например «1 января 2016 г.», используйте следующую формулу ТЕКСТ Excel:
=ТЕКСТ(A2, " mmmm d, yyyy")
Результат будет выглядеть примерно так:
Важное примечание! Функция ТЕКСТ в Excel преобразует числовое значение в текстовые строки . Следовательно, вы не сможете использовать результат формулы Text в других вычислениях.
Если есть такая необходимость, вы можете оставить исходные значения (скрыть или убрать из поля зрения) и использовать их в других формулах.
Коды форматов функции ТЕКСТ в Excel
Как вы только что видели, синтаксис функции ТЕКСТ в Excel очень прост, сложная часть заключается в предоставлении правильного кода формата, который будет выводить ваше число так, как вы хотите. По сути, функция ТЕКСТ принимает большинство кодов форматов, используемых в числовых форматах Excel. В таблице ниже перечислены наиболее распространенные и часто используемые из них.
| Код | Описание | Пример кода формата |
| 0 | Заполнитель цифр, отображающий незначащие нули. | #.00 — всегда отображает 2 знака после запятой. Если вы введете 2,5 в указанную ячейку, она будет отображаться как 2,50. |
| # | Заполнитель цифр, не отображающий лишние нули. | #. ## — отображает до 2 знаков после запятой.Если вы наберете 2.5, оно будет отображаться как 2.5. Если вы наберете 2,555, оно будет отображаться как 2,56. |
| ? | Заполнитель цифр, оставляющий место для незначащих нулей, но не отображающий их. Обычно он используется для выравнивания чисел в столбце по десятичной точке. | #.?? — отображает максимум 2 десятичных знака и выравнивает десятичные точки в столбце. |
| . (период) | Десятичная точка | |
| , (запятая) | Разделитель тысяч. | ###,###.## — отображает разделитель тысяч и 2 знака после запятой. Если вы наберете 250000, оно будет отображаться как 250 000,00 |
Кроме того, вы можете включить любой из следующих символов в код формата, и они будут отображаться точно так, как введены.
| Символ | Описание | |
| + и — | Знаки плюс и минус | |
| ( ) | Левая и правая скобки | |
| : | Каре | |
| Апостроф | ||
| { } | Фигурные скобки | |
| < > | Знаки «больше» и «меньше» | |
| = | Знак равенства | |
| / | Косая черта | |
| ! | Восклицательный знак | |
| и | Амперсанд | |
| ~ | Тильда | |
| Пробел |
В следующей электронной таблице показано, как можно использовать текстовую формулу в Excel для применения различных типов форматирования к одному и тому же значению.
| А | Б | С | |
|---|---|---|---|
| 1 | Исходное значение | Форматированное значение | Формула |
| 2 | 5,5 | 5,50 | =ТЕКСТ(A2, «0.00») |
| 3 | 550% | =ТЕКСТ(A2;»#%») | |
| 4 | 5,50 $ | =ТЕКСТ(A2, «$#,##0.00») | |
| 5 | + 5,50 долл. США | =ТЕКСТ(A2, «+ $#,##0.00;- $#,##0.00;$0.00») | |
| 6 | — 5,50 долл. США | =ТЕКСТ(A2, «- $#,##0.00;- $#,##0.00;$0.00») | |
| 7 | 5 1/2 | =ТЕКСТ(A2;»# ?/?») | |
| 8 | 5.50E+00 | =ТЕКСТ(A2;»0.00E+00″) | |
| 9 | ~6 ! | =ТЕКСТ(A2,»~# !») |
При использовании функции ТЕКСТ Excel с датами и умножить на можно использовать любой из следующих кодов формата.
| Код формата | Описание | Примеры |
| д | День месяца или день недели | d — одно- или двузначное число без ведущего нуля (от 1 до 31) dd — двузначный номер с нулем в начале (от 01 до 31) ddd — трехбуквенная аббревиатура (с понедельника по воскресенье) дддд — полное название дня недели (с понедельника по воскресенье) |
| м | Месяц (при использовании как часть даты) | m — одно- или двузначное число без ведущего нуля (от 1 до 12) мм — двузначное число с нулем в начале (от 01 до 12) ммм — сокращенный месяц (с января по декабрь) мммм — полное название месяца (с января по декабрь) |
| у | Год | yy — двузначное число (например, 06 означает 2006 год или 16 означает 2016 год) гггг — четырехзначное число (например, 2006, 2016) |
| ч | час | h — одно- или двузначное число без ведущего нуля (от 1 до 24) чч — двузначное число с нулем в начале (от 01 до 24) |
| м | Минута (при использовании как часть времени) | m — одно- или двузначное число без ведущего нуля (от 1 до 60) мм — двузначное число с нулем в начале (от 01 до 60) |
| с | Второй | s — одно- или двузначное число без ведущего нуля (от 1 до 60) сс — двузначный номер с нулем в начале (от 01 до 60) |
| AM/PM | Время в 12-часовом формате, за которым следует «AM» или «PM» | |
В следующей электронной таблице показано несколько текстовых формул Excel для дат:
| А | Б | С | |
|---|---|---|---|
| 1 | Исходная дата | Дата форматирования | Формула |
| 2 | 09. 12.2016 | 9 декабря 2016 г. | =ТЕКСТ(A2, «ммм дд гггг») |
| 3 | Пятница, 09 декабря 2016 г. | =ТЕКСТ(A2, «ддд дд мммм, гггг») | |
| 4 | 9 декабря 16 | =ТЕКСТ(A2, «д-ммм-гг») | |
| 5 | Пятница | =ТЕКСТ(A2;»дддд») |
Как использовать функцию ТЕКСТ в Excel. Примеры формул диалоговое окно, доступное через контекстное меню ячейки или сочетание клавиш Ctrl+1). Ситуация меняется, как только вы начинаете использовать ТЕКСТ в сочетании с другими функциями Excel. Приведенные ниже примеры дадут вам несколько вдохновляющих идей.
Объединение текста и числа (или даты) в пользовательском формате
При создании сводных таблиц или отчетов часто может потребоваться не только подсчет итогов, процентов и т.п., но и объяснение пользователям, что означает то или иное число. Для этого используйте функцию СЦЕПИТЬ, чтобы соединить текст и числа, и функцию ТЕКСТ, чтобы отобразить число (или дату) так, как вы хотите.
Пример 1. Форматирование чисел внутри текстовой строки
Предположим, вы рассчитываете общую сумму на основе цены за единицу в ячейке A2, количества в B2 и процента скидки в C2, используя этот расчет: =A2*B2*(1-C2) . Чтобы ваши пользователи точно знали, что означает выходной номер, вы хотите отобразить его вместе с пояснительным текстом, например « Ваша цена ». Кроме того, вы хотите показать символ валюты, разделитель тысяч и два десятичных знака.
Чтобы это сделать, укажите вышеприведенное вычисление в 1-м аргументе функции ТЕКСТ, включите соответствующий код формата во 2-й аргумент и соедините текстовую формулу со строкой, используя либо оператор амперсанда, либо функцию СЦЕПИТЬ:
="Ваша цена "&ТЕКСТ(A2*B2*(1-C2),"$###,###.00")
или
= СЦЕПИТЬ("Ваша цена ", ТЕКСТ(A2*B2*(1-C2), "$###,###.00"))
На следующем снимке экрана показан результат:
Пример 2. Объедините текст и дату в нужном формате
Когда вы возвращаете текущую дату с помощью функции СЕГОДНЯ() или СЕЙЧАС(), вы также можете захотеть отобразить ее в сочетании с каким-либо текстом, чтобы ни у кого не возникло сомнений к какому дню относится эта дата.
Однако, если вы попытаетесь соединить текст и дату обычным способом:
=СЦЕПИТЬ("Сегодня", СЕГОДНЯ())
Excel вернет очень странный результат, что-то вроде » Сегодня 42198 «.
Дело в том, что во внутренней системе Excel даты хранятся в виде чисел, и это число появляется в виде сцепленной текстовой строки. Чтобы исправить это, используйте функцию ТЕКСТ, чтобы отобразить дату так, как вы хотите.
Например, встраивание функции СЕГОДНЯ в текстовую формулу с кодом формата «дддд д ммм, гггг» вернет строку, подобную этой: «Сегодня понедельник, 12 декабря 2016 года».
Полная формула выглядит следующим образом:
= СЦЕПИТЬ ("Сегодня", ТЕКСТ(СЕГОДНЯ(), "дддд д ммм, гггг"))
или
="Сегодня " & ТЕКСТ( СЕГОДНЯ(), "дддд д ммм, гггг")
Еще несколько примеров формул можно найти здесь: Объединение чисел и дат в различных форматах.
Добавление начальных нулей к числам переменной длины
Как вы знаете, Microsoft Excel автоматически удаляет начальные нули, введенные перед числом в ячейке, что нормально работает в большинстве ситуаций.
Но что, если вы хотите сохранить предшествующие нули?
Функция ТЕКСТ Excel может быть простым решением для дополнения чисел ведущими нулями в столбце, даже если исходные значения имеют разную длину. Просто используйте код формата, содержащий только нулевые заполнители, такие как «0000000», где количество нулей соответствует количеству цифр, которые вы хотите отобразить.
Например, чтобы отобразить 7-значные числа с ведущими нулями, используйте эту формулу (где A2 — исходное число):
=ТЕКСТ(A2,"0000000")
Как вы можете видеть на снимке экрана ниже , наша формула Excel Text добавляет столько начальных нулей, сколько необходимо для создания длинной строки из 7 символов (пожалуйста, помните, что результатом функции ТЕКСТ в Excel всегда является текстовая строка, даже если она выглядит как число).
Преобразование значений в номера телефонов в определенном формате
Преобразование столбца числовых значений в номера телефонов может показаться сложной задачей, но только до тех пор, пока вы не вспомните, что функция ТЕКСТ Excel позволяет использовать дефисы и круглые скобки в кодах форматов.
Таким образом, чтобы отобразить номер в формате A2 в традиционном местном 7-значном телефонном формате США, таком как 123-456, используйте следующую формулу:
=ТЕКСТ(A2, "###-####")
Если некоторые из исходных значений могут содержать внутренний префикс (т. е. могут быть как 7-значные, так и 10-значные числа), включите следующий код условного формата для отображения 10-значных чисел в (123) 456-789формат:
=ТЕКСТ(A2,"[<=9999999]###-####;(###) ###-####")
На снимке экрана ниже показан этот текст Excel формула в действии:
Функция ТЕКСТ в Excel не работает - причины и решения
По сравнению с другими функциями Excel, функция ТЕКСТ очень проста и безболезненна, и у вас вряд ли возникнут трудности при ее использовании в электронных таблицах. Если, вопреки всем ожиданиям, текстовая формула у вас не работает, в большинстве случаев это связано с вводом неверного кода формата в поле 9.0003 формат_текст аргумент.
Вот две наиболее типичные проблемы:
- Функция ТЕКСТ возвращает #ИМЯ? ошибка, если вы опускаете кавычки вокруг кода формата.
Например, формула
=ТЕКСТ(A2, мм/дд/гг)неверна и должна быть записана следующим образом:=ТЕКСТ(A2, "мм/дд/гг") - Функция ТЕКСТ в Excel зависит от языка и требует использования кодов формата даты и времени для региона.
Например, формула =ТЕКСТ(A2, "мм/дд/гг") , который отлично работает для англоязычных пользователей, может возвращать ошибку #VALUE в других языковых стандартах. В частности, в Германии необходимо использовать следующие коды формата: « t » вместо « d » и « j » вместо « y », потому что «день» по-немецки « tag" и год "jahr"; «m» (месяц) подходит, потому что в немецком языке оно также начинается с «m» (monat). Таким образом, в немецком Excel приведенная выше формула будет выглядеть следующим образом: =ТЕКСТ(A2; "mm/tt/jj") .
Во Франции вы должны использовать код формата «mm/jj/aa», потому что «день» — это «jour», «месяц» — «mois», а «год» — «an».
Также обратите внимание, что в европейских странах в качестве разделителя списка обычно используется точка с запятой, а не запятая, поэтому ";" следует использовать для разделения аргументов формулы.
Вот как вы используете ТЕКСТ в Excel. Если кто-то хочет поближе познакомиться с формулами, обсуждаемыми в этом руководстве, вы можете загрузить рабочий лист Sample Excel TEXT.
Другие текстовые функции Excel
Помимо ТЕКСТА, существует несколько других функций для работы с текстовыми значениями в Excel. Некоторые из них предназначены для преобразования текста в числа и наоборот, а другие используются для манипулирования самими текстовыми строками. Ниже вы найдете примеры формул обоих типов.
- Функция Excel ВЛЕВО
- Функция Excel ВПРАВО
- Функция Excel MID
- Как преобразовать числа в текст
- Как преобразовать дату в текст
- Как убрать лишние пробелы в ячейках (функция TRIM)
- Как заменить один или несколько символов в текстовой строке (функции REPLACE и SUBSTITUTE)
- Как объединить текстовые строки в Excel (функция СЦЕПИТЬ)
- Как найти позицию определенного символа в строке (функции НАЙТИ и ПОИСК)
- Как изменить регистр символов в Excel (функции ПРОПИСНАЯ, НИЖНЯЯ и ПРОПИСНАЯ)
Как форматировать текст в Excel
Быстрый способ изменить внешний вид электронной таблицы — изменить шрифт текста.
Шрифт — это набор букв, цифр и знаков пунктуации, созданный для общего внешнего вида. Шрифт будет иметь вариации размера и стиля, например полужирный и курсив.
Изменение шрифта
Изменение шрифта — это быстрый и простой способ улучшить внешний вид электронной таблицы.
- Выберите ячейки, которые вы хотите отформатировать.
- Щелкните стрелку списка Шрифт на вкладке Главная.
Когда в ячейке выделен текст, можно также щелкнуть стрелку списка шрифтов на мини-панели инструментов.
- Выберите шрифт, который хотите использовать.
Применить полужирный, курсив или подчеркнутый
Помимо изменения типа шрифта, вы можете усилить свой проект, используя другие функции в группе «Шрифт», такие как полужирный, курсив или подчеркнутый.
- Выберите текст, который хотите отформатировать.
- Нажмите кнопку Жирный , Курсив или Подчеркнутый на вкладке Главная.
- Чтобы выделить жирным шрифтом, нажмите Ctrl + B .
- Чтобы выделить курсивом, нажмите Ctrl + I .
- Чтобы подчеркнуть, нажмите Ctrl + U .
Нажмите кнопку запуска диалогового окна в группе «Шрифт», чтобы просмотреть дополнительные параметры форматирования шрифта. В диалоговом окне вы можете изменить стиль подчеркивания и добавить эффекты.
Изменить размер шрифта
Изменение размера шрифта может помочь различать заголовки, заголовки и основной текст.
- Выберите ячейки, которые вы хотите отформатировать.
- Щелкните стрелку списка Размер шрифта .
Размер шрифта измеряется в пунктах (pt.), что составляет 1/72 дюйма. Чем больше количество точек, тем крупнее шрифт.
- Выберите нужный размер шрифта.
Нажмите Увеличить размер шрифта или Уменьшить размер шрифта Кнопки на вкладке «Главная» для настройки размера шрифта.
