Как в эксель присвоить слову числовое значение

Обновлено: 06.07.2024

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

=ТЕКСТ(1234;"0000000")

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

Минда советует.

Если вы предоставляете общий доступ к файлам и отчетам Excel пользователям из разных стран, скорее всего, потребуется, чтобы они были на разных языках. Минда Триси (Mynda Treacy), Excel MVP, предлагает отличное решение этой задачи в своей статье Отображение дат Excel на разных языках (на английском). В ней также есть пример книги, который вы можете скачать.

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

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

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

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

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

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

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

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

"Индекс + 4" ("00000-0000");

"Табельный номер" ("000-00-0000").

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

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

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

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

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

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

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

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

Как преобразовать числа в текст, например 123 в "сто двадцать три"?

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

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

Можно ли с помощью функции ТЕКСТ добавить новую строку (разрыв строки) в ячейке, как при нажатии клавиш ALT+ВВОД?

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

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

Почему Excel преобразует введенные числа во что-то вроде "1,22E+07"?

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

Минда советует.

Обычные числа и числа-как-текст (т.е. числа, которые только выглядят как числа, а, по-сути, являются текстом) - это причина многих проблем и сложностей при работе с данными в Microsoft Excel. Одна из подобных ситуаций - использование функции ВПР (VLOOKUP) для поиска и подстановки, когда в исходных данных есть эти пресловутые числа в текстовом формате.

Рассмотрим классический пример - подстановку цен из прайс-листа в таблицу заказов по совпадению артикулов:

Давайте рассмотрим различные сценарии, с которыми мы здесь можем столкнуться и решения для каждого из них.

Примечание : Различить нормальные и текстовые числа не всегда легко. Иногда на таких ячейках появляется зелёный уголок-индикатор, иногда - нет. В этой статье, для наглядности, я буду выравнивать числа-как-текст по левому краю, а нормальные числа - по правому.

Вариант 1. Числа-как-текст в искомых значениях

Предположим для начала, что псевдочисла эпизодически встречаются у нас в искомых значениях, т.е. в таблице заказов (диапазон B4:B7) и перемешаны с нормальными числовыми артикулами, что приводит к появлению ошибок.

умножения или деления на 1
прибавления или вычитания 0
двойного знака минус перед артикулом (равносильно двойному умножению на -1)

Значение артикула от выполнения такой безобидной математической операции никак не изменится, но сам факт её выполнения заставит Excel воспринимать артикул именно как число. А значит и ВПР найдет текстовые значения без проблем:

Вариант 2. Числа-как-текст в таблице, где ищем

Может встретиться и обратная сторона той же медали, когда псевдочисла будут не в заказе, а в артикулах прайс-листа. Здесь поможет та же логика: для принудительного преобразования исходных числовых артикулов в текстовые, над ними нужно выполнить любую безобидную не математическую, а текстовую операцию.

Самым простым и компактным вариантом будет приклеивание к артикулу пустой строки:

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

Вариант 3. Пропадание начальных нулей

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

Вариант 4. Числа-как-текст вперемешку с числами в обеих таблицах

Ну, и на десерт, давайте рассмотрим самый "веселый" случай, когда псевдочисла у нас перемешаны с нормальными числами и встречаются в артикулах обеих таблиц.

Привет друзья, мой вопрос может показаться нубским, но всё же:

В общем ситуация такая.

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

Может сложно описал вопрос, вот скрин:

__________________
Помощь в написании контрольных, курсовых и дипломных работ здесь

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

Как присвоить ячейке число в матрице
Есть матрица 3 на 3, как сделать чтобы каждой ячейке (всего получается 9) присвоить свое значение.

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

Как присвоить значение текущей ячейке в VBA?
Добрый день Подскажите пожалуйста как реализовать такой механизм Private Sub.

Ладно, объясню по проще:

Необходимо : X =2 Y =4 Z =6 W =8 M =10

выбирая из списка X подставлялось значение 2 и так далее.

Извините ради бога не сохраненный выложил.

Спасибо, суть понятна.
А можно сделать чтоб в поле не отображалось значение, а отображалась именно буква.

Смотрим там же в Диспетчере имен

Или вопрос был риторическим?

упс вторую страницу не прочитал. Удалил.

Добавлено через 6 минут
просмотрел все таки добавлю формулу

. что бы при выборе выпадающего меню было написано слово, а подставлялось число.

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

Добавлено через 5 минут
Pelena помогите разобраться

В моём варианте созданы имена, совпадающие с текстовыми значениями в выпадающем списке. Значение имени - ссылка на ячейку с числом. Перечень имён можно посмотреть в Диспетчере имён

Как присвоить ячейке неопределённое значение в DBGrid?
Вопрос состоит в том как присвоить нулевое значение внешнего ключа при вставке в DBGrid? В таблице.

Как присвоить значение ячейке из другого листа?
Добрый день. Есть порядка 70 листов в одной книге (в дальнейшем планируются книги с количеством.

Как присвоить кодам в одной ячейке текстовое значение в другой?
Здравствуйте! Работаю с базами ВЭД. В одном столбце имеются числовые коды, каждая совокупность.

Собственно суть вопроса в том, можно или нет присвоить буквенному символу значение 1, для дальнейшего суммирования. В спрвке ничего не нашел или не понял. Зарание спасибо за ответ.

Ответы

И так и эдак не получается, проехали. все равно считать ручками нужно

Слушайте Gorelovo,

Знаете что вам мешает решить проблему? - Вы сами ;-)

1) Вы не можете изложить проблему так, чтобы другие ее поняли (но это не так страшно)
2) Вы делаете выводы и отметаете решения, толком не объяснив задачи и не поняв предложенных решений
3) Вы не задаете вопросов, чтобы понять суть того, что вам предложили
4) А самое главное - вы не объясняете что лично для вас означает "не получается", в чем оно выражается, и каков критерий "получается"

И согласитесь между "присвоить букве цифровое значение" и "считать непустые ячейки" - пропасть. Судя по вашим последним пояснениям, ваша сумма - не сумма а счет ;-)

На всякий случай:

если надо считать ячейки только с "я" ИЛИ только с "н", то

если надо считать ячейки только с "я" И "н", то

=СУММА(СЧЁТЕСЛИ(А1:J1;)) Si la respuesta te es de utilidad por favor marca la pregunta como respondida.

Все ответы

Можно. Меню Вставка-> Имя-> Присвоить.

Si la respuesta te es de utilidad por favor marca la pregunta como respondida.

Можно. Меню Вставка-> Имя-> Присвоить.
Si la respuesta te es de utilidad por favor marca la pregunta como respondida.

Нет там такого. Это есть в меню формулы. Но это не то. Вам надо какуюто конкретную букву или слово искать, или Вам росто надо считать колличество символов? Если какуюто конкретную букву или слово то можно при помоши COUNTIF сила в справедливости

Вам надо какуюто конкретную букву или слово искать, или Вам росто надо считать колличество символов? Если какуюто конкретную букву или слово то можно при помоши COUNTIF сила в справедливости

Мне необходимо считать символы. один символ одна еденица

Допустим, в строке состоящей из 15 ячеек заполняются только одной буквой, допустим "н" 5 ячеек, буквой "я" 3 ячейки, остальные остаются пустыми. А вот в 16 ячейке у меня должна появляться сумма всех заполненных ячеек. Попробую изобразить то что мне нужно наглядно.

|н|н|я|я| | | |н|н|н|н|я|я|я| сумм=>11 | Cогласитесь, немного сложновато считать заполненные ячейки

И так и эдак не получается, проехали. все равно считать ручками нужно

То что Вы описали в примере очень легко делается.

т.е. countif сосчитает все ячейки в которых есть хоть какойто текстовый символ

сила в справедливости

И так и эдак не получается, проехали. все равно считать ручками нужно

Слушайте Gorelovo,

Знаете что вам мешает решить проблему? - Вы сами ;-)

На всякий случай:

если надо считать ячейки только с "я" ИЛИ только с "н", то

если надо считать ячейки только с "я" И "н", то

=СУММА(СЧЁТЕСЛИ(А1:J1;)) Si la respuesta te es de utilidad por favor marca la pregunta como respondida.

И так и эдак не получается, проехали. все равно считать ручками нужно

Слушайте Gorelovo,

Знаете что вам мешает решить проблему? - Вы сами ;-)

На всякий случай:

если надо считать ячейки только с "я" ИЛИ только с "н", то

если надо считать ячейки только с "я" И "н", то

=СУММА(СЧЁТЕСЛИ(А1:J1;)) Si la respuesta te es de utilidad por favor marca la pregunta como respondida. Ура . Заработало. Спасибо большое.Я всякую надежду потерял. А тут Такое.
А вопрос как мог так и сформулировал, даже пытался изобразить ячейки. Не обессудьте. Еще раз спасибо.

Читайте также: