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

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

  • автор:

Преобразование ТЕКСТовых значений в ЧИСЛА и ДАТЫ (Часть 2. Групповое изменение в EXCEL)

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

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

Как правило, в таких ячейках установлен текстовый формат. Если изменить формат ячейки на числовой (через Формат ячеек или Главная/ Число/ Числовой формат ), то изменений не последует.

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

После преобразования числа будут выравнены по правому краю ячейки (если, конечно, в формате ячейки установлено Выравнивание по горизонтали — по значению ).

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

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

  • введите в любую пустую ячейку число 1 ;
  • выделите эту ячейку и скопируйте ее значение в Буфер обмена ( CTRL+C );
  • выделите ячейки, которые содержат числа сохраненные как текст;
  • выберите Специальная ставка (на вкладке Главная в группе Буфер обмена щелкните стрелку рядом с командой Вставить , а затем выберите в списке пункт Специальная ставка );
  • выберите Умножить ;
  • нажмите ОК .

После умножения на 1 , числовые значения не изменятся, но будут преобразованы в числа (если по ошибке были выделены текстовые значения, операция умножения на них никак не повлияет).

Для EXCEL 2007: Если до умножения ячейки имели текстовый формат ячеек, то он будет сохранен (числовые значения все равно будут преобразованы, но будут выровнены по левому краю, как текст). Изменить формат ячеек на числовой можно стандартным способом через меню Формат ячеек (нажав CTRL+1 ).

Этот же прием поможет преобразовать ДАТЫ, сохраненные как текст в формат ДАТ ( Если, конечно, EXCEL поймет ту форму, в которой записана ДАТА ). Правда, после преобразования в ячейках будет содержаться число, соответствующее датам . Изменить формат ячеек на формат ДАТЫ можно стандартным способом через меню Формат ячеек (нажав CTRL+1 ).

СОВЕТ: О преобразовании чисел из ТЕКСТового формата в ЧИСЛОвой можно также прочитать в одноименной статье Преобразование чисел из текстового формата в числовой (часть 1) .

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

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

Сообщение о непредвиденных результатах в Excel.

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

Проверка

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

Проверка

Проверка

Изображение листа с выравниванием по левому краю и предупреждением о зеленом треугольнике

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

Ошибка При проверке символа в Excel.

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

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

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

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

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

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

    Используйте функцию VALUE в Excel.

    VALUE

    Поместите курсор здесь.

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

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

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

    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, при этом преобразовав текст в числа.

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

    Сообщение о непредвиденных результатах в Excel.

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

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

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

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

    Используйте функцию VALUE в Excel.

    VALUE

    Поместите курсор здесь.

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

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

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

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

    Преобразуйте числа, сохранённые как текст, в настоящие числа или наоборот

    Вы не можете быть совершенно уверены, что результаты вычислений верны, если вы не уверены, что ячейкам Excel присвоен правильный формат – и числовые значения распознаются как числа. В некоторых ячейках вы можете обнаружить ошибку, что это число на самом деле сохранено как текст. Значит, Excel будет игнорировать такие числа в расчётах. Или же наоборот, иногда бывает нужно, чтобы Excel воспринимал числа как текст.

    Инструмент «Преобразовать текст в число» поможет исправить ситуацию в массиве данных за секунды:

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

    Перед началом работы добавьте «Преобразовать текст в число» в Excel

    «Преобразовать текст в число» – это один из 20+ инструментов в составе надстройки XLTools для Excel. Работает в Excel 2019, 2016, 2013, 2010, десктоп Office 365.

    Начните работу с инструментами XLTools

    Скачать XLTools для Excel
    – пробный период дает 14 дней полного доступа ко всем инструментам.

    Как преобразовать числа, сохранённые как текст, в настоящие числа

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

    Выберите диапазон, в котором нужно преобразовать данные.

    Нажмите кнопку Текст в число на панели XLTools Вы выпадающем списке выберите Текст в число Готово, каждой ячейке назначен числовой формат. Обратите внимание, что из ячеек пропадёт индикатор ошибки.

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

    Как преобразовать числа в текстовый формат

    Если вам нужно, чтобы числовые значения в ячейке обрабатывались как текст, вы можете добавить апостроф (одинарную кавычку) перед значением. Это по сути преобразует числовое значение в текстовый формат.

    Выберите диапазон, в котором нужно преобразовать данные.

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

    Excel works!

    menu

    Бывает что, когда пытаешься произвести вычисления с данными, формулы не работают или Excel выдает ошибку. Причины сразу могут быть неясны. Особенно часто проблема встречается после выгрузки из учетных систем, таких как 1С или SAP. Как избежать проблемы, когда формулы не работают? Чаще всего необходимо преобразовать текст в число в Excel. Несколько полезных способов, как бороться с этим несчастьем читайте ниже.

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

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

    1. Преобразовать текст в число. Сделайте формат ячеек — число

    С самого начала проверьте формат чисел. Выделите диапазон с данными (на нашем примере столбец B). Нажмите горячую клавишу ctrl + 1 (меню Формат ячеек если правой кнопкой мыши). На вкладке Число выберите Формат ячеек — Числовой.

    Очень удобно пользоваться меню Число на главной панели. Здесь сразу доступны удобные форматы. Слева-направо: денежный, процентный, числовой + кнопки уменьшения/увеличения количества знаков после запятой. Рекомендую.

    2. Исправьте ошибки — преобразуйте текст в число специальным инструментом

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

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

    В нашем случае Число сохранено как текст — кликните Преобразовать в число. Вуаля!

    Это же доступно по адресу Формулы — Раздел Зависимости формул — Проверка формул

    3. Умножьте все числа на 1

    Не помог предыдущий вариант? Значит, ошибка довольно редкая, но решается просто. Есть крутая хитрость. В столбце С все значения столбца B перемножьте на 1. Да, буквально так, для ячейки C2 запишите =B2*1 и протяните вниз. Так Excel принудительно преобразовывает имеющиеся значения в число.

    4. Удалите лишний символ в ячейках при помощи Найти и Заменить

    И пункт 3 не помог? Значит в ячейках закрался какой-то символ, например как в ячейке В7 на первой картинке после первого числ стоит символ » «, это может быть даже обычный пробел (поэтому скопируйте именно этот символ). Такими вещами очень часто грешит 1С, причем любой конфигурации.

    Пользуемся инструментом Найти и заменить, подробное описание здесь . Если быстро нажимаете ctrl + H , появиться окно.

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

    В поле Найти: вставляем скопированный символ. В поле Заменить на: ничего не ставим (т.е. заменяем на пусто). Жмем Заменить все.

    5. Функция ЗНАЧЕН

    И, конечно, есть универсальный способ применить функцию =ЗНАЧЕН(). Она лечит все ошибки с числами, кроме лишних символов (не касается пробелов), и преобразовывает текст в число. Очень удобно использовать ее для проверок в расчетах, как подстраховку от ошибок.

    6. Перевод времени в число

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

    P.S.

    Кстати, для того чтобы убрать лишние пробелы в начале и конце ячейки с текстом (например, при записи ФИО мы часто ошибаемся и ставим пробел в конце, который не видно), а так же, чтобы убрать двойные пробелы, есть классная функция =СЖПРОБЕЛЫ() . Она убирает все лишние пробелы!

    Похожие статьи

    • 03.09.2015Удобные форматы чисел в ExcelPosted in Excel
    • 18.03.2015Почему тормозит Excel? Большой размер файла ExcelPosted in Формулы, Мануалы, Работа с файлом
    • 19.04.2015Как вставить текст в Excel?Posted in Работа с текстом
    • 16.07.2015Примечание в Excel. Как редактировать примечанияPosted in Excel
    • 18.03.2015Как разделить текст в Excel по столбцам?Posted in Excel, Работа с текстом
    • 27.11.2015Как удалить символ в ячейке Excel?Posted in Работа с текстом
    • 12.03.2016Заглавные буквы в Excel. Работа с регистромPosted in Работа с текстом
    • 23.10.2015Сортировка формулойPosted in Excel
  • Добавить комментарий

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