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

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

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

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

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

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

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

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

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

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

Количество

Строка (текстовое значение)

• Выровнено по правому краю по умолчанию.
• Если выбрано несколько ячеек, в строке состояния отображается «Среднее», «Число» и «Сумма» .

• Выровнено по левому краю по умолчанию.
• Если выбрано несколько ячеек, в строке состояния отображается только количество .
• В поле «Числовой формат» отображается текстовый формат (во многих случаях, но не всегда).
• Вы можете увидеть начальный апостроф в строке формул.
• Зеленый треугольник в верхнем левом углу.

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

Выберите элементы (возможно, весь столбец), которые вы хотите преобразовать, и убедитесь, что для их формата установлено значение «Общий.
Перейдите на вкладку «Данные», группу «Инструменты данных» и нажмите кнопку «Текст в столбцы» .
На шаге 1 мастера распространения выберите «Разделители» в разделе «Формат исходных данных» и сразу нажмите «Готово», чтобы завершить преобразование» .

Вот и все!

Повторный ввод.

Если у вас не так много проблемных клеток, о которых мы здесь говорим, то может быть хорошим вариантом просто повторно ввести их.

Для этого сначала установите формат «Обычный». Затем снова введите цифры в каждую из них.

Я думаю, вы знаете, как исправить ячейку, либо двойным щелчком мыши, либо с помощью клавиши F2.

Но это, конечно, если «псевдономеров» мало. В противном случае в игру не стоит играть. Есть много других менее трудоемких способов.

Преобразовать текст в число с помощью формулы

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

В Microsoft Excel есть специальная функция: ЗНАЧЕНИЕ (VALUE на английском языке). Обрабатывает как текст в кавычках, так и ссылку на элемент таблицы, содержащий символы для преобразования.

Функция ЗНАЧ может даже распознавать набор цифр, который включает в себя некоторые «лишние» символы.

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

=ЗНАЧ(«1000»)

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

=ЗНАЧ(«1000 $»)

Обе формулы вернут число 1000.

Таким же образом расправьтесь с пробелами перед числами.

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

Функция ЗНАЧ также полезна, когда вы извлекаете что-то из строки символов, используя одну из текстовых функций, например, ЛЕВЫЙ, ПРАВЫЙ и СРЕДНИЙ.

Например, чтобы получить последние 3 символа A2 и вернуть результат в виде чисел, используйте следующее:

=ЗНАЧ(ПРАВО(A2,3))

На следующем рисунке показана формула преобразования:

Если вы не обернете функцию RIGHT в VALUE, результат будет возвращен в виде массива символов, что сделает невозможным выполнение каких-либо вычислений с извлеченными значениями.

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

Математические операции.

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

Что бы это могло быть? Например, добавить с нулем, умножить или разделить на 1.

=А2+0

=А2*1

=А2/1

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

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

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

Удаление непечатаемых символов.

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

Эта атака может быть удалена программно с помощью формулы. Как и в предыдущем примере, в C2 вы можете написать что-то вроде этого:

=ЗНАЧ(ОТРЕЗАТЬ ПРОБЕЛ(ПРАВИЛЬНО(A2)))

Позвольте мне объяснить, как это работает. Функция CLEAN удаляет ненапечатанные символы. TRIM — лишние пробелы. Функция ЗНАЧ, как мы уже говорили, преобразует текст в число.

Макрос VBA.

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

Нажмите Alt+F11 или перейдите на вкладку «Разработчик» и нажмите кнопку Visual Basic. В появившемся окне редактора добавьте новый модуль через меню Insert — Module и скопируйте туда следующее небольшое выражение:

Sub Text_to_Number() Dim rArea As Range On Error Resume Next ActiveWindow.RangeSelection.SpecialCells(xlCellTypeConstants).Select If Err Затем выйдите из Sub с приложением: В Selection.Areas rArea.Replace «,», «.» rArea.FormulaLocal = rArea.FormulaLocal Следующий rArea с приложением: .ScreenUpdating = True: .EnableEvents = True: .Calculation = xlAutomatic: End With End Sub

После этого закройте редактор, нажав стандартную кнопку закрытия в правом верхнем углу окна.

Что делает этот макрос?

Вы можете выбрать несколько областей данных для преобразования (вы можете использовать мышь, удерживая клавишу CTRL). В этом случае, если в ваших числах в качестве десятичного разделителя используется запятая, она будет автоматически заменена точкой. Фактически, в Windows большую часть времени именно точка разделяет целую и дробную части числа. А при экспорте данных из других программ запятая в этой роли почему-то встречается очень часто.

Чтобы использовать этот код, выберите область электронной таблицы, которую необходимо преобразовать. Щелкните значок «Макросы», который находится на вкладке «Разработчик» в группе «Код». Или нам поможет комбинация клавиш ALT+F8.

Откроется окно Доступные макросы. Находим «Текст_к_номеру», наводим на него и нажимаем кнопку «Выполнить».

Извлечь число из текстовой строки с помощью Ultimate Suite

Как вы уже видели, универсальной формулы Excel для извлечения числа из текстовой строки не существует. Если вам трудно понять формулы или настроить их для своих наборов данных, вам может понравиться этот простой способ получить число из строки в Excel.

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

Вот как вы можете быстро получить число из любой буквенно-цифровой строки:

Перейдите на вкладку «Данные Ablebits» > «Текст» и нажмите «Извлечь) :

Выделите все ячейки с нужным текстом.
На панели инструментов выберите переключатель «Извлечь числа)».
В зависимости от того, хотите ли вы, чтобы результаты были формулами или значениями, выберите переключатель «Вставить как формулу» или оставьте его неотмеченным (по умолчанию).

Я советую вам активировать эту функцию, если вы хотите, чтобы извлеченные числа автоматически обновлялись, как только в исходные строки вносятся изменения.

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

Нажмите кнопку «Вставить результаты». Вот и все!

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

В этом примере мы решили вставить результаты в виде формул, и плагин сделал именно то, что было запрошено:

=ЕСЛИ(МИН(НАЙТИ({0;1;2;3;4;5;6;7;8;9},A2&»_0123456789″))>ДЛСТР(A2), «»,СУММПРОИЗВ(СРЕДН(0&A2, БОЛЬШОЙ(ИНДЕКС(ДВССЫЛ(—СРЕДНИЙ(A2,СТРОКА(ДВССЫЛ(«$1:$»&LIN(A2))),1))*СТРОКА(ДВССЫЛ(«$1:$»&LIN(A2))),0) ,СТРОКА(ДВССЫЛ(«$1:$»&ДЛИНА(A2))))+1,1)*10^СТРОКА(ДВССЫЛ(«$1:$»&ДЛИНА(A2))))/10))

Трудно написать эту формулу самостоятельно, не так ли?

Если флажок «Вставить как формулу» не установлен, вы увидите число в строке формул.

Вам интересно попробовать? Просто скачайте пробную версию Ultimate Suite и убедитесь сами 🙂

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

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