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

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

В чем разница между текстом и числом в Excel

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

Текст и числа визуально не отличаются

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

Нулевой результат при сложении текстовых ячеек

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

Количество выделенных ячеек внизу окна

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

Индикатор ошибки возле ячейки с текстом

Если вам неизвестно, в каком формате записано значение в ячейке, рекомендуется использовать формулу Т. Она проверяет, является ли запись текстовой. Если применен текстовый формат, в Excel формула T возвращает значение. В противном случае будет возвращена пустая строка.

Формула Т для проверки ячеек

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

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

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

Переопределение формата ячейки через индикатор ошибки

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

Преобразуем текст в число через индикатор ошибки

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

Преобразовываем весь столбец

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

Переформатирование данных

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

Меняем формат на числовой

Для этого выделяем ячейки, в которых нужно выполнить переформатирование. Далее во вкладке «Главная» находим строку изменения формата. В ней отображается формат «Текстовый». После этого нужно нажать на эту надпись и в выпадающем окне выбрать вариант «Числовой».

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

Переформатировать диапазон ячеек в Excel можно также с помощью контекстного меню:

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

   Вызываем контекстное меню
2. В окне параметров меняем формат на «Числовой». Попутно, при необходимости, можно настроить разделитель и определить число десятичных знаков. Это необязательные параметры и в случае отсутствия необходимости их можно проигнорировать. Сменив формат, нажмите OK, чтобы сделанные изменения сохранились.
   

   Меняем формат на числовой

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

Если по каким-либо причинам описанные выше способы преобразования не подходят, можно перевести текст в числовой тип данных, используя альтернативный метод. Для этого воспользуемся инструментом «Специальная вставка». Рассмотрим, как использовать эту опцию, если число сохранено как текст:

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

   Вставляем единицу и копируем
2. Далее выделяем группу ячеек, в которых числа записаны в текстовом формате. Затем нажимаем правую кнопку мыши и в списке контекстного меню выбираем вариант «Специальная вставка».
   

   Переходим в окно специальной вставки
3. В списке открывшихся операций выбираем «Умножить». Далее нажимаем OK.
   

   Применяем операцию умножения

В процессе вставки все числа из столбца с текстом перемножаются на 1. Для этого программа автоматически меняет их формат на «Общий». После этого вспомогательную ячейку с единицей можно удалить.

Результат выполнения

Применяем формулу для преобразования текста в число

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

Для быстрого преобразования формата во всех ячейках можно использовать формулу «ЗНАЧЕН». Она возвращает числовой тип данных вместо текстового. Применять такую формулу очень удобно, если таблица компактная и в нее можно добавить вспомогательный столбец. Рассмотрим алгоритм действий более подробно далее:

1. Выбираем ячейку для записи результата преобразования. Далее вводим формулу «ЗНАЧЕН». В скобках, в качестве аргумента, указывается ссылка на число, сохраненное в виде текста.
   

   Применяем формулу ЗНАЧЕН
2. Если применить данную формулу к ячейке, в ней отобразится идентичное значение, однако формат при этом уже не будет текстовым. Используя маркер автозаполнения, применяем формулу к другим ячейкам в требуемом диапазоне.
   

   Применяем к остальным ячейкам

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

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

Вставляем значения

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

Применяем макрос для быстрого конвертирования

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

Активация Панели разработчика

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

1. Переходим во вкладку «Файл». В списке доступных опций выбираем «Параметры». Данная кнопка находится внизу меню в левой части экрана.
   

   Открываем параметры
2. В меню параметров далее выбираем раздел «Настроить ленту». В списке «Основные вкладки» ставим отметку напротив строки «Разработчик». Выполнив это действие, сохраняем изменения через нажатие OK.
   

   Включаем панель разработчика

В результате чего вкладка «Разработчик» появится в ленте инструментов программы Excel. Ее можно использовать для создания макросов и надстроек, получения доступа к другим интересным функциям программы.

Создание макроса для преобразования текста в число

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

1. Сначала перейдите во вкладку «Разработчик». Потом в Панели инструментов слева нажмите «Visual Basic». Это команда запустит редактор кода, который требуется для создания макроса.
   

   Запускаем редактор VB
2. В открывшемся редакторе нужно создать новый модуль. Для этого в Панели инструментов в верхней части рабочего пространства нажимаем «Insert» и в выпадающем окне выбираем «Module».
   

   Создаем модуль
3. Далее приступаем непосредственно к самому программированию. Код довольно простой и состоит из нескольких ключевых слов, совокупность которых формирует требуемую функцию. Достаточно просто переписать приведенный фрагмент кода в редактор, соблюдая синтаксис, приведенный на изображении ниже. После ввода кода нужно нажать CTRL+S или значок в виде дискеты, чтобы сохранить созданный модуль.
   

   Вводим код
4. На следующем этапе программа запросит сохранение документа с поддержкой макросов. Используемый предложенный программой вариант и сохраняем файл.
   

   Сохраняем файл с поддержкой макросов

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

Применение макроса

Как видно, создать собственную мини-программу с функцией, которая превращает текстовые значения в числа, довольно просто. Фрагмент кода, который используется при написании макроса можно скопировать и вставить, чтобы не печатать его вручную. Весь фрагмент кода приведен ниже:

Sub Превратить_текст_в_число()

Selection.NumberFormat = "General"

Selection.Value = Selection.Value

End Sub

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

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

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

   Нажимаем выполнить

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

Результат выполнения

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