Иногда значения в Excel выглядят как числа, но на самом деле это текст. Их нельзя суммировать, умножать, вычитать, хотя выглядят как числа. Дело в том, что это числа в формате текста.
Excel помогает автоматически конвертировать данные в числа при копировании из посторонних источников. Не всегда это получается. В этой статье расскажу как конвертировать такие «текстовые» числа в настоящие.
- Как найти числа в текстовом формате
- Как конвертировать текст в числа в Excel
- Конвертация текста в числа через меню ошибки
- Как конвертировать текст в число с помощью смены формата ячейки
- Как преобразовать текст в число с помощью специальной вставки
- Как конвертировать текст в числа с помощью функции ЗНАЧЕН()
- Как конвертировать текст в числа с помощью математических вычислений
Как найти числа в текстовом формате
Excel автоматически проверяет значения на принадлежность к числовому формату. Вы могли видеть в эксель таблицах ячейки с маленьким зеленым треугольником в левом верхнем углу. Это сигнал, что с форматом ячейки неполадки. Чтобы узнать детали ошибки надо подвести курсор мыши к желтому ромбику с восклицательным знаком и прочитать уведомление:
Иногда уведомление о текстовом формате ячейки с числами отсутствует. Понять, что ячейка с числом в текстовом формате можно по визуальным признакам:
- Числа выровнены по левому краю
- Если выделена группа ячеек с такими «числами», то в строке состояния не будет подсказок по сумме, среднему чисел в выделенном диапазоне
- Апостроф стоит первым символом в строке формул этой ячейки
На скриншоте ниже пример как Excel автоматически выравнивает по левому краю текстовые значения и по правому краю числовые значения.
Как конвертировать текст в числа в Excel
Конвертация текста в числа через меню ошибки
Если в левом верхнему углу ячеек с числовыми значениями появился зеленый треугольник, то:
- Выделите диапазон таких ячеек
- Кликните по иконке с желтым ромбом и восклицательным знаком
- В выпадающем списке выберите пункт меню «Преобразовать в число»
Применять этот метод следует на небольшое количество ячеек. Не рекомендую использовать этот метод для конвертации сотен, тысяч строк. Excel может «зависнуть» в процессе конвертации.
Как конвертировать текст в число с помощью смены формата ячейки
Есть еще один способ изменить формат значений текстовой ячейки на числовой. Сделать это можно с помощью смены формата ячейки на числовой. Как это сделать:
- Выделите левой клавишей мыши диапазон ячеек
- На панели инструментов перейдите на вкладку «Главная»
- Перейдите в подгруппу «Число» и в выпадающем списке выберите формат «Числовой»
Примечание. Этот метод помогает не в 100% случаев. Например, если к ячейке был применен текстовый формат, после этого введено число, затем изменен формат ячейки на числовой, то она продолжит отображаться как текст.
Как преобразовать текст в число с помощью специальной вставки
Этот метод требует чуть больше действий, но работает в 99% случаев.
Как сделать:
- Выделить ячейки с числами в текстовом формате, и задать формат ячейки «Основной»
- Кликнуть левой клавишей мыши по пустой ячейке на листе
- Скопировать эту ячейку, зажав сочетание клавиш CTRL + C
- Выделить левой клавишей мыши ячейки которые хотите конвертировать в числа
- Кликнуть правой клавишей мыши и выбрать пункт «Специальная вставка» в выпадающем меню
- В диалоговом окне в категории «Вставить» поставить галочку напротив «Значения». В разделе «Операция» ставим галочку напротив «Сложить» и нажимаем «ОК»
Значения в выделенном диапазоне теперь будут упорядочены по правой стороне и Excel будет воспринимать их как числа.
Как конвертировать текст в числа с помощью функции ЗНАЧЕН()
Для конвертации числовых значений в текстовом формате в числа часто используют функцию ЗНАЧЕН(). Функция предназначена для перевода текста в числа. Напишите функцию в адресной строке и укажите ссылку на ячейку для конвертации.
Эта функция работает со всеми форматами ячеек.
Как конвертировать текст в числа с помощью математических вычислений
Достаточно умножить значение в текстовом формате на единицу, чтобы получить значение в числовом формате в Excel.
Прибавьте ноль или разделите значение на единицу и текстовое значение будет преобразовано в числовой формат.
Посмотрите видео урок про два способа преобразования чисел в текст в Excel:
Поделитесь в комментариях какой из методов вам кажется самым удобным в работе.
Проще умножить на 1.
При сравнении чисел в разных числовых форматах — значения не совпадают.
Например, ВПР не сочла одинаковыми значения
0020816 в формате 0000000;[RED]-0000000
и 20816 0;[RED]-0
Ирина, здравствуйте, умножить на 1 это такой же способ, как и сложить с пустой ячейкой, описанный выше.