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

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

Примечание: Мы стараемся как можно оперативнее обеспечивать вас актуальными справочными материалами на вашем языке. Эта страница переведена автоматически, поэтому ее текст может содержать неточности и грамматические ошибки. Для нас важно, чтобы эта статья была вам полезна. Просим вас уделить пару секунд и сообщить, помогла ли она вам, с помощью кнопок внизу страницы. Для удобства также приводим ссылку на оригинал (на английском языке).

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

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

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

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

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

3. Нажмите кнопку «Готово»

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

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

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

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

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

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

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

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

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

В одной из ячеек нового столбца введите =ЗНАЧЕН() и укажите в скобках ссылку на ячейку, содержащую числа, которые хранятся в виде текста. В данном примере это ячейка 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’ем как текст, поэтому не будет работать группировка и сортировка. Решения — те же самые, что и для чисел, только формат вместо числового нужно заменить на дату-время.

Как преобразовать в число сразу множество ячеек excel 2010

Прочее. Программное обеспечение

Доброго времени, суток 🙂
Вот макрос, который присваивает всем выделенным ячейкам формат «цифровой».
Надеюсь я правильно понял что Вам было нужно. Я вставил набор чисел из word в excel и преобразовал в числа.

Сообщение от ibragimr:
Вот макрос

где?🙂
Я бы тоже сделал как советуют в #2

Сообщение от ISN:
Прибавьте «0» к ячейке в соседнем столбце и посмотрите на результат

Результат: к соседней ячейке прибавляется 0, больше ничего не происходит.
upd: выделить проблемную ячейку, нажать ctrl+a, если надо — руками довыделять невыбранное — где-то рядом остается маркер устранения ошибки —> преобразовываем.

Читать еще:  Как в excel 2007 закрепить область

Сообщение от Studentroman:
Выделить все нажать пкм выбрать формат ячеек выбрать числовой,не?

нет, это не делает число, распознаваемое екселем как текст, числом

Сообщение от engngr:
Результат: к соседней ячейке прибавляется 0, больше ничего не происходит.

Не очень корректно написал, думал поймете смысл:
в ячейке «А1» ваше число, тогда запишем в любой ячейке формулу «=А1+0», разумеется без кавычек.

а потом просто получившийся столбец переставить на место старого?

попробовал. в принципе рабочее решение, но для случая, когда каждое число в одной ячейке.
У меня осложняется тем, что число находится в объединенных ячейках (см. скрин)
И поэтому лишние нули.

Сообщение от eugenmax:
а потом просто получившийся столбец переставить на место старого?

Да, а формулу можно будет удалить.

Сообщение от eugenmax:
У меня осложняется тем, что число находится в объединенных ячейках (см. скрин)

Тогда только через макросы.

Сообщение от eugenmax:
напишите после моего поста сообщение

Сообщение от CaMoCAD:
если выделить n ячеек с зеленым треугольником (Ваш столбец F), то появится желтый ромб с воскл. знаком и стрелочкой.

Если есть пропущенные ячейки, то такой фокус не получится.

Сообщение от eugenmax:
как бы разом их выделить и все преобразовать в число?

Все ответы правильные, только до конца не доделанные.
Проблема решается так :
1. Выделяешь одну ячейку из всех проблемных так чтобы появился ромб
2. Нажимаешь Шифт и держишь, одновременно мышью кликаешь в верхний левый угол таблицы где пересекаются названия строк и столбцов.
3. Этим самым выделяются все ячейки таблицы.
4. Но одновременно с выделением в углу появляется ромб
5. Нажимаешь на ромб и выбираешь «Преобразовать в число»
Всё. Во всех ячейках произойдёт преобразование.

Сообщение от maratovich:
5. Нажимаешь на ромб и выбираешь «Преобразовать в число»

какая функция макроса выполняет это действие?

Преобразовать текст в число Excel, или как сделать ячейку текстового формата числовым форматом

Все ячейки в Excel имеют тот или иной формат, который определяет, что можно делать с данными в ячейке. Например, значение «7» может быть как числом (и можно выполнять математические действия), а может быть и текстом, то есть просто символом, с которым математические действия выполнять нельзя. И это создает сложности, решением которых будет перевести текст (символы) в число в Excel.

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

Как определить, где в ячейке текст, а где число?

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

Как перевести ячейку из текстового в числовой формат?

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

Выйти на то же меню можно и другим способом: выделите ячейки и нажмите правую кнопку мыши (ПКМ), в открывшемся меню выберите Формат ячеек.

Далее выберите Числовой формат. Откорректируйте необходимое количество знаков после запятой. Для этого найдите надпись «Число десятичных знаков» и в счетчике укажите нужное количество десятичных
знаков.

Еще один способ преобразовать текст в число в Excel — использование формулы. Впишите в пустую соседнюю ячейку формулу. Для этого левой кнопкой мыши кликните на соседнюю ячейку и напишите формулу =ЗНАЧЕН(E4) (возможные названия: =ЗНАЧ(E4) или =VALUE(E4) зависит от используемой версии программы). ENTER.

Обратите внимание!

«Преобразовать текст в число Excel» — не совсем правильная формулировка. Правильнее будет сказать: «Преобразование из текстового формата в числовой».

Автор: Пашкова Екатерина Ивановна, преподавать основ компьютерной грамотности, офисных программ.

Автор скриншотов: Белицкий Алексей Викторович, учитель МКОУ «СОШ №7», с. Пелагиада, Шпаковский район, Ставропольский край.

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

Есть мнение?
Оставьте комментарий

Понравился материал?
Хотите прочитать позже?
Сохраните на своей стене и
поделитесь с друзьями

Вы можете разместить на своём сайте анонс статьи со ссылкой на её полный текст

Ошибка в тексте? Мы очень сожалеем,
что допустили ее. Пожалуйста, выделите ее
и нажмите на клавиатуре CTRL + ENTER.

Кстати, такая возможность есть
на всех страницах нашего сайта

2007-2019 «Педагогическое сообщество Екатерины Пашковой — PEDSOVET.SU».
12+ Свидетельство о регистрации СМИ: Эл №ФС77-41726 от 20.08.2010 г. Выдано Федеральной службой по надзору в сфере связи, информационных технологий и массовых коммуникаций.
Адрес редакции: 603111, г. Нижний Новгород, ул. Раевского 15-45
Адрес учредителя: 603111, г. Нижний Новгород, ул. Раевского 15-45
Учредитель, главный редактор: Пашкова Екатерина Ивановна
Контакты: +7-920-0-777-397, info@pedsovet.su
Домен: http://pedsovet.su/
Копирование материалов сайта строго запрещено, регулярно отслеживается и преследуется по закону.

Читать еще:  Как посчитать количество дней между датами в excel

Отправляя материал на сайт, автор безвозмездно, без требования авторского вознаграждения, передает редакции права на использование материалов в коммерческих или некоммерческих целях, в частности, право на воспроизведение, публичный показ, перевод и переработку произведения, доведение до всеобщего сведения — в соотв. с ГК РФ. (ст. 1270 и др.). См. также Правила публикации конкретного типа материала. Мнение редакции может не совпадать с точкой зрения авторов.

Для подтверждения подлинности выданных сайтом документов сделайте запрос в редакцию.

сервис вебинаров

О работе с сайтом

Мы используем cookie.

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

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

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

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

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

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

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

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

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

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

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

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

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

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

Чтобы вставить макрос, на вкладке «Разработчик» находим редактор Visual Basic. Открывается окно редактора. Для добавления кода нажимаем F7. Вставляем следующий код:

Чтобы он «заработал», нужно сохранить. Но книга Excel должна быть сохранена в формате с поддержкой макросов.

Теперь возвращаемся на страницу с цифрами. Выделяем столбец с данными. Нажимаем кнопку «Макросы». В открывшемся окне – список доступных для данной книги макросов. Выбираем нужный. Жмем «Выполнить».

Цифры переместились вправо.

Следовательно, значения в ячейках «стали» числами.

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

Таким образом, возникающую при импорте или копировании числовых данных ошибку легко устранить. Преобразовать текст в число можно разными способами (с помощью макросов и без них). Сделать это просто и быстро. А впоследствии с числовыми аргументами производятся все необходимые операции.

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

Ссылка на основную публикацию
Похожие публикации
Adblock
detector