Преобразование текста в число в ячейке Excel
При импорте файлов или копировании данных с числовыми значениями часто возникает проблема: число преобразуется в текст. В результате формулы не работают, вычисления становятся невозможными. Как это быстро исправить? Сначала посмотрим, как исправить ошибку без макросов.
Как преобразовать текст в число в Excel
Excel помогает пользователю сразу определить, значения в ячейках отформатированы как числа или как текст. Числовые форматы выравниваются по правому краю, текстовые – по левому.
Когда при импорте файлов или сбое в Excel числовой формат становится текстовым, в левом верхнем углу ячеек появляется зеленый треугольничек. Это знак ошибки. Ошибка также возникает, если перед числом поставить апостроф.
![Ошибка значения.](images/formatirovanie29-1.png)
Способов преобразования текста в число существует несколько. Рассмотрим самые простые и удобные.
- Использовать меню кнопки «Ошибка». При выделении любой ячейки с ошибкой слева появляется соответствующий значок. Это и есть кнопка «Ошибка». Если навести на нее курсор, появится знак раскрывающегося меню (черный треугольник). Выделяем столбец с числами в текстовом формате. Раскрываем меню кнопки «Ошибка». Нажимаем «Преобразовать в число».
- Применить любые математические действия. Подойдут простейшие операции, которые не изменяют результат (умножение / деление на единицу, прибавление / отнимание нуля, возведение в первую степень и т.д.).
- Добавить специальную вставку. Здесь также применяется простое арифметическое действие. Но вспомогательный столбец создавать не нужно. В отдельной ячейке написать цифру 1. Скопировать ячейку в буфер обмена (с помощью кнопки «Копировать» или сочетания клавиш Ctrl + C). Выделить столбец с редактируемыми числами. В контекстном меню кнопки «Вставить» нажать «Специальная вставка». В открывшемся окне установить галочку напротив «Умножить». После нажатия ОК текстовый формат преобразуется в числовой.
- Удаление непечатаемых символов. Иногда числовой формат не распознается программой из-за невидимых символов. Удалим их с помощью формулы, которую введем во вспомогательный столбец. Функция ПЕЧСИМВ удаляет непечатаемые знаки. СЖПРОБЕЛЫ – лишние пробелы. Функция ЗНАЧЕН преобразует текстовый формат в числовой.
- Применение инструмента «Текст по столбцам». Выделяем столбец с текстовыми аргументами, которые нужно преобразовать в числа. На вкладке «Данные» находим кнопку «Текст по столбцам». Откроется окно «Мастера». Нажимаем «Далее». На третьем шаге обращаем внимание на формат данных столбца.
![Преобразовать в число.](images/formatirovanie29-2.png)
![Математические операции.](images/formatirovanie29-3.png)
![Специальная вставка.](images/formatirovanie29-4.png)
![Печьсимв.](images/formatirovanie29-5.png)
![Текст по столбцам.](images/formatirovanie29-6.png)
Последний способ подходит в том случае, если значения находятся в одном столбце.
Макрос «Текст – число»
Преобразовать числа, сохраненные как текст, в числа можно с помощью макроса.
Есть набор значений, сохраненных в текстовом формате:
![Набор значений.](images/formatirovanie29-7.png)
Чтобы вставить макрос, на вкладке «Разработчик» находим редактор Visual Basic. Открывается окно редактора. Для добавления кода нажимаем F7. Вставляем следующий код:
Sub Conv() With ActiveSheet.UsedRange arr = .Value .NumberFormat = "General" .Value = arr End With End Sub
Чтобы он «заработал», нужно сохранить. Но книга Excel должна быть сохранена в формате с поддержкой макросов.
Теперь возвращаемся на страницу с цифрами. Выделяем столбец с данными. Нажимаем кнопку «Макросы». В открывшемся окне – список доступных для данной книги макросов. Выбираем нужный. Жмем «Выполнить».
![Макрос.](images/formatirovanie29-8.png)
Цифры переместились вправо.
![Пример.](images/formatirovanie29-9.png)
Следовательно, значения в ячейках «стали» числами.
Если в столбце встречаются аргументы с определенным числом десятичных знаков (например, 3,45), можно использовать другой макрос.
Sub Conv() With ActiveSheet.UsedRange .Replace ",","." arr = .Value .NumberFormat = "General" .Value = arr End With End Sub
![.](/en/formatting/images/formatting11-9.png)
Читайте так же: как перевести число и сумму прописью в Excel.
Скачать примеры преобразования текста в число.
Таким образом, возникающую при импорте или копировании числовых данных ошибку легко устранить. Преобразовать текст в число можно разными способами (с помощью макросов и без них). Сделать это просто и быстро. А впоследствии с числовыми аргументами производятся все необходимые операции.