Преобразовать «числа как текст» в Excel в настоящие числа

Чтобы убрать ошибку «Число сохранено как текст», выделите ячейки, нажмите на появившийся значок с восклицательным знаком и выберите «Преобразовать в число». Если значка нет, сработает Данные → Текст по столбцам → Готово или формула =--A2 (два минуса превращают текст в число). Для чисел с точкой или пробелами из выгрузок подойдёт =ЧЗНАЧ(A2;".";" "). Просто сменить формат ячейки на «Числовой» недостаточно — уже введённые значения останутся текстом. Исправить весь диапазон в 1 клик можно бесплатной процедурой !SEMTools.

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

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

1. Ошибки в расчетах: Представьте себе финансового аналитика, усердно работающего над сложной таблицей. Ему неизвестно, что некоторые (визуально числовые) записи на самом деле имеют текстовый формат. Как итог, формулы, зависящие от этих значений, рассчитываются неправильно, что приводит к некорректным результатам и ошибочным решениям.
2. Проблемы с сортировкой: В отчете о продажах столбец с данными о выручке на первый взгляд выглядит нормально. Однако некоторые записи на самом деле являются текстом. При сортировке отчета по выручке эти записи окажутся не на своем месте, нарушая порядок и мешая анализу.
3. Искажение графиков: Руководитель проекта сделал диаграмму для визуализации хода выполнения проекта. Если в числовых данных некоторые являются текстом, это может привести к графикам с искаженной действительностью.
4. Проблемы с импортом данных: Маркетинговая команда импортирует файл с данными о клиентах в Excel. Некоторые контактные номера сохранены как текстовые источником данных. При попытке использовать эти номера для рекламных кампаний команда сталкивается с ошибками, так как Excel обрабатывает эти записи как текст, нарушая их предназначение.
5. Путаница с условным форматированием: В базе данных HR данные о стаже сотрудников кажутся числами. Однако, если часть из них будет в текстовом формате, применение условного форматирования на основе числовых диапазонов может привести к неправильным визуальным подсказкам в данных, нарушая процесс принятия решений.

Почему числа могут быть текстом

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

1. Данные импортированы из другого источника (десктопное или серверное приложение, отчет из веб-интерфейса и т.д.), который по той или иной причине все их отдаёт в текстовом формате.
2. Числа намеренно сохранены как текст с помощью знака ' (апостроф) перед ними.
3. При копировании данных на Excel лист из веб-интерфейсов или прочих текстовых файлов не совпадает разделитель целой и дробной части. Обычно это либо точка, либо запятая, часто зависит от региональных настроек. Если разделитель не совпадает с теми, что установлены в настройках Excel, целые числа распознаются как числа, а дробные — как текст.
4. Лишние пробелы (в т.ч. неразрывные пробелы), табуляция, перенос строки и прочие невидимые символы перед или после чисел. Они невидимы для человека, но для компьютера являются текстовыми символами. Их наличие в ячейке автоматически делает её значение текстовым.
5. Разделитель разрядов в виде неразрывного пробела (часто фигурирует в выгрузках из сторонних источников)
6. Выбор текстового формата для числовых столбцов инструментом "импорт данных", намеренно или по недосмотру, разумеется, приведет к тому, что они и будут в текстовом формате.

Как сохранить числа как текст

Самый простой способ сохранить число как текст — вставить перед ним апостроф. Этот символ позволяет значение ячейки в любом из форматов превратить в текстовый.

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

Второй способ — заранее задать ячейкам формат «Текстовый» (Ctrl+1 → «Число» → «Текстовый»), и только потом вводить данные: так сохраняются ведущие нули в артикулах и кодах (например, «000123»). Формулой число превращает в текст функция =ТЕКСТ(A2;"0") или приём =A2&"".

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

А теперь давайте рассмотрим основные способы отличить текст от чисел и как предотвратить их ввод в виде текста.

Встроенные подсказки в Excel

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

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

Проверить формулой, текст ли в ячейке, можно функцией =ЕТЕКСТ(A2): ИСТИНА — текст, ЛОЖЬ — число или пусто.

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

Выравнивание внутри ячеек

Самое очевидное отличие текстовых данных от числовых в том, что их выравнивание по умолчанию — по левому краю, а не по правому. Поэтому основная рекомендация здесь — не меняйте выравнивание ячеек, назначенное по умолчанию. Оно позволит вам быстро отличать числовые данные от текстовых!

"Проверка данных" как превентивная валидация

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

• Выделить список ячеек, для которых будет производиться проверка данных;
• Можно (и нужно) выбрать, целое или действительное число вы хотите видеть в ячейках;
• Выбрать тип сравнения целевого значения с эталонным
• Задать минимум и/или максимум;
• Нажать "ОК"

К сожалению, данный способ не спасает от простой вставки текстовых данных (Ctrl+V) поверх ячеек с установленными правилами валидации, т.к. такая вставка затирает их.

Как превратить текст в числа

Перечислю основные методы быстрого решения проблем, в зависимости от их причины.

Текст по столбцам — самый быстрый способ

1. Выделите столбец с «числами-текстом» (только один столбец).
2. Нажмите Данные → Текст по столбцам.
3. Сразу нажмите «Готово». Excel заново распознает значения, и текст станет числами.

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

Специальная вставка: умножить на 1

Работает для нескольких столбцов сразу: введите 1 в пустую ячейку и скопируйте её, выделите диапазон, нажмите Ctrl+Alt+V, выберите «значения» и операцию «умножить». Умножение заставляет Excel пересчитать значения как числа.

Преобразовать текст в число формулой

Формулы =--A2, =ЗНАЧЕН(A2) и =A2*1 превращают текст в число, если он записан по правилам вашей системы. Для выгрузок, где дробная часть отделена точкой, а разряды — пробелами, используйте ЧЗНАЧ с явными разделителями:

Лишние пробелы

Если причина в них - нужно удалить лишние пробелы из ячеек.

Разделитель разрядов

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

1. с помощью процедуры "найти и заменить" заменить один разделитель на другой (например, точку на запятую)
2. поменять разделитель разрядов в настройках Excel (Опция находится в разделе Параметры Excel - "Дополнительно")

Другие (в т.ч. невидимые) символы в ячейках

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

Если разница ненулевая, есть также два варианта:

1. Если такой лишний символ очевиден, он один или два - их можно выделить и заменить на пустоту, обычно этого достаточно для решения.
2. Если лишних символов много, и не все просто так легко заметить (табуляция, неразрывные пробелы, переносы строк и т.д.) - лучше не удалять лишнее, а пойти от обратного. Читайте, как извлечь числа из текстовых ячеек.

"Поправить" числовые ячейки в 1 клик с надстройкой !SEMTools (бесплатно)

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

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

Поэтому предлагаю вам наиболее удобное решение для подобной задачи - мою надстройку для Excel. Установить !SEMTools потребуется лишь однажды, и она будет запускаться вместе с Excel каждый раз, когда вы будете им пользоваться. Удобная процедура на интуитивно понятной панели инструментов в отдельной вкладке Excel поможет вам в пару кликов мыши превратить ваши числа в настоящие.

Процедура доступна в бесплатной версии надстройки как часть бесплатных опций. Никаких ограничений на её использование нет. Находится в разделе "ИЗМЕНИТЬ" - "Изменить числа":

Почему текст не превращается в число

• Сменили формат на «Числовой», а значения остались текстом. Формат влияет только на новый ввод. Уже введённые значения нужно «переввести»: «Текст по столбцам → Готово», умножение на 1 или F2+Enter в каждой ячейке.
• ЗНАЧЕН возвращает #ЗНАЧ!. В ячейке есть посторонние символы: неразрывный пробел, буквы («руб.»), невидимые символы. Удалите их или извлеките числа.
• После преобразования «15.5» стало датой 15 мая. В русской локали точка между числами похожа на дату. Используйте ЧЗНАЧ(A2;".") или замените точку на запятую через «Найти и заменить» до преобразования.
• Длинные коды превратились в 1,23E+15 и потеряли цифры. Excel хранит только 15 значащих цифр. Номера карт, счетов и длинные артикулы оставляйте текстом.
• Пропали ведущие нули. «000123» после преобразования становится 123. Если нули важны, оставьте значение текстом или задайте формат «000000».

Числа как текст в Excel — частые вопросы

Как убрать ошибку «Число сохранено как текст»?

Выделите ячейки, нажмите значок предупреждения и выберите «Преобразовать в число». Если значок не появляется — «Данные → Текст по столбцам → Готово».

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

Для одного столбца — «Текст по столбцам», для нескольких — специальная вставка с умножением на 1, для формул — =--A2. В !SEMTools это одна бесплатная процедура для любого диапазона.

Почему Excel не видит число в ячейке как число?

Значение сохранено как текст: из-за апострофа, текстового формата, пробелов или неподходящего десятичного разделителя. Проверить можно формулой =ЕТЕКСТ(A2).

Как сохранить число как текст?

Поставьте перед ним апостроф ('), задайте ячейке формат «Текстовый» до ввода или используйте формулу =ТЕКСТ(A2;"0").

Итоги: как превратить текст в число в Excel

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