Как преобразовать в текст в excel

Обновлено: 04.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 цифр, если выбран формат Числовой. Если вы вводите длинные цифровые строки, но не хотите, чтобы они отображались в таком виде, то сначала примените к соответствующим ячейкам формат Текстовый.

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

Для преобразования текста в таблицу или наоборот щелкните знак абзаца Показать или скрытьна вкладке Главная, чтобы увидеть, каким образом текст расположен в документе.

Преобразование текста в таблицу

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

Примечание: Если в тексте есть запятые, в качестве разделителей используйте знаки табуляции.

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

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

Выделите текст, который вы хотите преобразовать, а затем щелкните Вставка > Таблица > Преобразовать в таблицу.

В окне Текст в таблицу задайте параметры.

В разделе Размер таблицы убедитесь в том, что указанные числа соответствуют требуемому количеству столбцов и строк.

В разделе Автоподбор ширины столбцов выберите вид таблицы. Word автоматически подбирает ширину столбцов таблицы. Если вам нужны столбцы другой ширины, выполните одно из перечисленных ниже действий.

Задать ширину всех столбцов

В поле Фиксированная ширина столбца введите или выберите необходимое значение.

Изменить размер столбцов в соответствии с шириной текста в каждом столбце

Автоподбор по содержимому

Автоматически менять размер таблицы при изменении ширины доступного пространства (например, режим веб-документа или альбомная ориентация)

Автоподбор по ширине окна

В разделе Разделитель текста выберите использованный в тексте знак разделителя.

Преобразование таблицы в текст

Вы выберите строки или таблицу, которые нужно преобразовать в текст.

На вкладке Макет в разделе Данные нажмите кнопку Преобразовать в текст.

В окне Таблицу в текст в разделе Разделитель выберите знак разделения, который будет использоваться вместо границ столбцов. Строки будут разделены знаками абзаца.

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

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

Зачем преобразовывать числа в текст в Excel?

При работе с числами в Excel лучше всего использовать их только как числа. Но в некоторых случаях наличие номера может стать проблемой.

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

Сохранение ведущих нулей

Например, если вы введете 001 в ячейку в Excel, вы заметите, что Excel автоматически удаляет ведущие нули (поскольку считает, что они не нужны).

Таким образом, вы получите именно то, что вводите.

Ввод больших числовых значений

Знаете ли вы, что в Excel можно ввести только числовое значение, состоящее из 15 цифр? Если вы введете 16-значное число, оно изменит 16-ю цифру на 0.

Поэтому, если вы работаете с SSN, номерами счетов или любым другим типом больших чисел, существует вероятность, что ваши входные данные автоматически изменяются Excel.

И что еще хуже, вы не получаете никаких подсказок или ошибок. Он просто меняет цифры на 0 после 15-й цифры.

Опять же, об этом позаботятся, если преобразовать число в текст.

Преобразование чисел в даты

Это порадовало многих (в том числе и меня).

Попробуйте ввести 01-01 в Excel, и он автоматически изменит его на дату (01 января текущего года).

Поэтому, если вы введете что-либо, что является допустимым форматом даты в Excel, оно будет преобразовано в дату.

Многие люди обращаются ко мне по этому поводу, поскольку они хотят вводить оценки в Excel в этом формате, но в конечном итоге разочаровываются, когда вместо этого видят даты. Опять же, изменение формата ячейки с числа на текст поможет сохранить оценки как есть.

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

Преобразование чисел в текст в Excel

В этом разделе я расскажу о четырех различных способах преобразования чисел в текст в Excel.

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

Добавление апострофа

Если вы вручную вводите данные в Excel и не хотите, чтобы ваши числа автоматически меняли формат, вот простой трюк:

Поэтому, если вы хотите ввести 001, введите «001» (где перед числом стоит апостроф).

И не волнуйтесь, в ячейке не видно апострофа. Вы увидите только номер.

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

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

Добавление апострофа изменяет выравнивание ячеек влево

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

Точно так же, если вы хотите ввести 01-01, добавление апострофа гарантирует, что он не изменится на дату.

Хотя этот метод работает во всех случаях, он полезен только в том случае, если вы вручную вводите несколько чисел в Excel. Если вы вводите много данных в определенный диапазон строк / столбцов, используйте следующий метод.

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

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

Вы можете продолжить ввод данных, как обычно, и Excel позаботится о том, чтобы ваши числа не изменились.

Вот как это сделать:

Вышеупомянутые шаги изменили бы форматирование ячеек по умолчанию с общего на текстовое.

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

Это означает, что Excel не будет автоматически изменять вводимый вами текст (например, усекать начальные нули или преобразовывать введенные числа в даты).

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

Но помните, что если вы уже ввели числа, которые были изменены в Excel (например, удалили ведущие нули или заменили текст датами), это не вернется. Вам придется снова ввести эти данные.

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

Использование функции TEXT

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

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

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

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

В приведенной выше формуле я использовал «00000» в качестве второго аргумента, который сообщает формуле желаемый формат вывода. В этом примере 00000 будет означать, что мне нужно, чтобы все числа были как минимум пятизначными.

Использование этого метода может быть полезно, если у вас уже есть данные в Excel и вы хотите отформатировать их определенным образом.

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

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

Предположим, у вас есть набор данных, показанный ниже, и вы хотите преобразовать все числа в столбце A в текст.

Ниже приведены шаги для этого:

Щелкните значок «Текст в столбцы» на ленте. Это откроет мастер текста в столбцы, это откроет мастер текста в столбцы

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

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

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

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

А если у вас уже есть числа в Excel и вы хотите преобразовать их в текст, вы можете использовать метод формулы TEXT или метод текста в столбцы, который я рассмотрел в этом руководстве.

Возможно, существует несколько способов преобразования чисел в текстовый формат на листе Excel, но в этой статье я расскажу о некоторых функциях для решения этой задачи.

Преобразование чисел в текст с функцией ТЕКСТ

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

value : число, которое вы хотите преобразовать.
format_text : формат, который вы хотите применить.

1. Введите следующую формулу в пустую ячейку:

2. А затем скопируйте эту формулу в другие ячейки, которые вы хотите использовать, и все числа будут преобразованы в текстовый формат, см. Снимок экрана:

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

=TEXT(A2,"000000") (Add leading zeros to make the numbers as same text length)

Преобразование чисел в текст с помощью функции FIXED

Функция FIXED в Excel также может помочь вам решить эту задачу, она преобразует числа в текст, округленный до определенного количества десятичных знаков. Общий синтаксис:

number : число, которое вы хотите отформатировать как текст.
decimal : количество десятичных знаков для данного числа.
no_commas : указывает, если возвращаемый текст с запятыми, если используется ИСТИНА, запятые не включаются в результат, если ЛОЖЬ, запятые включаются в результат.

1. Пожалуйста, примените приведенную ниже формулу в пустую ячейку:

2. Затем перетащите дескриптор заполнения вниз к ячейкам, к которым вы хотите применить эту формулу, все числа были преобразованы в текстовый формат с округлением до определенного количества десятичных знаков по мере необходимости, см. Снимок экрана:

Внимание: Если вам не нужен разделитель групп запятых между числами, используйте следующую формулу:

Используемые относительные функции:

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

FIXED :
Функция FIXED возвращает число, отформатированное как десятичные дроби и представленное в виде текста.

Другие статьи:

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

Ячейка подсчета ключевых слов содержит на основе списка
Если вы хотите подсчитать количество ключевых слов в ячейке на основе списка ячеек, комбинация функций СУММПРОИЗВ, ЕЧИСЛО и ПОИСК может помочь вам решить эту проблему в Excel.

Лучшие инструменты для работы в офисе

Kutools for Excel - поможет вам выделиться из толпы

Хотите быстро и безупречно выполнять свою повседневную работу? Kutools for Excel предлагает мощные расширенные функции 300 (объединение книг, сумма по цвету, разделение содержимого ячеек, дата преобразования и так далее . ) и экономия 80% времени для вас.

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