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

Как преобразовать текст в число в Excel: 10 способов.

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

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

Сортировка данных перестает работать корректно, потому что числовые и текстовые значения сортируются по-разному. Функции поиска, такие как ВПР, также не могут найти нужные значения (подробнее об этом — Почему не работает ВПР?). Подсчет СУММЕСЛИ и СЧЁТЕСЛИ даст неправильный результат. Если они находятся между «нормальными» числами, функция СУММ проигнорирует их и даже не заметит. Результат — неверные расчеты.

• Как числа выглядят как текст?
• Мы используем флаг ошибки.
• Изменение формата ячейки может преобразовать текст в число
• Специальная вставка
• Инструмент «Текст по столбцам
• Повторный вход
• Формулы для преобразования текста в число
• Как вы можете использовать математические операции
• Удаление непечатаемых символов
• Использование макроса VBA
• Как извлечь число из текста с помощью инструмента Ultimate Suite

В этом уроке вы узнаете, как преобразовывать строки в «настоящие» числа.

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

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

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

На изображении ниже вы можете видеть текстовые представления чисел справа и действительные числа слева:

Есть несколько различных способов изменить текст на число в Excel. Мы рассмотрим их ниже, начиная с самого быстрого и простого. Если простые методы не работают для вас, не расстраивайтесь. Нет проблем, которые нельзя преодолеть. Просто нужно попробовать другие способы.

Используем индикатор ошибок.

Если в ваших ячейках отображается индикатор ошибки (зеленый треугольник в левом верхнем углу), преобразование выполняется одним щелчком мыши:

1. Выберите всю область, где числа хранятся в виде текста.
2. Нажмите на предупреждающий знак, а затем — Преобразовать в число.

Таким образом, вы можете преобразовать весь столбец в числа за один раз. Просто выберите всю проблемную область, а затем нажмите восклицательный знак.

Смена формата ячейки.

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

Итак, первый быстрый способ модификации выглядит следующим образом:

1. Выберите ячейки с числами в текстовом формате.
2. На вкладке «Главная» в группе «Число» выберите «Общий» или «Числовой» из выпадающего списка «Формат» .

Или вы можете использовать контекстное меню, щелкнув по нему правой кнопкой мыши.

Последовательность действий в этом случае показана на рисунке. В любом случае вы должны использовать числовой или общий формат.

Этот способ не очень удобен и достался нам от более ранних версий Excel, когда не было индикатора ошибки в виде зеленого уголка.

Примечание. Этот метод не работает в некоторых случаях. Например, если вы применяете текстовый формат, вы вводите несколько чисел, а затем меняете формат на числовой. Здесь ячейка по-прежнему останется отформатированной как текст.

То же самое произойдет, если перед цифрами стоит апостроф. Это недвусмысленно сообщает Excel, что набирается текст и ничего больше.

Наконечник. Если зеленых уголков нет, проверьте, не отключены ли они в настройках Excel (Файл — Параметры — Формулы — Числа в текстовом формате или с предшествующим апострофом).

Специальная вставка.

По сравнению с предыдущими методами этот метод требует нескольких дополнительных шагов, но работает почти на 100%.

Чтобы исправить числа, отформатированные как текст, с помощью специальной вставки, выполните следующие действия:

1. Выберите ячейки таблицы с текстовым номером и установите для них формат «Общий», как описано выше.
2. Скопируйте пустую ячейку. Для этого наведите на него курсор и нажмите Ctrl+C, либо щелкните правой кнопкой мыши и в контекстном меню выберите «Копировать.
3. Выберите ячейки таблицы, которые вы хотите преобразовать, щелкните правой кнопкой мыши и выберите «Специальная вставка». Как вариант, нажмите комбинацию клавиш Ctrl+Alt+V.
4. В диалоговом окне «Специальная вставка» выберите «Значения» в разделе «Вставить», а затем «Добавить» в разделе «Операция».
5. Нажмите «ОК.

Если все сделано правильно, ваши значения изменят выравнивание слева направо. Excel теперь обрабатывает их как числа.

Инструмент «текст по столбцам».

Это еще один способ использования встроенных функций Excel. При использовании для других целей, таких как разделение ячеек, мастер «Текст в столбцы» представляет собой многоэтапный процесс. Но чтобы просто завершить нашу метаморфозу, нажмите кнопку «Готово» на первом этапе