Skip to content
Menu
PROFEXCEL.RU - Справочник EXCEL
PROFEXCEL.RU - Справочник EXCEL

Excel функция текст в число

Posted on 31.07.2021

Excel функция текст в число

Функция ТЕКСТ для преобразования числа в особый формат Excel

Функция ТЕКСТ предназначена для преобразования числового значения в текстовое с присвоением формата. Например, если ячейке с числовым значением присвоить денежный формат ,00 ₽, то для Excel данное значение остается все еще числом. Но если применить функцию =ТЕКСТ(;"0,00\ [$₽]"), тогда для Excel это будет уже текстовая строка со значением «,00 ₽». И с ней уже нельзя выполнять математических операций.

Данная функция приносит пользу в тех случаях, когда необходимо показать числа в определенном формате или комбинировать числа и текст.

Примеры использования функции ТЕКСТ в Excel

Для функции ТЕКСТ следует указать 2 обязательных для заполнения аргументы:

  1. Значение – числовое значение, ссылка на числовое значение, формула, результат которой число.
  2. Формат – числовой формат из списка числовые форматы (CTRL+1).

Рассмотрим на примере работу функции в EXCEL. На складе имеется перечень товаров в количественном и суммовом выражении, таблица представлена ниже.

Задача: Необходимо в столбе F сформировать номенклатуру ведомости с наименованием, ценой и валютой. Цены должны быть разбиты на разряды по тысячам для лучшей читабельности (например, 28 ).

Воспользуемся функцией ТЕКСТ. Щелкнем в ячейку G3 и нажмем мастер функций нажав на кнопку fx или комбинацию клавиш SHIFT+F3. В разделе категория выберем текстовые и найдем необходимую функцию.

В окне аргументов зададим следующие значения:

Нажмем Ок и получим результат:

Все сумы приобрели денежный формат и стали удобочитаемыми в тексте номенклатуры вместе с наименованием товара. Стоит обратить внимание на то что изменение на денежный формат чисел в столбце E (Сумма) не позволило бы решить данную задачу так как Excel все равно воспринимает значения в денежном формате ячеек как числа:

Вот зачем нужна функция ТЕКСТ и ее весьма сложно заменить другими решениями.



Пример как сделать два значения в одной ячейке Excel

Экономисту организации было поручено в плане на будущий год преобразовать формат сумм расходов в тысячи рублей. Кроме того, необходимо проставить пометки долей в процентах от общей сумы.

Первоначальная таблица с данными представлена ниже:

Задача: необходимо изменить внешний вид плана закупок на более информативный удобный для визуального анализа.

Перейдите курсором клавиатуры в ячейку D2 и введите следующую на первый взгляд сложную формулу:

Заполните этой формулой весь столбец скопировав ее в каждую соответственную ячейку.

В результате в я столбце D (план закупок) получатся текстовые значение с наглядным видом сумм и в скобках указаны доли (какая часть плана припадает на закупку одной позиции) для каждой суммы.

Благодаря такой формуле с функцией ТЕКСТ мы в одной и той же ячейке одновременно вводим сразу 2 значения: суммы в рублях и доли в процентах. Да еще и в двух соответствующих форматах данных.

Определение дня недели по дате в Excel

Торговому представителю необходимо посмотреть какие товары в какие дни недели он отвозил. Таблица перевозок представлена ниже:

Задача: необходимо дату преобразовать в день недели. Воспользуемся функцией ТЕКСТ.

Поставим курсор в ячейку D2. Введем знак равенства «=», а после него название функции ТЕКСТ. Далее заполняем аргументы:

В результате в столбце D отобразится соответствующий день недели для каждой даты.

Читайте также: Как перевести сумму или число прописью в Excel.

Таким образом, рассмотрев функцию ТЕКСТ можно сделать вывод, о том, что она помогает изменить представление числа, применив к нему форматирование с кодами форматов. Это полезно в ситуации, когда нужно отобразить числа в удобочитаемом виде либо объединить их с текстом или символами.

Источник: profexcel.ru

Как отобразить текст и число в одной ячейке

Для того чтобы в одной ячейке совместить как текст так и значение можно использовать следующие способы:

  • Конкатенация;
  • Функция СЦЕПИТЬ;
  • Функция ТЕКСТ;
  • Пользовательский формат.

Разберем эти способы и рассмотрим плюсы и минусы каждого из них.

Использование конкатенации

Один из самых простых способов реализовать сочетание текста и значения - использовать конкатенацию (символ &).

Допустим ячейка A1 содержит итоговое значение ,45, тогда в любой другой ячейке можно записать формулу
="Итого: "&A1

В итоге результатом будет следующее содержание ячейки Итого: ,45.

Это простое решение, однако имеет много минусов. 

  1. Результатом формулы будет текстовое значение, которое нельзя будет использовать при дальнейших вычислениях.
  2.  Значение ячейки A1 будет выводится в общем формате, без возможности всякого форматирования. В следствие чего этот метод не всегда применим.

Применение функции СЦЕПИТЬ

Аналогичное простое решение, но с теми же недостатками  - использование функции СЦЕПИТЬ. Применяется она так: =СЦЕПИТЬ("Итого: ";A1). Результаты ее использования аналогичные:

Применение функции ТЕКСТ

Функция ТЕКСТ позволяет не только объединить текст и значение, но еще и отформатировать значение в нужном формате. Если мы применим следующую формулу =ТЕКСТ(A1;"""Итого: ""##0"), то мы получим такой результат Итого: .

В качестве второго аргумента функция ТЕКСТ принимает строку с числовым форматом. Более подробно о числовых форматах вы можете прочитать в статье Применение пользовательских форматов.

Единственный минус этого способа в том, что полученные значения также являются текстовыми и с ними нельзя проводить дальнейшие вычисления.

Использование пользовательского формата

Не такой простой способ как предыдущие, но наиболее функциональный. Заключается в применении к итоговой ячейки пользовательского числового формата. Чтобы добавить текст "Итого" к ячейке A1 необходимы следующие действия:

  1.  Выберите ячейку A1.
  2. Откройте диалоговое окно Формат ячейки.
  3. В поле Тип укажите нужный формат. В нашем случае "Итого: "# ##0.

В результате ячейка A1 будет содержать Итого: .

Большой плюс данного способа заключается в том, что вы можете использовать в дальнейших вычислениях ячейку A1 так же как и число, но при этом отображаться она будет в нужном вам виде. 

Плюсы и минусы методов

В таблице далее сведены плюсы и минусы. В зависимости от ситуации можно пользоваться тем или иным способом обращая на особенности каждого.

Источник: profexcel.ru

Функция ТЕКСТ() в EXCEL

history 28 мая г.

Функция ТЕКСТ( ) , английская версия TEXT() , преобразует число в текст и позволяет задать формат отображения с помощью специальных строк форматирования, например, формула =ТЕКСТ(;"0,00 р.") вернет текстовую строку ,00 р., а формула =ТЕКСТ(1;"#%") вернет текст (не число!) %. Эта функция полезна, если числа требуется отобразить в более удобном формате или если требуется объединить числа с текстом или символами.


Синтаксис

ТЕКСТ(значение; формат)

  • Значение. Численное значение или ссылка на ячейку, содержащую численное значение.
  • Формат. Текстовая строка определенного вида (например, "profexcel.ru" для вывода числа в формате даты или "# ##0,00" для вывода в числовом формате с разделителями разрядов и двумя знаками после запятой. Правила форматирования см. ниже.

Форматов для отображения чисел в MS EXCEL много (например, см. здесь ), также имеются форматы для отображения дат (например, см. здесь ). Также приведено много форматов ]]> в статье к функции ТЕКСТ() на сайте Microsoft ]]> .

Функция ТЕКСТ() преобразует число в форматированный текст и результат больше не может быть использован в вычислениях в качестве числа. Чтобы отформатировать число, но при этом оставить его числом (с которым можно выполнять арифметические действия), щелкните ячейку правой кнопкой мыши, выберите команду Формат ячеек и в диалоговом окне Формат ячеек на вкладке Число настройте нужные параметры форматирования (см. здесь ).

Одной из самых полезных свойств функции ТЕКСТ() является возможность отображения в текстовой строке чисел и дат в нужном формате (см. подробнее об отображении чисел , дат и времени ). В файле примера приведен наглядный пример: с форматированием и без форматирования.

В файле примера приведено также множество других форматов.

© Copyright - profexcel.ru All Rights Reserved

Источник: profexcel.ru

Функция ТЕКСТ

С помощью функции ТЕКСТ можно изменить представление числа, применив к нему форматирование с кодами форматов. Это полезно в ситуации, когда нужно отобразить числа в удобочитаемом виде либо объединить их с текстом или символами.

Примечание: Функция ТЕКСТ преобразует числа в текст, что может затруднить их использование в дальнейших вычислениях. Рекомендуем сохранить исходное значение в одной ячейке, а функцию ТЕКСТ использовать в другой. Затем, если потребуется создать другие формулы, всегда ссылайтесь на исходное значение, а не на результат функции ТЕКСТ.

Синтаксис

ТЕКСТ(значение; формат)

Аргументы функции ТЕКСТ описаны ниже.

Имя аргумента

Описание

значение

Числовое значение, которое нужно преобразовать в текст.

формат

Текстовая строка, определяющая формат, который требуется применить к указанному значению.

Общие сведения

Самая простая функция ТЕКСТ означает следующее:

  • =ТЕКСТ(значение, которое нужно отформатировать; "код формата, который требуется применить")

Ниже приведены популярные примеры, которые вы можете скопировать прямо в Excel, чтобы поэкспериментировать самостоятельно. Обратите внимание: коды форматов заключены в кавычки.

Формула

Описание

=ТЕКСТ(,;"# ##0,00 ₽")

Денежный формат с разделителем групп разрядов и двумя разрядами дробной части, например: 1 ,57 ₽. Обратите внимание: Excel округляет значение до двух разрядов дробной части.

=ТЕКСТ(СЕГОДНЯ();"profexcel.ru")

Сегодняшняя дата в формате ДД/ММ/ГГ, например:

=ТЕКСТ(СЕГОДНЯ();"ДДДД")

Сегодняшний день недели, например: понедельник

=ТЕКСТ(ТДАТА();"ЧЧ:ММ")

Текущее время, например:

=ТЕКСТ(0,;"0,0 %")

Процентный формат, например: 28,5 %

=ТЕКСТ(4,34; "# ?/?")

Дробный формат, например: 4 1/3

=СЖПРОБЕЛЫ(ТЕКСТ(0,34;"# ?/?"))

Дробный формат, например: 1/3 Обратите внимание: функция СЖПРОБЕЛЫ используется для удаления начального пробела перед дробной частью.

=ТЕКСТ(;"0,00E+00")

Экспоненциальное представление, например: 1,22E+07

=ТЕКСТ(;"[<=]###-####;(###) ###-####")

Дополнительный формат (номер телефона), например: ()

=ТЕКСТ(;"")

Добавление нулей в начале, например:

=ТЕКСТ(;"##0° 00' 00''")

Пользовательский формат (широта или долгота), например: 12° 34' 56''

Примечание: Функцию ТЕКСТ можно использовать для изменения форматирования, но это не единственный способ. Чтобы изменить форматирование без формулы, нажмите клавиши CTRL+1 (на компьютере Mac — +1), а затем в диалоговом окне Формат ячеек на вкладке Число выберите нужный формат.

Скачивание образцов

Предлагаем скачать книгу, в которой содержатся все примеры применения функции ТЕКСТ из этой статьи и несколько других. Вы можете воспользоваться ими или создать собственные коды форматов для функции ТЕКСТ.

Скачать примеры применения функции ТЕКСТ

Другие доступные коды форматов

Просмотреть другие доступные коды форматов можно в диалоговом окне Формат ячеек.

  1. Нажмите клавиши CTRL+1 (на компьютере Mac — +1), чтобы открыть диалоговое окно Формат ячеек.

  2. На вкладке Число выберите нужный формат.

  3. Выберите пункт (все форматы).

  4. Нужный код формата будет показан в поле Тип. В этом случае выделите всё содержимое поля Тип, кроме точки с запятой (;) и символа @. В примере ниже выделен и скопирован только код profexcel.ru

  5. Нажмите клавиши CTRL+C, чтобы скопировать код формата, а затем — кнопку Отмена, чтобы закрыть диалоговое окно Формат ячеек.

  6. Теперь осталось нажать клавиши CTRL+V, чтобы вставить код формата в функцию ТЕКСТ. Пример: =ТЕКСТ(B2;"profexcel.ru"). Обязательно заключите скопированный код формата в кавычки ("код формата"), иначе в Excel появится сообщение об ошибке.

Коды форматов по категориям

В примерах ниже показано, как применить различные числовые форматы к значениям следующим способом: открыть диалоговое окно Формат ячеек, выбрать пункт (все форматы) и скопировать нужный код формата в формулу с функцией ТЕКСТ.

  • Выбор числового формата
  • Нули в начале
  • Разделитель групп разрядов.
  • Числовые, денежные и финансовые форматы
  • Даты
  • Значения времени
  • Проценты
  • Дроби
  • Экспоненциальное представление
  • Дополнительные форматы

Почему программа Excel удаляет нули в начале?

Excel воспринимает последовательность цифр, введенную в ячейку, как число, а не как цифровой код, например артикул или номер SKU. Чтобы сохранить нули в начале последовательностей цифр, перед вставкой или вводом значений примените к соответствующему диапазону ячеек текстовый формат. Выделите столбец или диапазон, в который нужно поместить значения, нажмите клавиши CTRL+1, чтобы открыть диалоговое окно Формат ячеек, и выберите на вкладке Число пункт Текстовый. Теперь программа Excel не будет удалять нули в начале.

Если вы уже ввели данные и Excel удалил начальные нули, вы можете снова добавить их с помощью функции ТЕКСТ. Создайте ссылку на верхнюю ячейку со значениями и используйте формат =ТЕКСТ(значение;""), где число нулей представляет нужное количество символов. Затем скопируйте функцию и примените ее к остальной части диапазона.

Если по какой-либо причине потребуется преобразовать текстовые значения обратно в числа, можно умножить их на 1 (например: =D4*1) или воспользоваться двойным унарным оператором (--), например: =--D4.

В Excel группы разрядов разделяются пробелом, если код формата содержит пробел, окруженный знаками номера (#) или нулями. Например, если используется код формата "# ###", число отображается как 12  

Пробел после заполнителя цифры задает деление числа на Например, если используется код формата "# ###,0 ", число отображается в Excel как 12 ,0.

Примечания:

  • Разделитель групп разрядов зависит от региональных параметров. Для России это пробел, но в других странах и регионах может использоваться запятая или точка.

  • Разделитель групп разрядов можно применять в числовых, денежных и финансовых форматах.

Ниже показаны примеры стандартных числовых (только с разделителем групп разрядов и десятичными знаками), денежных и финансовых форматов. В денежном формате можно добавить нужное обозначение денежной единицы, и значения будут выровнены по нему. В финансовом формате символ рубля располагается в ячейке справа от значения (если выбрать обозначение доллара США, то эти символы будут выровнены по левому краю ячеек, а значения — по правому). Обратите внимание на разницу между кодами денежных и финансовых форматов: в финансовых форматах для отделения символа денежной единицы от значения используется звездочка (*).

Чтобы получить код формата для определенной денежной единицы, сначала нажмите клавиши CTRL+1 (на компьютере Mac — +1) и выберите нужный формат, а затем в раскрывающемся списке Обозначение выберите символ.

После этого в разделе Числовые форматы слева выберите пункт (все форматы) и скопируйте код формата вместе с обозначением денежной единицы.

Примечание: Функция ТЕКСТ не поддерживает форматирование с помощью цвета. Если скопировать в диалоговом окне "Формат ячеек" код формата, в котором используется цвет, например "# ##0,00 ₽;[Красный]# ##0,00 ₽", то функция ТЕКСТ воспримет его, но цвет отображаться не будет.

Способ отображения дат можно изменять, используя сочетания символов "Д" (для дня), "М" (для месяца) и "Г" (для года).

В функции ТЕКСТ коды форматов используются без учета регистра, поэтому допустимы символы "М" и "м", "Д" и "д", "Г" и "г".

Способ отображения времени можно изменить с помощью сочетаний символов "Ч" (для часов), "М" (для минут) и "С" (для секунд). Кроме того, для представления времени в часовом формате можно использовать символы "AM/PM".

Если не указывать символы "AM/PM", время будет отображаться в часовом формате.

В функции ТЕКСТ коды форматов используются без учета регистра, поэтому допустимы символы "Ч" и "ч", "М" и "м", "С" и "с", "AM/PM" и "am/pm".

Для отображения десятичных значений можно использовать процентные (%) форматы.

Десятичные числа можно отображать в виде дробей, используя коды форматов вида "?/?".

Экспоненциальное представление — это способ отображения значения в виде десятичного числа от 1 до 10, умноженного на 10 в некоторой степени. Этот формат часто используется для краткого отображения больших чисел.

В Excel доступны четыре дополнительных формата:

  • "Почтовый индекс" ("");

  • "Индекс + 4" ("");

  • "Номер телефона" ("[<=]###-####;(###) ###-####");

  • "Табельный номер" ("").

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

Типичный сценарий

Функция ТЕКСТ редко используется сама по себе, а чаще применяется в сочетании с чем-то еще. Предположим, что вы хотите объединить текст и числовое значение, например, чтобы получить строку "Отчет напечатан " или "Еженедельный доход: 66 ,72 ₽". Такие строки можно ввести вручную, но суть в том, что Excel может сделать это за вас. К сожалению, при объединении текста и форматированных чисел, например дат, значений времени, денежных сумм и т. п., Excel убирает форматирование, так как неизвестно, в каком виде нужно их отобразить. Здесь пригодится функция ТЕКСТ, ведь с ее помощью можно принудительно отформатировать числа, задав нужный код формата, например "profexcel.ru" для дат.

В примере ниже показано, что происходит, если попытаться объединить текст и число, не применяя функцию ТЕКСТ. Мы используем амперсанд (&) для сцепления текстовой строки, пробела (" ") и значения: =A2&" "&B2.

Вы видите, что значение даты, взятое из ячейки B2, не отформатировано. В следующем примере показано, как применить нужное форматирование с помощью функции ТЕКСТ.

Вот обновленная формула:

  • ячейка C2:=A2&" "&ТЕКСТ(B2;"profexcel.ru") — формат даты.

Вопросы и ответы

Да, вы можете использовать функции ПРОПИСН, СТРОЧН и ПРОПНАЧ. Например, формула =ПРОПИСН("привет") возвращает результат "ПРИВЕТ".

Да, но для этого необходимо выполнить несколько действий. Сначала выделите нужные ячейки и нажмите клавиши CTRL+1, чтобы открыть диалоговое окно Формат ячеек. Затем на вкладке Выравнивание в разделе "Отображение" установите флажок Переносить по словам. После этого добавьте в функцию ТЕКСТ код ASCII СИМВОЛ(10) там, где нужен разрыв строки. Вам может потребоваться настроить ширину столбца, чтобы добиться нужного выравнивания.

В этом примере использована формула ="Сегодня: "&СИМВОЛ(10)&ТЕКСТ(СЕГОДНЯ();"profexcel.ru").

Это экспоненциальное представление числа. Excel автоматически приводит к такому виду числа длиной более 12 цифр, если к ячейкам применен формат Общий, и числа длиннее 15 цифр, если выбран формат Числовой. Если вы вводите длинные цифровые строки, но не хотите, чтобы они отображались в таком виде, то сначала примените к соответствующим ячейкам формат Текстовый.

См. также

Создание и удаление пользовательских числовых форматов

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

Функции Excel (по категориям)

Источник: profexcel.ru%D1%84%D1%83%D0%BD%D0%BA%D1%86%D0%B8%D1%8F-%D1%82%D0%B5%D0%BA%D1%81%D1%d5ac4d-7bfd-bbdc

20 Функция Excel ТЕКСТ, time: 4:20

.

Может быть интересно:

Функция СЛУЧМЕЖДУ Случайное число из заданного интервала в MS EXCEL | Функция АДРЕС
  • 1
  • 2
  • Next

Свежие записи

  • Функция РАНГ в Excel
  • Задаем область печати в Excel
  • Диапазон дат в одной ячейке в текстовом формате в MS EXCEL
  • Преобразовать дату в текст Excel
  • Отбор уникальных значений убираем повторы из списка в MS EXCEL

Рубрики

  • Дата
  • Диаграммы
  • Конвертация
  • Общие вопросы
  • Печать эксель файла
  • Поиск значения
  • Работа с текстом
  • Разные вопросы
  • Сортировка значений
  • Список значений
  • Формулы
  • Функции
©2021 PROFEXCEL.RU - Справочник EXCEL | WordPress Theme: EcoCoded