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

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

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

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

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

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

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

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

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

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

Числа как текст – варианты валидации

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

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

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

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

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

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

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

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

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

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

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

Исправление проблем

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

---

Смотрите также:

Как сделать число и сумму прописью в Excel

Как массово применить арифметические операции к числам в Excel