Excel число сохранено как текст преобразовать в число все сразу
Перейти к содержимому

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

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

Зеленые треугольники, которые обозначают ячейки с числами, хранящимися как текст

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

1. Выделите столбец

Выделенный столбец

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

2. Нажмите эту кнопку

Вкладка

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

3. Нажмите кнопку "Применить"

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

4. Задайте формат

Выбран денежный формат

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

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

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

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

1. Вставьте новый столбец

Новый столбец рядом с числами в виде текста

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

2. Примените функцию ЗНАЧЕН

Ячейка F23 с формулой =ЗНАЧЕН(E23) и результат 107,71

В одной из ячеек нового столбца введите =ЗНАЧЕН() и укажите в скобках ссылку на ячейку, содержащую числа, которые хранятся в виде текста. В данном примере это ячейка E23.

3. Наведите указатель мыши

Указатель наведен на правый нижний угол ячейки

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

4. Щелкните и перетащите вниз

Указатель перетащен вниз, ячейки заполнены формулой

Щелкните и перетащите вниз, чтобы заполнить формулой другие ячейки. После этого можно будет использовать новый столбец или скопировать и ввести новые значения в исходный столбец. Вот как это сделать: выберем ячейки с новой формулой. Нажмите CTRL+C. Щелкните первую ячейку исходного столбца. Затем на вкладке "Главная" щелкните стрелку под кнопкой "Вировать" и выберите "Специальная >".

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

Выделите пустую ячейку, в которой нет этой проблемы, введите в нее цифру 1 и нажмите клавишу ВВОД.

Нажмите клавиши CTRL+C, чтобы скопировать ячейку.

Выделите ячейки с числами, которые сохранены как текст.

На вкладке Главная щелкните Вставить и выберите пункт Специальная вставка.

Щелкните умножить и нажмите кнопку ОК. Excel умножит каждую ячейку на 1, при этом преобразовав текст в числа.

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

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

Если для каких-либо ячеек на листе был установлен текстовый формат (это мог сделать пользователь или программа при выгрузке данных в 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 и скопируйте туда следующий код:

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

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

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

Добавить комментарий

Ваш адрес email не будет опубликован. Обязательные поля помечены *