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

Форматирование чисел в виде текста

Если вы хотите, чтобы в Excel числа определенных типов воспринимались как текст, используйте вместо числового формата текстовый. Например, при использовании номеров кредитных карт или других числового кода, содержащих не менее 16 цифр, необходимо использовать текстовый формат. Это происходит потому Excel что точность не может быть больше 15 цифр, и она округлит все числа после 15-й цифры до нуля, что, вероятно, вас не захотнет.

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

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

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

Примечание: Если вы не видите параметр Текст, прокрутите список с помощью ручений.

• Чтобы использовать десятичные знаки в числах, хранящихся как текст, возможно, придется вводить эти числа с десятичными разделителями.
• При вводе числа, начинающегося с нуля (например, кода продукта), этот ноль по умолчанию удаляется. Если требуется сохранить ноль, можно создать пользовательский числовой формат, который не позволит приложению Excel удалять начальные нули в числах. Например, при вводе десятизначного кода продукта Excel по умолчанию изменяет число 0784367998 на 784367998. В данном случае можно создать пользовательский числовой формат с кодом 0000000000, чтобы в Excel отображались все десять знаков кода продукта, включая начальный ноль. Дополнительные сведения об этой проблеме см. в статьях Создание и удаление пользовательских числовых форматов и Сохранение начальных нулей в числовых кодах.
• Иногда числа могут быть отформатированы и сохранены в ячейках как текст, что впоследствии может привести к проблемам при вычислениях или беспорядку при сортировке. Это иногда случается при импорте или копировании чисел из базы данных или другого источника данных. В такой ситуации требуется обратное преобразование чисел, сохраненных в виде текста, в числовой формат. Дополнительные сведения см. в статье Преобразование чисел из текстового формата в числовой.
• Вы также можете использовать функцию ТЕКСТ для преобразования числа в текст с определенным числовым форматом. Примеры использования этого метода см. в статье Сохранение начальных нулей в числовых кодах. Сведения об использовании функции ТЕКСТ см. в документе Функция ТЕКСТ.

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

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

Чтобы воспользоваться данной командой выделите ячейки с числами, перейдите во вкладку «ЁXCEL» Главного меню, нажмите кнопку «Ячейки» и выберите команду «Преобразовать число в текст»:

Все числа в выделенных ячейках будут преобразованы в числа, написанные как текст.

Для отмены операции нажмите кнопку отмены:

Преобразуйте числа, сохранённые как текст, в настоящие числа или наоборот

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

Инструмент «Преобразовать текст в число» поможет исправить ситуацию в массиве данных за секунды:

Преобразовать числа, сохранённые как текст, в числа
Преобразовать числа в текстовый формат

Перед началом работы добавьте «Преобразовать текст в число» в Excel

«Преобразовать текст в число» – это один из 20+ инструментов в составе надстройки XLTools для Excel. Работает в Excel 2019, 2016, 2013, 2010, десктоп Office 365.

Скачать XLTools для Excel
– пробный период дает 14 дней полного доступа ко всем инструментам.

Как преобразовать числа, сохранённые как текст, в настоящие числа

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

Выберите диапазон, в котором нужно преобразовать данные.

Нажмите кнопку Текст в число на панели XLTools Вы выпадающем списке выберите Текст в число Готово, каждой ячейке назначен числовой формат. Обратите внимание, что из ячеек пропадёт индикатор ошибки.

Как преобразовать числа в текстовый формат

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

Выберите диапазон, в котором нужно преобразовать данные.

Нажмите кнопку Текст в число на панели XLTools Вы выпадающем списке выберите Число в текст Готово, каждой ячейке назначен текстовый формат. Обратите внимание, что в верхнем левом углу ячеек появится индикатор, что числовое значение на самом деле сохранено как текст.

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

Excel для Microsoft 365 Excel для Microsoft 365 для Mac Excel для Интернета Excel 2021 Excel 2021 для Mac Excel 2019 Excel 2019 для Mac Excel 2016 Excel 2016 для Mac Excel 2013 Excel 2010 Excel 2007 Excel для Mac 2011 Excel Starter 2010 Еще. Меньше

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

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

Технические сведения

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

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

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

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

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

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

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

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

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

=ТЕКСТ(1234,567;«# ##0,00 ₽»)

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

=ТЕКСТ(1234;«0000000»)

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

=ТЕКСТ(123456;«##0° 00′ 00»»)

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

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

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

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

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

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

1. Нажмите клавиши CTRL+1 (+1 на компьютере Mac), чтобы открыть диалоговое окно Формат ячеек .
2. На вкладке Число выберите нужный формат.
3. Выберите параметр Пользовательский .
4. Нужный код формата будет показан в поле Тип. В этом случае выделите всё содержимое поля Тип, кроме точки с запятой (;) и символа @. В примере ниже выделен и скопирован только код ДД.ММ.ГГГГ.
5. Нажмите клавиши CTRL+C , чтобы скопировать код форматирования, а затем нажмите кнопку Отмена , чтобы закрыть диалоговое окно Формат ячеек .
6. Теперь осталось нажать клавиши CTRL+V, чтобы вставить код формата в функцию ТЕКСТ. Пример: =ТЕКСТ(B2;»ДД.ММ.ГГГГ«). Убедитесь, что код формата вставляется в кавычки («код форматирования»), в противном случае Excel выдаст сообщение об ошибке.

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

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

Выбор числового формата

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

Если вы предоставляете общий доступ к файлам и отчетам 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 в «сто двадцать три»?

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

Можно ли изменить регистр текста?

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

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

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

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

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

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

Даты на разных языках

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

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