Вводим в ячейку число как текст
Часто в 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 числа определенных типов воспринимались как текст, используйте вместо числового формата текстовый. Например, при использовании номеров кредитных карт или других числового кода, содержащих не менее 16 цифр, необходимо использовать текстовый формат. Это происходит потому Excel что точность не может быть больше 15 цифр, и она округлит все числа после 15-й цифры до нуля, что, вероятно, вас не захотнет.
Если число имеет текстовый формат, это легко определить, поскольку число будет выровнено в ячейке по левому краю, а не по правому.
- Выберем ячейку или диапазон, которые содержат числа, которые вы хотите отформать как текст. Выбор ячеек или диапазона.
Совет: Можно также выделить пустые ячейки, отформатировать их как текст, а затем ввести числа. Такие числа будут иметь текстовый формат.
Примечание: Если вы не видите параметр Текст, прокрутите список с помощью ручений.
- Чтобы использовать десятичные знаки в числах, хранящихся как текст, возможно, придется вводить эти числа с десятичными разделителями.
- При вводе числа, начинающегося с нуля (например, кода продукта), этот ноль по умолчанию удаляется. Если требуется сохранить ноль, можно создать пользовательский числовой формат, который не позволит приложению Excel удалять начальные нули в числах. Например, при вводе десятизначного кода продукта Excel по умолчанию изменяет число 0784367998 на 784367998. В данном случае можно создать пользовательский числовой формат с кодом 0000000000, чтобы в Excel отображались все десять знаков кода продукта, включая начальный ноль. Дополнительные сведения об этой проблеме см. в статьях Создание и удаление пользовательских числовых форматов и Сохранение начальных нулей в числовых кодах.
- Иногда числа могут быть отформатированы и сохранены в ячейках как текст, что впоследствии может привести к проблемам при вычислениях или беспорядку при сортировке. Это иногда случается при импорте или копировании чисел из базы данных или другого источника данных. В такой ситуации требуется обратное преобразование чисел, сохраненных в виде текста, в числовой формат. Дополнительные сведения см. в статье Преобразование чисел из текстового формата в числовой.
- Вы также можете использовать функцию ТЕКСТ для преобразования числа в текст с определенным числовым форматом. Примеры использования этого метода см. в статье Сохранение начальных нулей в числовых кодах. Сведения об использовании функции ТЕКСТ см. в документе Функция ТЕКСТ.
Преобразуйте числа, сохранённые как текст, в настоящие числа или наоборот
Вы не можете быть совершенно уверены, что результаты вычислений верны, если вы не уверены, что ячейкам Excel присвоен правильный формат – и числовые значения распознаются как числа. В некоторых ячейках вы можете обнаружить ошибку, что это число на самом деле сохранено как текст. Значит, Excel будет игнорировать такие числа в расчётах. Или же наоборот, иногда бывает нужно, чтобы Excel воспринимал числа как текст.
Инструмент «Преобразовать текст в число» поможет исправить ситуацию в массиве данных за секунды:
Преобразовать числа, сохранённые как текст, в числа
Преобразовать числа в текстовый формат
Перед началом работы добавьте «Преобразовать текст в число» в Excel
«Преобразовать текст в число» – это один из 20+ инструментов в составе надстройки XLTools для Excel. Работает в Excel 2019, 2016, 2013, 2010, десктоп Office 365.
Скачать XLTools для Excel
– пробный период дает 14 дней полного доступа ко всем инструментам.
Как преобразовать числа, сохранённые как текст, в настоящие числа
Если в ячейке стоит числовое значение, и при этом в верхнем левом углу есть индикатор ошибки – значит, что это число на самом деле сохранено как текст. Поэтому Excel будет обрабатывать это значение как текст – и будет игнорировать его в расчётах. Поэтому крайне важно преобразовать подобные нераспознанные числа в полноценный числовой формат.
Выберите диапазон, в котором нужно преобразовать данные.
Нажмите кнопку Текст в число на панели XLTools Вы выпадающем списке выберите Текст в число Готово, каждой ячейке назначен числовой формат. Обратите внимание, что из ячеек пропадёт индикатор ошибки.
Как преобразовать числа в текстовый формат
Если вам нужно, чтобы числовые значения в ячейке обрабатывались как текст, вы можете добавить апостроф (одинарную кавычку) перед значением. Это по сути преобразует числовое значение в текстовый формат.
Выберите диапазон, в котором нужно преобразовать данные.
Нажмите кнопку Текст в число на панели XLTools Вы выпадающем списке выберите Число в текст Готово, каждой ячейке назначен текстовый формат. Обратите внимание, что в верхнем левом углу ячеек появится индикатор, что числовое значение на самом деле сохранено как текст.
покупка
Как изменить или преобразовать число в текст в Excel?
Иногда вы можете не захотеть, чтобы числа в ячейках участвовали в вычислениях, или вы можете захотеть отобразить ведущие нули в числах в ячейках. Для этого вам может потребоваться преобразовать число в текст. Следующие методы позволяют преобразовывать числа в ячейках в текст в Excel.
Преобразование числа в текст с функцией текста
Если вы знакомы с формулами Microsoft Excel, вы можете преобразовывать числа в ячейках в текст с помощью функции «Текст».
Если вы просто хотите преобразовать число в текст без какого-либо форматирования, вы можете использовать формулу: = ТЕКСТ (A1; «0») ;
1. В ячейке E1 введите формулу = ТЕКСТ (A1; «0»).
2, Затем нажмите Enter ключ. И выберите ячейку E1, перетащите маркер заполнения по диапазону ячеек, к которому вы хотите применить эту формулу, Смотрите скриншот:
Внимание: Поскольку данные представляют собой формулы, вы можете изменить формулы на значения. Скопируйте ячейки и щелкните правой кнопкой мыши то место, куда вы хотите вставить значения, и выберите Специальная вставка> Значения.
Если вы хотите отображать ведущий ноль в числах, вы можете использовать эту формулу: = ТЕКСТ (A1; «00000») ; такие как преобразовать 23 в 00023 .
Как и в предыдущих шагах, если вы примените эту формулу, вы получите следующий результат:
А если вы хотите преобразовать числа в текст, содержащий несколько десятичных цифр, вы можете использовать эту формулу: = ТЕКСТ (A1; «0.000») . Такие как преобразовать 23 в 23.000 .
Также с помощью вышеуказанных шагов, если вы примените эту формулу, вы получите результат в виде следующих снимков экрана:
Внимание: вы можете определить стиль текста. Например, если вы хотите преобразовать числа в текст в формате телефонного номера, вы можете заменить «000» на «00-00000» или другие.
Конвертируйте или записывайте числа в английскую или китайскую валюту:
Работы С Нами Kutools for Excel‘s Числа в слова в Excel можно писать числа на английском и китайском языках. Например, можно писать » 12.75 «К» Двенадцать долларов и семьдесят пять центов «.
Kutools for Excel: с более чем 300 удобными надстройками Excel, которые можно попробовать бесплатно без ограничений в течение 30 дней. Загрузите и бесплатную пробную версию прямо сейчас!
Преобразование числа в текст с помощью команды «Форматировать ячейки»
Microsoft Excel Формат ячеек Команда также может преобразовывать числа в текст.
1. Выберите числа, которые вы хотите преобразовать в текст.
2. Щелкните выделенный диапазон правой кнопкой мыши и выберите Формат ячейкиs из контекстного меню. Смотрите скриншот:
3. В Формат ячеек в диалоговом окне выберите Текст в Категории в поле под Число и нажмите OK кнопка. Смотрите скриншот:
4. Затем числа в выбранном диапазоне преобразуются в текст.
Преобразование числа в текст с помощью Kutools for Excel
Если у вас есть Kutools for Excel установлен, вам будет проще преобразовать числа в ячейках в текст с Преобразование текста в число.
После установки Kutools for Excel, пожалуйста, сделайте следующее:
1. Выберите диапазон с числами, которые вы хотите преобразовать в текст.
2. Нажмите Кутулс > Содержание > Преобразование текста в число. Смотрите скриншот:
3. В Преобразование текста в число диалоговое окно, проверьте Число в текст вариант, а затем щелкните OK or Применить кнопка. И все числа были преобразованы в текст в исходном диапазоне. Смотрите скриншот:
Демо
Kutools for Excel: с более чем 300 удобными надстройками Excel, которые можно попробовать бесплатно без ограничений в течение 30 дней. Загрузите и бесплатную пробную версию прямо сейчас!
Связанная статья :