Как преобразовать текст в число в Excel — 8 способов.
В этом руководстве показано множество различных способов преобразования текста в число в Excel: опция проверки ошибок в числах, формулы, математические операции, специальная вставка и многое другое.
Перестает правильно работать сортировка данных, поскольку числовые и текстовые значения упорядочиваются по-разному. Функции поиска, подобные ВПР, также не могут найти нужные значения (подробнее об этом читайте – Почему не работает ВПР?). Если они находятся среди «нормальных» чисел, то функция СУММ их проигнорирует, а вы этого даже не заметите. В результате – неверные расчеты.
Из этого материала вы узнаете, как преобразовать строки в «настоящие» числа.
Как определить числа, записанные как текст?
В некоторых случаях индикатор ошибки не отображается для чисел, записанных в виде текста. Но есть и другие визуальные индикаторы текстовых чисел:
Число
Строка (текстовое значение)
На изображении ниже вы можете видеть текстовые представления чисел справа и реальные числа слева:
Есть несколько разных способов изменить текст на число Excel. Ниже мы рассмотрим их, начиная с самых быстрых и простых. Если простые методы не работают для вас, пожалуйста, не расстраивайтесь. Нет проблем, которые невозможно преодолеть. Просто нужно попробовать другие способы.
Используем индикатор ошибок.
Если в ваших клетках отображается индикатор ошибки (зеленый треугольник в верхнем левом углу), преобразование выполняется одним щелчком мыши:
Смена формата ячейки.
Все ячейки в Экселе имеют определенный формат, который указывает программе, как их обрабатывать. Например, даже если в клетке таблицы будут записаны цифры, но формат выставлен текстовый, то они будут рассматриваться как простой текст. Никакие подсчеты с ними вы провести не сможете. Для того, чтобы Excel воспринимал цифры как нужно, они должны быть записаны с общим или числовым форматом.
Итак, первый быстрый способ видоизменения заключается в следующем:
Или же можно воспользоваться контекстным меню, вызвав его правым кликом мышки.
Последовательность действий в этом случае показана на рисунке. В любом случае, нужно применить числовой либо общий формат.
Этот способ не слишком удобен и достался нам «в наследство» от предыдущих версий Excel, когда еще не было индикатора ошибки в виде зелёного уголка.
Заметка. Этот метод не работает в некоторых случаях. Например, если вы примените текстовый формат, запишете несколько цифр, а затем измените формат на «Числовой». Тут ячейка все равно останется отформатированной как текст.
Специальная вставка.
По сравнению с предыдущими методами этот метод требует еще нескольких дополнительных шагов, но работает почти на 100%.
Вот что вы делаете:
Если все сделано правильно, то ваши значения изменят выравнивание слева направо. Excel теперь воспринимает их как числа.
Инструмент «текст по столбцам».
Это еще один способ использовать встроенные возможности Excel. При использовании для других целей, например для разделения ячеек, мастер «Текст по столбцам» представляет собой многоэтапный процесс. А вот чтобы просто выполнить нашу метаморфозу, нажимаете кнопку Готово на самом первом шаге 🙂
Это все, что нужно сделать!
Преобразовать текст в число с помощью формулы
До сих пор мы обсуждали встроенные возможности, которые можно применить для перевода текста в число в Excel. Во многих ситуациях это может быть сделано быстрее с помощью формулы.
Функция ЗНАЧЕН может даже распознавать набор цифр, включающих некоторые «лишние» символы.
Например, распознает цифры, записанные с разделителем тысяч в виде пробела:
Точно так же она расправляется с пробелами перед цифрами.
Чтобы преобразовать столбец символьных значений в числа, введите выражение в первую позицию и перетащите маркер заполнения, чтобы скопировать его вниз по столбцу.
Математические операции.
Что это может быть? Например, сложение с нулём, умножение или деление на 1.
Важно, чтобы эти действия не изменили величины чисел. Выше вы видите пример таких операций: двойное умножение на минус 1, умножение на 1, сложение с 0. Наиболее элегантно и просто для ввода выглядит «двойное отрицание»: ставим два минуса перед ссылкой, то есть дважды умножаем на минус 1. Результат расчета не изменится, а записать такую формулу очень просто.
Но если исходные значения отформатированы как текст, Excel также может автоматически применить соответствующий формат и к полученным результатам. Вы сможете заметить это по выровненному влево их содержимому. Чтобы это исправить, обязательно установите общий формат для ячеек, которые используются в формуле.
Примечание. Если вы хотите, чтобы результаты были значениями, а не формулами, используйте после применения этого метода функцию специальной вставки, чтобы заменить их результатами.
Удаление непечатаемых символов.
Когда вы копируете в таблицу Excel данные из других приложений при помощи буфера обмена (то есть Копировать – Вставить), вместе с цифрами часто копируется и различный «мусор». Так в таблице могут появиться внешне не видимые непечатаемые символы. В результате ваши цифры будут восприниматься программой как символьная строка.
Эту напасть можно удалить программным путем при помощи формулы. Аналогично предыдущему примеру, в С2 можно записать примерно такое выражение:
Поясню, как это работает. Функция ПЕЧСИМВ удаляет непечатаемые знаки. СЖПРОБЕЛЫ – лишние пробелы. Функция ЗНАЧЕН, как мы уже говорили ранее, преобразует текст в число.
Извлечение числа из текста.
Функция ЗНАЧЕН также пригодится, когда вы извлекаете что-либо из символьной строки с помощью одной из текстовых функций, таких как ЛЕВСИМВ, ПРАВСИМВ и ПСТР.
Например, чтобы получить последние 3 символа из A2 и вернуть результат в виде цифр, используйте следующее:
На приведенном ниже рисунке продемонстрирована формула трансформации:
Если вы не обернете функцию ПРАВСИМВ в ЗНАЧЕН, результат будет возвращен в виде набора символов, что делает невозможным любые вычисления с извлеченными значениями.
Этот метод подходит, когда вы точно знаете, сколько символов и откуда вы желаете получить, а затем превратить их в число.
Вот как вы можете преобразовать текст в число Excel с помощью формул и встроенных функций. Я благодарю вас за чтение и надеюсь не раз еще увидеть вас в нашем блоге!
Преобразование чисел из текстового формата в числовой
Примечание: Мы стараемся как можно оперативнее обеспечивать вас актуальными справочными материалами на вашем языке. Эта страница переведена автоматически, поэтому ее текст может содержать неточности и грамматические ошибки. Для нас важно, чтобы эта статья была вам полезна. Просим вас уделить пару секунд и сообщить, помогла ли она вам, с помощью кнопок внизу страницы. Для удобства также приводим ссылку на оригинал (на английском языке).
Числа, хранящиеся как текст, могут приводить к непредвиденным результатам. Выделите ячейки и нажмите кнопку 
, чтобы выбрать вариант преобразования. Если эта кнопка недоступна, выполните указанные ниже действия.
1. Выделите столбец
Выделите столбец с такими ячейками. Если вы не хотите преобразовывать весь столбец, можно выбрать одну или несколько ячеек. Ячейки должны находиться в одном и том же столбце, иначе этот процесс не будет работать. (Если такие ячейки есть в нескольких столбцах, см. раздел «Другие способы преобразования» ниже.)
2. Нажмите эту кнопку
Кнопка «столбцы» обычно применяется для разделения столбцов, но ее также можно использовать для преобразования столбца текста в числа. На вкладке Данные нажмите кнопку Текст по столбцам.
3. Нажмите кнопку «Готово»
Остальные шаги мастера нужны для разделения текста на столбцы. Так как нам нужно только преобразовать текст, нажмите кнопку Готово, и Excel преобразует ячейки.
4. Задайте формат
Нажмите клавиши CTRL+1 (или 
+1 на Mac). Выберите нужный формат.
Примечание: Если вы по-прежнему видите формулы, которые не выводят числовые результаты, возможно, включен параметр Показать формулы. Откройте вкладку Формулы и отключите параметр Показать формулы.
Другие способы преобразования
С помощью функции ЗНАЧЕН можно возвращать числовое значение текста.
1. Вставьте новый столбец
Вставьте столбец рядом с ячейками, содержащими текст. В этом примере столбец E содержит числа, которые хранятся в виде текста. Столбец F является новым столбцом.
2. Примените функцию ЗНАЧЕН
В одной из ячеек нового столбца введите =ЗНАЧЕН() и укажите в скобках ссылку на ячейку, содержащую числа, которые хранятся в виде текста. В данном примере это ячейка E23.
3. Наведите указатель мыши
Теперь нужно заполнить формулу вниз. Вот как это сделать: Наведите указатель на правый нижний угол ячейки, чтобы он принял вид знака плюс (+).
4. Щелкните и перетащите вниз
Щелкните и перетащите вниз, чтобы добавить формулу в другие ячейки. После этого вы можете использовать новый столбец или скопировать и вставить новые значения в исходный столбец. Вот как это сделать: Выделите ячейки с новой формулой. Нажмите клавиши CTRL+C. Щелкните первую ячейку в исходном столбце. На вкладке Главная щелкните стрелку рядом с кнопкой Вставить и выберите пункт Специальная вставка > Значения.
Если указанные выше действия не дали результатов, попробуйте использовать этот метод, который подходит, если вы хотите преобразовать несколько столбцов текста.
Выделите пустую ячейку, в которой нет этой проблемы, введите в нее цифру 1 и нажмите клавишу ВВОД.
Нажмите клавиши CTRL+C, чтобы скопировать ячейку.
Выделите ячейки с числами, которые сохранены как текст.
На вкладке Главная щелкните Вставить и выберите пункт Специальная вставка.
Щелкните умножить и нажмите кнопку ОК. Excel умножит каждую ячейку на 1, при этом преобразовав текст в числа.
Нажмите клавиши CTRL+1 (или +1 на Mac). Выберите нужный формат.
Отключение зеленых треугольников
Можно сделать так, чтобы числа, хранящиеся как текст, не помечались зелеными треугольниками. Выберите Файл > Параметры > Формулы и снимите флажок Числа в текстовом формате.
Преобразование текста в число в ячейке Excel
При импорте файлов или копировании данных с числовыми значениями часто возникает проблема: число преобразуется в текст. В результате формулы не работают, вычисления становятся невозможными. Как это быстро исправить? Сначала посмотрим, как исправить ошибку без макросов.
Как преобразовать текст в число в Excel
Excel помогает пользователю сразу определить, значения в ячейках отформатированы как числа или как текст. Числовые форматы выравниваются по правому краю, текстовые – по левому.
Когда при импорте файлов или сбое в Excel числовой формат становится текстовым, в левом верхнем углу ячеек появляется зеленый треугольничек. Это знак ошибки. Ошибка также возникает, если перед числом поставить апостроф.
Способов преобразования текста в число существует несколько. Рассмотрим самые простые и удобные.
Последний способ подходит в том случае, если значения находятся в одном столбце.
Макрос «Текст – число»
Преобразовать числа, сохраненные как текст, в числа можно с помощью макроса.
Есть набор значений, сохраненных в текстовом формате:
Чтобы вставить макрос, на вкладке «Разработчик» находим редактор Visual Basic. Открывается окно редактора. Для добавления кода нажимаем F7. Вставляем следующий код:
Чтобы он «заработал», нужно сохранить. Но книга Excel должна быть сохранена в формате с поддержкой макросов.
Теперь возвращаемся на страницу с цифрами. Выделяем столбец с данными. Нажимаем кнопку «Макросы». В открывшемся окне – список доступных для данной книги макросов. Выбираем нужный. Жмем «Выполнить».
Цифры переместились вправо.
Следовательно, значения в ячейках «стали» числами.
Если в столбце встречаются аргументы с определенным числом десятичных знаков (например, 3,45), можно использовать другой макрос.
Таким образом, возникающую при импорте или копировании числовых данных ошибку легко устранить. Преобразовать текст в число можно разными способами (с помощью макросов и без них). Сделать это просто и быстро. А впоследствии с числовыми аргументами производятся все необходимые операции.