Вводим в ячейку число как текст
Часто в Excel числа необходимо записать как текст. И на оборот, введенные нами текстовые значения программа меняет на числа или даты.
На первом уроке рассмотрим, в каких случаях может возникнуть конфликт между пользователем и программой при введении данных в ячейки. Так же найдем лучшее решения для правильного введения в ячейки числа как текст.
Как преобразовать число в текст?
Вводить числа как текст в Excel приходится достаточно часто. Например:
- Номер фактуры или договора состоит из более 12-ти чисел. Допустим номер: «1234567891012» Excel округляет большие числа в такой формат «1,23457E+12».
- Некоторые номера телефонов автоматически конвертируются в дату. Номер телефона: 2012-10-17 отображается как «17.10.2012».
- Иногда нужно записать формулу обычным текстом.
Поэтому важно научиться управлять форматами ячеек.
Попробуйте заполнить табличку, так как показано на рисунке:
Как видно на примере проблема возникает при введении текста «10/12». После нажатия клавиши «Enter» получаем дату:
Необходимо заставить Excel распознавать числа как текст.
Решение 1 . В ячейке D2 введите перед текстовым значением символ апострофа «’» ( апостроф следует ввести с английской раскладки клавиатуры ). Теперь число преобразовано в текстовое значение.
Решение 2 . Задайте в ячейке D2 текстовый формат. Для этого откройте окно «Формат ячеек» CTRL+1. На вкладке «Число» выберите из списка «Числовые формат» опцию «Текстовый» и нажмите ОК. Теперь можно вводить любые числа как текст без апострофа.
Главным отличием выше описанных двух примеров является закрепление за ячейкой способа отображения ее значений с помощью присвоения формата. Если в первом решении в ячейку будут вводиться новые типы данных ( без апострофа ), то они будут отображаться по умолчанию.
Примечание. Второе решение закрепляет за ячейкой ее формат, и любые другие типы данных будут преобразованы, пока пользователь не изменит для нее другие форматы (например, «Общий» — по умолчанию).
Математические операции с текстом в Excel
Если функция ссылается на ячейку, в которой число записано текстом, то при вычислении значение этой ячейки равно «0». Но здесь нужно быть очень внимательным, так как бывают исключения. Это может приводить к ошибочным расчетам. Приводим конкретный пример.
- Заполните диапазон ячеек A1:D6 так как показано на рисунке:
- Колонку E не нужно заполнять, так как в ней отображается содержимое ячеек в колонке D. Обратите внимание в ячейках B3 и B6 числовые данные записаны как текстовые через апостроф «’».
- В колонку D введите формулы, которые отображаются в колонке E как на рисунке.
Теперь обратите внимание на результаты вычислений функций и формул в колонке D.
В ячейке D2 функция =СУММ(A2:C2) отображает правильный результат 1+100+10=111. А в ячейке D3 функция суммирования меняет значение ячейки B3 на число 0, так как там данные отображаются в текстовом формате. В результате: 1+0+10=11.
Внимание! При вычислении аналогичных значений ячеек, но уже с помощью обычной формулы =A6+B6+C6 все значения ячеек воспринимаются как числовые значения, несмотря на то, что ячейка B6 имеет текстовый формат. Формулы могут суммировать, умножать, разделять и вычитать текст, если он состоит только из символов чисел (0-9), а функции не могут.
Из этого примера возникает простой вывод. При вычислении и расчетах в Excel необходимо использовать одинаковые типы данных, не зависимо от формул и функций. Это лучше чем конвертировать текст в число и позволит избежать много ошибок. Не выполняйте никаких математических операций с текстом.
Как в Excel преобразовать число в текст
В этом учебном материале по Excel мы рассмотрим примеры того, как преобразовать числа в текст.
Основная формула
=ТЕКСТ( A1 ; «0» )
Описание
Для преобразования чисел в текстовые значения можно использовать функцию ТЕКСТ.
В показанном примере формула C5 имеет следующий вид:
=ТЕКСТ( B5 ; «0» )
Результатом является число 10500, отформатированное как текст «10500». Все числа в столбце D отформатированы как текст с формулами, указанными в столбце F.
Пояснение
Обычно вы хотите хранить числовые значения в Excel, потому что их можно использовать в формулах, выполняющих числовые вычисления. Однако бывают ситуации, когда преобразование чисел в текст имеет смысл. Один из примеров — когда вы хотите объединить (присоединить) форматированное число к тексту. Например, «Продажи в прошлом году увеличились более чем на 15%», где число 0,15 было отформатировано с символом процента. Без функции ТЕКСТ форматирование чисел будет удалено.
Другой пример — когда вы хотите выполнить поиск чисел с использованием подстановочных знаков, что невозможно сделать с числовыми значениями.
Преобразование с форматированием
Функцию ТЕКСТ можно использовать для преобразования чисел в текст с использованием заданного числового формата. В показанном примере функция ТЕКСТ используется для преобразования каждого числа в столбце B в текстовое значение с использованием формулы и числа, показанных в столбце F. Функция ТЕКСТ принимает число в качестве аргумента значение , формат числа для использования в качестве аргумента код формата . Числовой формат может включать коды дат, времени, чисел, валюты, процентов и т.д. А также настраиваемые числовые форматы. Обратите внимание, что вывод функции ТЕКСТ представляет собой текстовое значение, которое по умолчанию в Excel выровнено по левому краю.
Преобразование без форматирования
Чтобы преобразовать число в формате A1 в текстовое значение без форматирования числа, вы можете объединить число в пустую строку («») следующим образом:
Преобразуйте числа, сохранённые как текст, в настоящие числа или наоборот
Вы не можете быть совершенно уверены, что результаты вычислений верны, если вы не уверены, что ячейкам Excel присвоен правильный формат – и числовые значения распознаются как числа. В некоторых ячейках вы можете обнаружить ошибку, что это число на самом деле сохранено как текст. Значит, Excel будет игнорировать такие числа в расчётах. Или же наоборот, иногда бывает нужно, чтобы Excel воспринимал числа как текст.
Инструмент «Преобразовать текст в число» поможет исправить ситуацию в массиве данных за секунды:
Преобразовать числа, сохранённые как текст, в числа
Преобразовать числа в текстовый формат
Перед началом работы добавьте «Преобразовать текст в число» в Excel
«Преобразовать текст в число» – это один из 20+ инструментов в составе надстройки XLTools для Excel. Работает в Excel 2019, 2016, 2013, 2010, десктоп Office 365.
Скачать XLTools для Excel
– пробный период дает 14 дней полного доступа ко всем инструментам.
Как преобразовать числа, сохранённые как текст, в настоящие числа
Если в ячейке стоит числовое значение, и при этом в верхнем левом углу есть индикатор ошибки – значит, что это число на самом деле сохранено как текст. Поэтому Excel будет обрабатывать это значение как текст – и будет игнорировать его в расчётах. Поэтому крайне важно преобразовать подобные нераспознанные числа в полноценный числовой формат.
Выберите диапазон, в котором нужно преобразовать данные.
Нажмите кнопку Текст в число на панели XLTools Вы выпадающем списке выберите Текст в число Готово, каждой ячейке назначен числовой формат. Обратите внимание, что из ячеек пропадёт индикатор ошибки.
Как преобразовать числа в текстовый формат
Если вам нужно, чтобы числовые значения в ячейке обрабатывались как текст, вы можете добавить апостроф (одинарную кавычку) перед значением. Это по сути преобразует числовое значение в текстовый формат.
Выберите диапазон, в котором нужно преобразовать данные.
Нажмите кнопку Текст в число на панели XLTools Вы выпадающем списке выберите Число в текст Готово, каждой ячейке назначен текстовый формат. Обратите внимание, что в верхнем левом углу ячеек появится индикатор, что числовое значение на самом деле сохранено как текст.
Форматирование чисел в виде текста
Если вы хотите, чтобы в Excel числа определенных типов воспринимались как текст, используйте вместо числового формата текстовый. Например, при использовании номеров кредитных карт или других числового кода, содержащих не менее 16 цифр, необходимо использовать текстовый формат. Это происходит потому Excel что точность не может быть больше 15 цифр, и она округлит все числа после 15-й цифры до нуля, что, вероятно, вас не захотнет.
Если число имеет текстовый формат, это легко определить, поскольку число будет выровнено в ячейке по левому краю, а не по правому.
-
Выберем ячейку или диапазон, которые содержат числа, которые вы хотите отформать как текст. Выбор ячеек или диапазона.
Совет: Можно также выделить пустые ячейки, отформатировать их как текст, а затем ввести числа. Такие числа будут иметь текстовый формат.
Примечание: Если вы не видите параметр Текст, прокрутите список с помощью ручений.
- Чтобы использовать десятичные знаки в числах, хранящихся как текст, возможно, придется вводить эти числа с десятичными разделителями.
- При вводе числа, начинающегося с нуля (например, кода продукта), этот ноль по умолчанию удаляется. Если требуется сохранить ноль, можно создать пользовательский числовой формат, который не позволит приложению Excel удалять начальные нули в числах. Например, при вводе десятизначного кода продукта Excel по умолчанию изменяет число 0784367998 на 784367998. В данном случае можно создать пользовательский числовой формат с кодом 0000000000, чтобы в Excel отображались все десять знаков кода продукта, включая начальный ноль. Дополнительные сведения об этой проблеме см. в статьях Создание и удаление пользовательских числовых форматов и Сохранение начальных нулей в числовых кодах.
- Иногда числа могут быть отформатированы и сохранены в ячейках как текст, что впоследствии может привести к проблемам при вычислениях или беспорядку при сортировке. Это иногда случается при импорте или копировании чисел из базы данных или другого источника данных. В такой ситуации требуется обратное преобразование чисел, сохраненных в виде текста, в числовой формат. Дополнительные сведения см. в статье Преобразование чисел из текстового формата в числовой.
- Вы также можете использовать функцию ТЕКСТ для преобразования числа в текст с определенным числовым форматом. Примеры использования этого метода см. в статье Сохранение начальных нулей в числовых кодах. Сведения об использовании функции ТЕКСТ см. в документе Функция ТЕКСТ.