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

Преобразование текста в число в ячейке Excel

При импорте файлов или копировании данных с числовыми значениями часто возникает проблема: число преобразуется в текст. В результате формулы не работают, вычисления становятся невозможными. Как это быстро исправить? Сначала посмотрим, как исправить ошибку без макросов.

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

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

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

Способов преобразования текста в число существует несколько. Рассмотрим самые простые и удобные.

1. Использовать меню кнопки «Ошибка». При выделении любой ячейки с ошибкой слева появляется соответствующий значок. Это и есть кнопка «Ошибка». Если навести на нее курсор, появится знак раскрывающегося меню (черный треугольник). Выделяем столбец с числами в текстовом формате. Раскрываем меню кнопки «Ошибка». Нажимаем «Преобразовать в число».
2. Применить любые математические действия. Подойдут простейшие операции, которые не изменяют результат (умножение / деление на единицу, прибавление / отнимание нуля, возведение в первую степень и т.д.).
3. Добавить специальную вставку. Здесь также применяется простое арифметическое действие. Но вспомогательный столбец создавать не нужно. В отдельной ячейке написать цифру 1. Скопировать ячейку в буфер обмена (с помощью кнопки «Копировать» или сочетания клавиш Ctrl + C). Выделить столбец с редактируемыми числами. В контекстном меню кнопки «Вставить» нажать «Специальная вставка». В открывшемся окне установить галочку напротив «Умножить». После нажатия ОК текстовый формат преобразуется в числовой.
4. Удаление непечатаемых символов. Иногда числовой формат не распознается программой из-за невидимых символов. Удалим их с помощью формулы, которую введем во вспомогательный столбец. Функция ПЕЧСИМВ удаляет непечатаемые знаки. СЖПРОБЕЛЫ – лишние пробелы. Функция ЗНАЧЕН преобразует текстовый формат в числовой.
5. Применение инструмента «Текст по столбцам». Выделяем столбец с текстовыми аргументами, которые нужно преобразовать в числа. На вкладке «Данные» находим кнопку «Текст по столбцам». Откроется окно «Мастера». Нажимаем «Далее». На третьем шаге обращаем внимание на формат данных столбца.

Последний способ подходит в том случае, если значения находятся в одном столбце.

Макрос «Текст – число»

Преобразовать числа, сохраненные как текст, в числа можно с помощью макроса.

Есть набор значений, сохраненных в текстовом формате:

Чтобы вставить макрос, на вкладке «Разработчик» находим редактор 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 Formula Examples
• Создать таблицу
• Форматирование
• Функции Excel
• Формулы и диапазоны
• Фильтр и сортировка
• Диаграммы и графики
• Сводные таблицы
• Печать документов
• Базы данных и XML
• Возможности Excel
• Настройки параметры
• Уроки Excel
• Макросы VBA
• Скачать примеры

Настройка формул для всех строк с помощью формул в столбцах

Дополнительные сведения о планах и их возможностях см. на странице «Расценки».

Возможности

Кому доступна эта возможность?

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

Проверьте, доступна ли эта функция на платформах Smartsheet Regions и Smartsheet Gov.

Настройка формул для всех строк с помощью формул в столбцах

PLANS

• Smartsheet
• Pro
• Business
• Enterprise

For more information about plan types and included capabilities, see the Smartsheet Plans page.

Права доступа

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

Find out if this capability is included in Smartsheet Regions or Smartsheet Gov.

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

• отслеживать расхождения в датах запланированного и фактического завершения всех задач проекта;
• автоматически подставлять информацию о назначенном ресурсе (например, его должности, отделе и непосредственном руководителе) с помощью функции INDEX;
• автоматически рассчитывать итоговые значения для запросов цен на основе информации, предоставленной с помощью формы соответствующего запроса.

Создание и редактирование формул в столбцах

Чтобы создать формулу в столбце, сделайте следующее:

Введите формулу в любую ячейку требуемого столбца.

Формула будет применена ко всем ячейкам в выбранном столбце и будет автоматически подставляться во все новые строки, добавляемые в таблицу.

Чтобы изменить или удалить формулу в столбце, щёлкните правой кнопкой мыши любую ячейку в нем и выберите пункт Изменить формулу в столбце или Преобразовать в ячейку с формулой.

Ограничения, связанные с формулами в столбцах

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

Преобразование чисел-как-текст в нормальные числа

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

• перестает нормально работать сортировка — «псевдочисла» выдавливаются вниз, а не располагаются по-порядку как положено:
• функции типа ВПР (VLOOKUP) не находят требуемые значения, потому как для них число и такое же число-как-текст различаются:
  
• при фильтрации псевдочисла отбираются ошибочно
• многие другие функции Excel также перестают нормально работать:
• и т.д.

Особенно забавно, что естественное желание просто изменить формат ячейки на числовой — не помогает. Т.е. вы, буквально, выделяете ячейки, щелкаете по ним правой кнопкой мыши, выбираете Формат ячеек (Format Cells) , меняете формат на Числовой (Number) , жмете ОК — и ничего не происходит! Совсем!

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

Способ 1. Зеленый уголок-индикатор

Если на ячейке с числом с текстовом формате вы видите зеленый уголок-индикатор, то считайте, что вам повезло. Можно просто выделить все ячейки с данными и нажать на всплывающий желтый значок с восклицательным знаком, а затем выбрать команду Преобразовать в число (Convert to number) :

Все числа в выделенном диапазоне будут преобразованы в полноценные.

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

Способ 2. Повторный ввод

Если ячеек немного, то можно поменять их формат на числовой, а затем повторно ввести данные, чтобы изменение формата вступило-таки в силу. Проще всего это сделать, встав на ячейку и нажав последовательно клавиши F2 (вход в режим редактирования, в ячейке начинает мигаеть курсор) и затем Enter. Также вместо F2 можно просто делать двойной щелчок левой кнопкой мыши по ячейке.

Само-собой, что если ячеек много, то такой способ, конечно, не подойдет.

Способ 3. Формула

Можно быстро преобразовать псевдочисла в нормальные, если сделать рядом с данными дополнительный столбец с элементарной формулой:

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

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

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

Этот способ использовали еще в старых версиях Excel, когда современные эффективные менеджеры под стол ходили зеленого уголка-индикатора еще не было в принципе (он появился только с 2003 года). Алгоритм такой:

• в любую пустую ячейку введите 1
• скопируйте ее
• выделите ячейки с числами в текстовом формате и поменяйте у них формат на числовой (ничего не произойдет)
• щелкните по ячейкам с псевдочислами правой кнопкой мыши и выберите команду Специальная вставка (Paste Special) или используйте сочетание клавиш Ctrl+Alt+V
• в открывшемся окне выберите вариант Значения (Values) и Умножить (Multiply)

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

Способ 5. Текст по столбцам

Если псеводчисла, которые надо преобразовать, вдобавок еще и записаны с неправильными разделителями целой и дробной части или тысяч, то можно использовать другой подход. Выделите исходный диапазон с данными и нажмите кнопку Текст по столбцам (Text to columns) на вкладке Данные (Data) . На самом деле этот инструмент предназначен для деления слипшегося текста по столбцам, но, в данном случае, мы используем его с другой целью.

Пропустите первых два шага нажатием на кнопку Далее (Next) , а на третьем воспользуйтесь кнопкой Дополнительно (Advanced) . Откроется диалоговое окно, где можно задать имеющиеся сейчас в нашем тексте символы-разделители:

После нажатия на Готово Excel преобразует наш текст в нормальные числа.

Способ 6. Макрос

Если подобные преобразования вам приходится делать часто, то имеет смысл автоматизировать этот процесс при помощи несложного макроса. Нажмите сочетание клавиш Alt+F11 или откройте вкладку Разработчик (Developer) и нажмите кнопку Visual Basic. В появившемся окне редактора добавьте новый модуль через меню Insert — Module и скопируйте туда следующий код:

Sub Convert_Text_to_Numbers() Selection.NumberFormat = "General" Selection.Value = Selection.Value End Sub

Теперь после выделения диапазона всегда можно открыть вкладку Разрабочик — Макросы (Developer — Macros) , выбрать наш макрос в списке, нажать кнопку Выполнить (Run ) — и моментально преобразовать псевдочисла в полноценные.

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

P.S.

С датами бывает та же история. Некоторые даты тоже могут распознаваться Excel’ем как текст, поэтому не будет работать группировка и сортировка. Решения — те же самые, что и для чисел, только формат вместо числового нужно заменить на дату-время.

Ссылки по теме

• Деление слипшегося текста по столбцам
• Вычисления без формул специальной вставкой
• Преобразование текста в числа с помощью надстройки PLEX

Преобразование чисел из текстового формата в числовой

Excel для Microsoft 365 Excel для Microsoft 365 для Mac Excel 2021 Excel 2021 для Mac Excel 2019 Excel 2019 для Mac Excel 2016 Excel 2016 для Mac Excel 2013 Excel Web App Excel 2010 Excel 2007 Excel для Mac 2011 Excel Starter 2010 Еще. Меньше

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

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

Выберите ячейки, которые требуется преобразовать, а затем выберите .

В меню выберите Преобразовать в число .

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

Дополнительные сведения о форматировании чисел и текста в Excel см. в статье Форматирование чисел и текста.

• Если кнопка оповещения недоступна, вы можете включить оповещения об ошибках, выполнив следующие действия.

1. В меню Excel выберите пункт Параметры.
2. В разделе Формулы и списки нажмите кнопку Проверка ошибок, а затем проверка флажок Включить проверка проверки ошибок в фоновом режиме.

Другие способы преобразования

Использование формулы

С помощью функции ЗНАЧЕН можно возвращать числовое значение текста.

Вставка нового столбца

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

VALUE

Затем заполните формулу ячейки вниз, в другие ячейки. Для этого наведите курсор на правый нижний угол ячейки, пока он не изменится на знак «плюс».
Щелкните и перетащите вниз

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

Для этого выполните указанные ниже действия.

1. Выделите ячейки с помощью новой формулы.
2. Нажмите клавиши CTRL+C. Щелкните первую ячейку исходного столбца.
3. На вкладке Главная щелкните стрелку под кнопкой Вставить и выберите пункт Вставить специальные >значения
   . или используйте сочетание клавиш CTRL + SHIFT + V.

Преобразование текстового столбца в числа

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

Выделение текста в столбцы
Кнопка Текст в столбцы обычно используется для разделения столбца, но она также может использоваться для преобразования одного столбца текста в числа. На вкладке Данные нажмите кнопку Текст по столбцам.

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

Настройка формата
Нажмите клавиши CTRL+1 (Windows) или +1 (Mac), а затем выберите нужный формат.

Примечание: Если вы по-прежнему видите формулы, которые не выводят числовые результаты, возможно, включен параметр Показать формулы. Откройте вкладку Формулы и отключите параметр Показать формулы.

Использование специальной вставки и умножения

Если описанные выше действия не сработали, можно использовать этот метод, который можно использовать, если вы пытаетесь преобразовать несколько столбцов текста.

1. Выберите пустую ячейку без этой проблемы, введите в нее число 1 и нажмите клавишу ВВОД.
2. Нажмите клавиши CTRL+C , чтобы скопировать ячейку.
3. Выделите ячейки с числами, которые сохранены как текст.
4. На вкладке Главная выберите Вставить >Специальная вставка.
5. Выберите Умножить и нажмите кнопку ОК. Excel умножит каждую ячейку на 1, при этом преобразовав текст в числа.

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

Использование формулы

С помощью функции ЗНАЧЕН можно возвращать числовое значение текста.

Вставка нового столбца

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

VALUE

Затем заполните формулу ячейки вниз, в другие ячейки. Для этого наведите курсор на правый нижний угол ячейки, пока он не изменится на знак «плюс».
Щелкните и перетащите вниз

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

Для этого выполните указанные ниже действия.

1. Выделите ячейки с помощью новой формулы.
2. Нажмите клавиши CTRL+ C. Щелкните первую ячейку исходного столбца.
3. На вкладке Главная щелкните стрелку под кнопкой Вставить и выберите пункт Вставить специальные >значения.
   или используйте сочетание клавиш CTRL+ SHIFT + V.