Преобразуйте числа, сохранённые как текст, в настоящие числа или наоборот
Вы не можете быть совершенно уверены, что результаты вычислений верны, если вы не уверены, что ячейкам Excel присвоен правильный формат – и числовые значения распознаются как числа. В некоторых ячейках вы можете обнаружить ошибку, что это число на самом деле сохранено как текст. Значит, Excel будет игнорировать такие числа в расчётах. Или же наоборот, иногда бывает нужно, чтобы Excel воспринимал числа как текст.
Инструмент «Преобразовать текст в число» поможет исправить ситуацию в массиве данных за секунды:
Преобразовать числа, сохранённые как текст, в числа
Преобразовать числа в текстовый формат
Перед началом работы добавьте «Преобразовать текст в число» в Excel
«Преобразовать текст в число» – это один из 20+ инструментов в составе надстройки XLTools для Excel. Работает в Excel 2019, 2016, 2013, 2010, десктоп Office 365.
Скачать XLTools для Excel
– пробный период дает 14 дней полного доступа ко всем инструментам.
Как преобразовать числа, сохранённые как текст, в настоящие числа
Если в ячейке стоит числовое значение, и при этом в верхнем левом углу есть индикатор ошибки – значит, что это число на самом деле сохранено как текст. Поэтому Excel будет обрабатывать это значение как текст – и будет игнорировать его в расчётах. Поэтому крайне важно преобразовать подобные нераспознанные числа в полноценный числовой формат.
Выберите диапазон, в котором нужно преобразовать данные.
Нажмите кнопку Текст в число на панели XLTools Вы выпадающем списке выберите Текст в число Готово, каждой ячейке назначен числовой формат. Обратите внимание, что из ячеек пропадёт индикатор ошибки.
Как преобразовать числа в текстовый формат
Если вам нужно, чтобы числовые значения в ячейке обрабатывались как текст, вы можете добавить апостроф (одинарную кавычку) перед значением. Это по сути преобразует числовое значение в текстовый формат.
Выберите диапазон, в котором нужно преобразовать данные.
Нажмите кнопку Текст в число на панели XLTools Вы выпадающем списке выберите Число в текст Готово, каждой ячейке назначен текстовый формат. Обратите внимание, что в верхнем левом углу ячеек появится индикатор, что числовое значение на самом деле сохранено как текст.
Преобразование чисел из текстового формата в числовой
Числа, хранящиеся в виде текста, могут привести к непредвиденным результатам, таким как нерасчисленная формула, отображающаяся вместо результата.
В большинстве случаев Excel распознает это, и вы увидите оповещение рядом с ячейкой, в которой числа хранятся в виде текста. Если вы видите оповещение:
-
Выберите ячейки, которые требуется преобразовать, а затем выберите
Дополнительные сведения о форматировании чисел и текста в Excel см. в статье Форматирование чисел и текста.
- Если кнопка оповещения недоступна, вы можете включить оповещения об ошибках, выполнив следующие действия.
Другие способы преобразования
Использование формулы
С помощью функции ЗНАЧЕН можно возвращать числовое значение текста.
-
Вставка нового столбца
VALUE
Щелкните и перетащите вниз, чтобы заполнить формулу в другие ячейки. Теперь вы можете использовать этот новый столбец или скопировать и вставить эти новые значения в исходный столбец.
Для этого выполните указанные ниже действия.
- Выделите ячейки с помощью новой формулы.
- Нажмите клавиши CTRL+C. Щелкните первую ячейку исходного столбца.
- На вкладке Главная щелкните стрелку под кнопкой Вставить и выберите пункт Вставить специальные >значения
. или используйте сочетание клавиш CTRL + SHIFT + V.
Преобразование текстового столбца в числа
- Выбор столбца
Выберите столбец с этой проблемой. Если вы не хотите преобразовывать весь столбец, можно выбрать одну или несколько ячеек. Ячейки должны находиться в одном и том же столбце, иначе этот процесс не будет работать. (Если эта проблема возникла в нескольких столбцах, см. раздел Использование специальной вставки и умножения ниже.)
Примечание: Если вы по-прежнему видите формулы, которые не выводят числовые результаты, возможно, включен параметр Показать формулы. Откройте вкладку Формулы и отключите параметр Показать формулы.
Использование специальной вставки и умножения
Если описанные выше действия не сработали, можно использовать этот метод, который можно использовать, если вы пытаетесь преобразовать несколько столбцов текста.
- Выберите пустую ячейку без этой проблемы, введите в нее число 1 и нажмите клавишу ВВОД.
- Нажмите клавиши CTRL+C , чтобы скопировать ячейку.
- Выделите ячейки с числами, которые сохранены как текст.
- На вкладке Главная выберите Вставить >Специальная вставка.
- Выберите Умножить и нажмите кнопку ОК. Excel умножит каждую ячейку на 1, при этом преобразовав текст в числа.
Числа, хранящиеся в виде текста, могут привести к непредвиденным результатам, таким как нерасчисленная формула, отображающаяся вместо результата.
Использование формулы
С помощью функции ЗНАЧЕН можно возвращать числовое значение текста.
-
Вставка нового столбца
VALUE
Щелкните и перетащите вниз, чтобы заполнить формулу в другие ячейки. Теперь вы можете использовать этот новый столбец или скопировать и вставить эти новые значения в исходный столбец.
Для этого выполните указанные ниже действия.
- Выделите ячейки с помощью новой формулы.
- Нажмите клавиши CTRL+ C. Щелкните первую ячейку исходного столбца.
- На вкладке Главная щелкните стрелку под кнопкой Вставить и выберите пункт Вставить специальные >значения.
или используйте сочетание клавиш CTRL+ SHIFT + V.
Преобразование текста в число в ячейке Excel
При импорте файлов или копировании данных с числовыми значениями часто возникает проблема: число преобразуется в текст. В результате формулы не работают, вычисления становятся невозможными. Как это быстро исправить? Сначала посмотрим, как исправить ошибку без макросов.
Как преобразовать текст в число в Excel
Excel помогает пользователю сразу определить, значения в ячейках отформатированы как числа или как текст. Числовые форматы выравниваются по правому краю, текстовые – по левому.
Когда при импорте файлов или сбое в Excel числовой формат становится текстовым, в левом верхнем углу ячеек появляется зеленый треугольничек. Это знак ошибки. Ошибка также возникает, если перед числом поставить апостроф.
Способов преобразования текста в число существует несколько. Рассмотрим самые простые и удобные.
- Использовать меню кнопки «Ошибка». При выделении любой ячейки с ошибкой слева появляется соответствующий значок. Это и есть кнопка «Ошибка». Если навести на нее курсор, появится знак раскрывающегося меню (черный треугольник). Выделяем столбец с числами в текстовом формате. Раскрываем меню кнопки «Ошибка». Нажимаем «Преобразовать в число».
- Применить любые математические действия. Подойдут простейшие операции, которые не изменяют результат (умножение / деление на единицу, прибавление / отнимание нуля, возведение в первую степень и т.д.).
- Добавить специальную вставку. Здесь также применяется простое арифметическое действие. Но вспомогательный столбец создавать не нужно. В отдельной ячейке написать цифру 1. Скопировать ячейку в буфер обмена (с помощью кнопки «Копировать» или сочетания клавиш Ctrl + C). Выделить столбец с редактируемыми числами. В контекстном меню кнопки «Вставить» нажать «Специальная вставка». В открывшемся окне установить галочку напротив «Умножить». После нажатия ОК текстовый формат преобразуется в числовой.
- Удаление непечатаемых символов. Иногда числовой формат не распознается программой из-за невидимых символов. Удалим их с помощью формулы, которую введем во вспомогательный столбец. Функция ПЕЧСИМВ удаляет непечатаемые знаки. СЖПРОБЕЛЫ – лишние пробелы. Функция ЗНАЧЕН преобразует текстовый формат в числовой.
- Применение инструмента «Текст по столбцам». Выделяем столбец с текстовыми аргументами, которые нужно преобразовать в числа. На вкладке «Данные» находим кнопку «Текст по столбцам». Откроется окно «Мастера». Нажимаем «Далее». На третьем шаге обращаем внимание на формат данных столбца.
Последний способ подходит в том случае, если значения находятся в одном столбце.
Макрос «Текст – число»
Преобразовать числа, сохраненные как текст, в числа можно с помощью макроса.
Есть набор значений, сохраненных в текстовом формате:
Чтобы вставить макрос, на вкладке «Разработчик» находим редактор Visual Basic. Открывается окно редактора. Для добавления кода нажимаем F7. Вставляем следующий код:
Sub Conv() With ActiveSheet.UsedRange arr = .Value .NumberFormat = "General" .Value = arr End With End Sub
Чтобы он «заработал», нужно сохранить. Но книга Excel должна быть сохранена в формате с поддержкой макросов.
Теперь возвращаемся на страницу с цифрами. Выделяем столбец с данными. Нажимаем кнопку «Макросы». В открывшемся окне – список доступных для данной книги макросов. Выбираем нужный. Жмем «Выполнить».
Цифры переместились вправо.
Следовательно, значения в ячейках «стали» числами.
Если в столбце встречаются аргументы с определенным числом десятичных знаков (например, 3,45), можно использовать другой макрос.
Sub Conv() With ActiveSheet.UsedRange .Replace ",","." arr = .Value .NumberFormat = "General" .Value = arr End With End Sub
Таким образом, возникающую при импорте или копировании числовых данных ошибку легко устранить. Преобразовать текст в число можно разными способами (с помощью макросов и без них). Сделать это просто и быстро. А впоследствии с числовыми аргументами производятся все необходимые операции.
- Создать таблицу
- Форматирование
- Функции Excel
- Формулы и диапазоны
- Фильтр и сортировка
- Диаграммы и графики
- Сводные таблицы
- Печать документов
- Базы данных и XML
- Возможности Excel
- Настройки параметры
- Уроки Excel
- Макросы VBA
- Скачать примеры
Число сохранено как текст преобразовать в число все сразу
Страницы: 1
число в ячейке сохранено как текст с пробелами перед числом и после
Пользователь
Сообщений: 78 Регистрация: 01.01.1970
29.03.2010 13:26:14
Надо преобразовать числа в таблице в столбце M в нормальный вид, без пробелов до и после числа. Поиск пробелов и их замена чарез Ctrl+F ничего не дал (или же я чего-то не доделываю), функция СЖПРОБЕЛЫ сработала, но не отображает текст, как число, обрабатывать такие чила не возможно, хотя ячейке и присвоен числовой формат. Как преобразовать эти числа, чтобы с ними можно было бы совершать вычисления.
Прикрепленные файлы
- post_112410.xlsx (16.81 КБ)
Пользователь
Сообщений: 2684 Регистрация: 01.01.1970
29.03.2010 13:37:58
Вы думаете мы сразу взяли и угадали в каких ячейках Вам нужны числа?
Пользователь
Сообщений: 47199 Регистрация: 15.09.2012
29.03.2010 13:38:41
Копировать любую ячейку с нулем, меню Правка-Спецвставка-Сложить. Текст преобразунтся в числа.
Или, если хотите, =СЖПРОБЕЛЫ(. )*1