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

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

Надстройка позволяет удалять апострофы перед числами, находить числа, отформатированные как текст и преобразовывать их в реальные числа, а также удалять начальные, конечные пробелы и другие непечатаемые символы (Tab, Alt+Enter) в выбранном диапазоне значений.

Другие материалы по теме:

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

Каждый пользователь, которому много приходится работать с документами, хотел бы упростить, автоматизировать свою работу хотя бы в какой-то степени. Специальные средства, реализованные разработчиками в редакторе таблиц Microsoft Excel, позволяют это сделать. В этой статье разберёмся, как в Excel преобразовать число в текст и наоборот. Давайте же начнём. Поехали!

Конвертация числа в текстовый вид

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

Форматирование через контекстное меню

Это самый часто используемый вариант конвертации, поэтому мы решили начать именно с него. Следуйте приведённой ниже инструкции, и у вас всё получится.

1. Первым делом выделите те значения на листе, которые вы хотите конвертировать в текст. На текущем этапе программа воспринимает эти данные как число. Об этом свидетельствует установленный параметр «Общий», который находится на вкладке «Главная».
2. Кликните правой кнопкой мыши по выделенному объекту и в появившемся меню выберите «Формат ячеек».
3. Перед вами появится окошко форматирования. Откройте подраздел «Число» и в списке «Числовые форматы» нажмите на пункт «Текстовый». Далее сохраните изменения клавишей «ОК».
4. По завершении этой процедуры вы можете убедиться в успешном преобразовании, посмотрев на подменю «Число», находящееся на панели инструментов. Если вы всё сделали верно, в специальном поле будет отображаться информация о том, что ячейки имеют текстовый вид.
5. Однако, на предыдущем шаге настройка не заканчивается. Excel ещё не полностью выполнил конвертацию. Например, если вы решите подсчитать автосумму, то чуть ниже высветится результат.
6. Для того чтобы завершить процесс форматирования, поочерёдно для каждого элемента выбранного диапазона проделайте следующие манипуляции: сделайте два клика левой кнопкой мыши по ячейке и нажмите на клавишу «Enter». Двойное нажатие также можно заменить функциональной кнопкой «F2».
7. Готово! Теперь приложение будет воспринимать числовую последовательность как текстовое выражения, а значит, и автосумма этой области данных будет равняться нулю. Ещё одним признаком того, что ваши действия привели к необходимому результату, является наличие зелёного треугольника внутри каждой ячейки. Единственное — эта пометка в ряде случаев может отсутствовать.

Инструменты в ленте

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

1. Выделите нужную область значений, а затем перейдите во вкладку «Главная». Здесь вам необходимо найти категорию «Число» и кликнуть на небольшой треугольник рядом с полем формата (по умолчанию выставлен «Общий»).
2. В выпадающем перечне вариантов выберите «Текстовый» тип отображения.
3. После чего, как и в предыдущем методе, последовательно для всего диапазона, установите курсор на каждую ячейку и сделайте двойной клик ЛКМ (или F2), а затем щёлкните на клавишу «Enter».

Использование функции

Дополнительным способом переформатирования числовых элементов в текстовые, является базовая функция «ТЕКСТ». Её особенно удобно использовать в тех случаях, когда необходимо перенести значения с новым форматом в другой столбец или объём данных слишком велик, чтобы вручную выполнять преобразования для каждой ячейки. Согласитесь, если документ имеет сотни или даже тысячи строк, конвертация при помощи уже рассмотренных вариантов не рациональна, поскольку займёт слишком много времени.

Как работать с этой опцией:

1. Выберите ячейку, с которой будет начинаться преобразованный диапазон. Далее около строчки с формулами кликните на иконку «Вставить функцию».
2. Перед вами откроется окно «Мастер функций». Здесь нужно выбрать категорию «Текстовые» и в поле, находящемся чуть ниже, соответствующий пункт «ТЕКСТ». Подтвердите нажатием «ОК».
3. Далее появится панель с настройкой аргументов для выбранной функции, которая состоит из двух параметров: «Значение» и «Формат». В первое поле введите преобразуемое число, либо укажите ссылку на то место, где оно расположено. Второе поле предназначено для корректировки нецелых чисел. Например, если вы пропишите «0», то результат будет без дробных знаков, хоть в исходнике они и присутствовали. Соответственно, если написать «0,0», то текстовый тип будет иметь одну цифру после запятой. Аналогичным образом формируется то, что вы увидите на выходе, если внести «0,00» и тому подобные варианты.

По завершении всех манипуляций нажмите на клавишу «ОК».

4. Теперь следует просто скопировать формулу в смежные элементы листа. Для этого наведите курсор на левый нижний угол только что отредактированной ячейки. Когда курсор изменит вид на небольшой крестик, зажмите левую кнопку мыши и перетащите формулу на всё поле диапазона параллельно исходным данным.
5. Как вы видите, все числа появились на своих местах. Однако, на этом процесс конвертации ещё не закончен. Выделите полученный столбец и на вкладке «Главная» в самом первом разделе «Буфер обмена» нажмите на значок «Копировать».
6. Если вы хотите сохранить и начальный вариант: не сбрасывая выделение, кликните правой кнопкой мыши по преобразованной области и в предложенном списке выберите «Специальная вставка», а в следующем окне щёлкните на «Значения и форматы чисел».

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

7. Если вы выбрали второй вариант, то фрагмент с формулами можно удалить. Чтобы это сделать, выделите их, кликните ПКМ → «Очистить содержимое».

Конвертация текста в число

Пришло время рассмотреть способы обратной конвертации в Excel. Методов по переводу текстовых данных в числовые несколько больше, поэтому мы уверены, что вы найдёте для себя наиболее подходящий.

Преобразование с помощью уведомления об ошибке

Один из самых быстрых и простых способов произвести конвертацию — это воспользоваться специальным значком, уведомляющим пользователя о возможной ошибке. Эта иконка имеет форму ромба, в который вписан восклицательный знак. Обычно она появляется при выделении ячеек, имеющих зелёную пометку в верхнем левом углу, о которой мы ранее уже говорили. Цифры, содержащиеся в поле с текстовым представлением, вызывают подозрения у программы, и она тем самым сигнализирует юзеру, чтобы тот обратил внимание на этот момент. Однако, Excel не всегда отображает данный значок, поэтому рассматриваемый способ форматирования является скорее ситуационным. Но в любом случае, если вы обнаружили у себя этот «маячок», можете легко выполнить нужные преобразования.

1. Нажмите на ту ячейку, в которой находится индикатор об ошибке, а затем кликните на соответствующую пиктограмму.
2. В открывшемся меню выберите пункт «Преобразовать в число».
3. После этого объект сразу примет числовой тип.
4. Если необходимо переформатировать одновременно несколько значений, выделите весь диапазон и повторите предыдущие шаги.

Окно форматирования

В Excel также имеется возможность обратного конвертирования через специальное форматирующее окошко. Алгоритм следующий:

1. Выделите диапазон цифр, представленных в текстовом варианте, а затем щёлкните правой кнопкой мышки.
2. В контекстном меню нас интересует позиция «Формат ячеек».
3. Запустится окно форматирования, в котором необходимо сделать выбор в пользу одного из двух форматов: «Общий» или «Числовой». Независимо от того, на каком варианте вы остановились, приложение будет воспринимать цифры как числа. Единственное, если вы выбрали «Числовой» метод, то в правом блоке можно дополнительно настроить такие параметры как, количество знаков после запятой и разрядные разделители. После всех манипуляций нажмите на «ОК».
4. На последнем этапе нужно один за другим прощёлкать все элементы, установив курсор в каждый из них и нажав «Enter».

Инструменты в ленте

Ещё один достаточно простой способ перевода текстового типа данных в числовой осуществляется при помощи инструментов, находящихся на верхней рабочей панели.

1. В первую очередь следует выделить те значения, которые предстоит трансформировать. Далее на панели быстрого доступа перейдите во вкладку «Главная», а затем на ленте найдите группу «Число».
2. В специальном поле выставьте «Общий» вид или «Числовой».
3. После этого отдельно нажмите на каждую из выделенных ячеек, используя клавиши «F2» и «Enter». Алгоритм уже был нами описан выше.

Форматирование завершено! Необходимые текстовые данные изменили тип на числовой.

Применение формулы

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

1. В пустой ячейке, напротив первого преобразуемого объекта впишите следующую последовательность символов: «=» и «—» (знак «равно» и два минуса). Затем укажите ссылку на трансформируемый элемент. В приведённом случае мы выполнили двойное умножение на «-1» и получили тот же самый результат, но только в другом формате.
2. После нажатия «Enter» вы увидите готовое значение. При помощи маркера заполнения растяните эту формулу на всю область диапазона. Это действие аналогично уже описанному нами в пункте про функцию «ТЕКСТ».
3. Теперь нужно выделить созданный столбец и скопировать его, кликнув на соответствующую кнопку на вкладке «Главная». Кроме того, можно использовать комбинацию: «Ctrl + C».
4. Далее выделите изначальный список и кликните ПКМ. В представленном перечне опций выберите «Специальная вставка» → «Значения и форматы чисел».
5. Исходные данные были заменены новыми. На этапе транзитный диапазон с нашей формулой уже можно удалить. Выделите его, щёлкните правой кнопкой мышки и в выпавшем меню выберите «Очистить содержимое».

Обращаем ваше внимание, что вместо двойного умножения на минус единицу можно воспользоваться и другими операциями, не влияющими на итоговое число. Например, прибавить или отнять от него ноль и тому подобное.

Опция специальной вставки

Этот способ во многом схож с предыдущим, однако имеет одно отличие: здесь нет необходимости создавать новый столбец. Перейдём непосредственно к инструкции:

1. В любом пустом месте на листе впишите цифру «1», а затем скопируйте эту ячейку уже знакомой опцией «Копировать» на ленте инструментов.
2. После чего выделите преобразуемый диапазон и кликните ПКМ. В открывшемся окне дважды перейдите в пункт «Специальная вставка».
3. Откроется меню настройки, здесь необходимо установить флажок в блоке «Операция» напротив строчки «Умножить». Подтвердите внесённые изменения кнопкой «ОК».
4. Вот и всё! Конвертация окончена. Вспомогательную единицу, которую мы применяли для этой процедуры, можно смело удалить.

Инструмент «Текст столбцами»

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

1. Выделите исходный фрагмент, а затем запустите необходимую опцию, перейдя поп пути «Данные» → «Работа с данными» → «Текст по столбцам».
2. Перед вами откроется «Мастер текстов». Оказавшись на первой странице, обязательно убедитесь, что в форматирующем блоке напротив пункта «С разделителями» стоит флажок. После этого нажмите «Далее».
3. На втором шаге ничего менять не нужно, поэтому просто кликните «Далее».
4. На третьем шаге необходимо перейти по кнопке «Подробнее», расположенной в правой части окошка.
5. В окошке настроек, в свободном поле «Разделитель целой и дробной части» впишите точку, а в поле «Разделитель разрядов» — апостроф. Следом нажмите «ОК».
6. После возвращения к «Мастеру текстов» нажмите «Готово».

Применение макросов

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

1. Выполните переход во вкладку «Разработчик», затем на ленте найдите категорию «Код» и нажмите на «Visual Basic».
2. Откроется редактор, в который потребуется вставить следующий текст:
   Sub Текст_в_число()

   Selection.NumberFormat = "General"

   Selection.Value = Selection.Value

   End Sub

   После вставки просто закройте это окошко, кликнув на стандартный крестик.

3. Далее выделите фрагмент, который следует переформатировать. После этого на вкладке «Разработчик» в области «Код» кликните на «Макросы».
4. В списке всех записанных макросов выберите «Текст_в_число» и нажмите «Выполнить».
5. Конвертация успешно завершена.

Конвертирование при помощи сторонней надстройки

Для реализации этого способа в первую очередь необходимо скачать и установить специальную надстройку «sumprop». После того как вы скачаете пакет, переместите файл «sumprop» в папку, путь к которой можно посмотреть следующим образом. Откройте меню «Файл» и нажмите «Параметры». В окне параметров Excel перейдите на вкладку «Надстройки». Внизу, в пункте «Управление» укажите «Надстройки Excel» и щёлкните по кнопке «Перейти». В открывшемся окне нажмите «Обзор». Далее скопируйте путь к папке, в которую нужно будет переместить файл «sumprop». После этого запустите Excel заново и ещё раз откройте окно надстроек (повторите вышеописанные действия). В разделе «Доступные надстройки» отметьте галочкой пункт «Сумма прописью». Нужная функция появится в программе.

Теперь рассмотрим, как этим пользоваться. В ячейку введите какое-нибудь число и нажмите на кнопку «Вставить функцию». В появившемся окне выберите «Определённые пользователем» в разделе «Категория». В списке ниже вы найдёте функции «ЧислоПрописью», «СуммаПрописью» и несколько её вариаций для валют (рубли, гривны, доллары, евро). Удобно использовать, если необходимо указать какую-либо денежную сумму словами, что очень часто требуется в различной документации.

Обратите внимание, что после того, как вы применили функцию к какой-либо ячейке, все числа, которые вы будете в неё вводить, сразу будут преобразованы в текст. Используйте «ЧислоПрописью» или «СуммаПрописью» в зависимости от того, какие задачи стоят перед вами сейчас.

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

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

Главная » Уроки MS Excel

Автор Елизавета КМ На чтение 5 мин Опубликовано 09.01.2021

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

Содержание

1. Проблемы, вызванные неправильным форматом чисел
2. Меняем формат ячейки
3. Преобразование формата через формулы
4. Используем специальные вставки
5. Отключение проверки на наличие ошибок
6. Заключение

Проблемы, вызванные неправильным форматом чисел

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

Для исправления указанной ошибки необходимо выделить все ячейки, в которых установлен неверный формат. При этом появится значок «!» (восклицательный знак), при нажатии на который Excel сам укажет на наличие ошибки, а именно «Число сохранено как текст».

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

Меняем формат ячейки

Числовые значения в таблице Excel, выраженные в текстовом формате, не дают возможности работать с формулами и делать необходимые вычисления. Ячейки также могут поменять установленный формат в результате перенесения файла на другой компьютер или при открытии документа в иной версии Эксель. Для исправления такой ошибки необходимо провести следующую последовательность действий:

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

2. Далее следует перейти во вкладку «Данные» и в категории «Работа с данными» активировать параметр «Текст по столбцам». Откроется диалоговое окошко – «Мастер распределения текста по столбцам».

3. Настройки состоят из трех шагов, в соответствие с которыми выставляются параметры столбца – формат исходных данных с разделителем или фиксированной ширины, выбор разделителя и формат данных столбца. В данном случае нас устраивают стандартные настройки, поэтому кликаем «Готово» и значения столбца перейдут в числовой формат.
4. При нажатии комбинации клавиш «Ctrl+1» появиться окошко «Формат ячеек». Здесь выбираем параметр «Числовой» и нажимает «ОК».

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

Преобразование формата через формулы

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

1. В данном случае нужно создать новый столбик справа от значений, которые будем переводить в другой формат.
2. В первой ячейке нового столбика вводим формулу «=ЗНАЧЕН(D5)». В скобках следует указать адрес ячейки.
3. После применения формулы в первой ячейке следует растянуть ее действие на всю длину столбца, нажав курсором мышки на нижний правый угол ячейки и потянув его вниз.

4. Преобразованные значения копируем и переносим в столбец с исходными данными. Выделяем столбец с новыми значениями и нажимаем комбинацию клавиш на клавиатуре «Ctrl+С». Таким образом значения сохранились в буфере обмена. Далее переходим в первую ячейку столбца с исходными значениями и, нажав на стрелочку под параметром «Вставка» на «Главной» вкладке, выбираем категорию «Вставить значения».

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

Не менее простым и эффективным способом преобразования чисел из текстового формата в числовой является использование специальных вставок Эксель. Так, чтобы узнать о том, в каком формате число отображено в данным момент, достаточно при активации ячейки посмотреть на блок инструментов на «Главной» вкладке. Здесь есть параметр, в котором отображается формат ячейки. При стандартных настройках – формат «Общий». При нажатии на стрелку слева появится меню для выбора других форматов.

Для проведения преобразования проставим в одной из ячеек цифру 3, которая останется в «Общем» формате. Необходимо скопировать указанную ячейку и перейти в другую область. При нажатии на стрелочку под параметром «Буфер обмена», выбираем критерий «Специальная вставка». Появится окно с определенным набором параметров. В блоке настроек «Операция» необходимо поставить отметку напротив «Умножить».

Отключение проверки на наличие ошибок

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

1. В открытом файле Excel переходим во вкладку «Файл».
2. В левой панели следует перейти в категорию «Параметры».

3. В появившемся окне переходим в настройки под названием «Формулы».

4. В комплексе команд «Правила поиска ошибок» нужно убедиться в том, что напротив параметра «Числа, отформатированные как текст или с предшествующим апострофом» установлен флажок активации.

Если в документе Excel, наоборот, ошибки не отображаются, то галочку возле указанного выше параметра нужно снять и активировать выбор нажатием кнопки «ОК».

Заключение

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

Оцените качество статьи. Нам важно ваше мнение:

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

Преобразование чисел, сохраненных в виде текста, в числа

Excel

Введите и отформатируйте данные

Формат данных

Формат данных

Преобразование чисел, сохраненных в виде текста, в числа

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 Дополнительно. ..Меньше

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

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

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

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

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

3. Нажмите «Применить».

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

4. Установите формат

Нажмите CTRL + 1 (или + 1 на Mac). Затем выберите любой формат.

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

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

Вы можете использовать функцию ЗНАЧ для возврата только числового значения текста.

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

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

2. Используйте функцию ЗНАЧЕНИЕ

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

3. Оставьте курсор здесь

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

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

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

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

org/ItemList»>

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

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

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

4. На вкладке Главная нажмите Вставить > Специальная вставка .

5. Нажмите Умножьте и нажмите OK . Excel умножает каждую ячейку на 1 и при этом преобразует текст в числа.

6. Нажмите CTRL + 1 (или + 1 на Mac). Затем выберите любой формат.

Связанные темы

Заменить формулу ее результатом
Десять лучших способов очистить ваши данные
Функция ОЧИСТКИ

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

Обычно числа хранятся в виде текста в Excel. Это приводит к неправильным вычислениям при использовании этих ячеек в функциях Excel, таких как СУММ и СРЗНАЧ (поскольку эти функции игнорируют ячейки, содержащие текстовые значения). В таких случаях вам необходимо преобразовать ячейки, содержащие числа в виде текста, обратно в числа.

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

1. Использование ‘ (апостроф) перед числом.
   • Многие люди вводят апостроф перед номером, чтобы сделать его текстовым. Иногда это также происходит, когда вы загружаете данные из базы данных. Хотя это заставляет числа отображаться без апострофа, это влияет на ячейку, заставляя ее обрабатывать числа как текст.
2. Получение чисел по формуле (например, ЛЕВЫЙ, ПРАВЫЙ или СРЕДНИЙ)
   • Если вы извлекаете числовую часть текстовой строки (или даже часть числа) с помощью функций ТЕКСТ, результат это число в текстовом формате.

Теперь давайте посмотрим, как действовать в таких случаях.

В этом уроке вы узнаете, как преобразовать текст в числа в Excel.

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

• Использование опции «Преобразовать в число».
• Измените формат с Текст на Общий/Число.
• Использование специальной вставки.
• Использование текста в столбцах.
• Использование комбинации функций VALUE, TRIM и CLEAN.

Преобразование текста в числа с помощью параметра «Преобразовать в число»

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

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

• Выберите все ячейки, которые вы хотите преобразовать из текста в числа.
• Нажмите на значок в виде желтого ромба, который появляется в правом верхнем углу. В появившемся меню выберите опцию «Преобразовать в число».

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

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

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

Вот шаги:

• Выберите все ячейки, которые вы хотите преобразовать из текста в числа.
• Перейти на главную -> номер. В раскрывающемся списке Формат числа выберите Общий.

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

 Читайте также: Как преобразовать серийные номера в даты в Excel 

Преобразование текста в числа с помощью специальной опции вставки

Чтобы преобразовать текст в числа с помощью специальной опции вставки:

• Введите 1 в любую пустую ячейку на листе. Убедитесь, что он отформатирован как число (т. е. выровнен по правому краю ячейки).
• Скопируйте ячейку, содержащую 1.
• Выберите ячейки, которые вы хотите преобразовать из текста в числа.
• Щелкните правой кнопкой мыши и выберите «Специальная вставка».
• В диалоговом окне «Специальная вставка» выберите «Умножение» в категории «Операция».
• Нажмите OK.

Преобразование текста в числа с помощью преобразования текста в столбец

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

Вот шаги:

• Выберите все ячейки, которые вы хотите преобразовать из текста в числа.
• Перейдите в раздел «Данные» -> «Инструменты данных» -> «Текст в столбцы».
• В мастере преобразования текста в столбец:
  • На шаге 1: выберите «С разделителями» и нажмите «Далее».
  • На шаге 2: выберите Tab в качестве разделителя и нажмите «Далее».
  • На шаге 3: в формате данных столбца убедитесь, что выбрано «Общие». Вы также можете указать место назначения, где вы хотите получить результат. Если вы ничего не укажете, он заменит исходный набор данных.

Хотя результирующие ячейки по-прежнему могут быть в текстовом формате, а числа по-прежнему выровнены по левому краю, теперь это будет работать в таких функциях, как СУММ и СРЗНАЧ.

Преобразование текста в числа с помощью функции VALUE

Вы можете использовать комбинацию функций VALUE, TRIM и CLEAN для преобразования текста в числа.

• Функция ЗНАЧ преобразует любой текст, представляющий число, обратно в число.
• Функция TRIM удаляет все начальные и конечные пробелы.
• Функция CLEAN удаляет лишние пробелы и непечатаемые символы, которые могут проникнуть внутрь при импорте данных или загрузке из базы данных.

Предположим, вы хотите преобразовать ячейку A1 из текста в числа, вот формула:

 =ЗНАЧ(ОТРЕЗАТЬ(ОЧИСТИТЬ(A1))) 

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

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

Вам также могут понравиться следующие учебники по Excel:

• Умножение в Excel с помощью специальной вставки.
• Преобразование чисел в текст в Excel
• Преобразование формул в значения с помощью специальной вставки.
• Пользовательское числовое форматирование Excel.
• Преобразование времени в десятичное число в Excel
• Преобразование отрицательного числа в положительное в Excel
• Как сделать первую букву текстовой строки прописной в Excel
• Преобразование научной записи в число или текст в Excel
• Как преобразовать дату в серийный номер число в экселе?

7 способов преобразования текста в числа в Microsoft Excel

Этот пост покажет вам все способы преобразования текста в числа в Microsoft Excel.

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

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

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

Загрузить файлы примеров

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

Как идентифицировать числа, введенные в виде текста

Как узнать, были ли данные вашего номера введены в виде текста?

Есть несколько простых способов сказать!

Выровнено по левому краю

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

По умолчанию текст выравнивается по левому краю, а числа выравниваются по правому краю.

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

Проверка ошибок зеленого треугольника

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

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

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

Когда вы нажмете на это, он покажет вам, что это за ошибка. В этом случае вы можете увидеть ошибку Номер сохранен как текст .

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

Перейдите на вкладку File и выберите Options внизу. Это откроет меню Параметры Excel .

В разделе Формула меню Параметры Excel ➜ Проверка ошибок .

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

Применен текстовый формат

Одним из способов ввода чисел в виде текста является форматирование ячейки в виде текста. Это приводит к тому, что значения вводятся как текстовые значения независимо от того, являются ли они числовыми.

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

Выберите ячейку и перейдите на вкладку Home . Вы сможете увидеть, является ли Text выбранным форматированием в раскрывающемся меню, найденном в Number 9.0264 сечение ленты.

Предшествующий апостроф

Другой способ ввода чисел в виде текста — использование предшествующего апострофа.

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

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

Использование функции ISTEXT

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

 ISTEXT (ввод) 

• ввод — это значение, ячейка или диапазон, который вы хотите проверить, является ли он текстом.

Функция ISTEXT вернет ИСТИНА , если значение является текстом, и ЛОЖЬ в противном случае.

 = ISTEXT ( B3 ) 

Здесь вы можете увидеть, как функция ISTEXT используется для определения того, содержат ли ячейки текст. Функция возвращает TRUE , когда находит текстовое значение.

 IНОМЕР (ввод) 

• ввод — это значение, ячейка или диапазон, который вы хотите проверить, является ли он числом.

Точно так же вы можете проверить, является ли значение числом, используя функцию ISNUMBER . Он принимает один вход и возвращает ИСТИНА , если значение является числом, и ЛОЖЬ в противном случае.

Здесь те же данные тестируются с использованием функции ISNUMBER . Функция возвращает ЛОЖЬ , когда не находит число.

Строка состояния показывает только количество

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

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

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

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

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

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

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

Щелкните значок Ошибка и выберите Преобразовать в число из вариантов. Затем Excel преобразует каждую ячейку в выбранном диапазоне в число.

Примечание . Этот параметр может работать очень медленно, если требуется преобразовать большое количество ячеек.

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

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

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

Затем вы можете выбрать, как форматировать результаты, и здесь вы можете выбрать общий формат для вывода.

Выполните следующие действия, чтобы использовать функцию преобразования текста в столбцы для преобразования текста в числа.

1. Выберите ячейки, содержащие текст, который вы хотите преобразовать в число.
2. Перейдите на вкладку Данные .
3. Щелкните команду Text to Column на вкладке Data Tools .

4. Выберите с разделителями в параметрах Исходный тип данных .
5. Нажмите кнопку Далее .

6. Нажмите кнопку Next еще раз на шаге 2 мастера преобразования текста в столбцы. Вы можете оставить параметры по умолчанию.

7. Выберите Общий из параметра Формат данных столбца .
8. Выберите пункт назначения , если вы хотите поместить преобразованные значения в новое место. В противном случае вы можете сохранить значение по умолчанию, которое должно быть активной ячейкой, это заменит текст числовыми значениями.
9. Нажмите кнопку Готово .

Это преобразует ваши текстовые значения в числовые значения.

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

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

Это тоже верно, даже если делить нечего!

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

Преобразование текста в число с помощью умножения на 1

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

Вы можете использовать это, чтобы преобразовать текст в числа, умножив их на 1. Поскольку вы умножаете на 1, это не изменит числа, но преобразует их.

 = B3 * 1 

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

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

Преобразование текста в число с помощью специальной вставки Multiply

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

Хорошая новость: есть простой способ умножить текст на 1 без использования формулы.

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

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

1. Введите 1 в любую ячейку на листе. 1 даже не обязательно должно быть числовым значением и может быть текстовым значением.
2. Скопируйте ячейку, содержащую 1 , используя Ctrl + C на клавиатуре или щелкните правой кнопкой мыши и выберите Копировать .
3. Выберите ячейки с текстовыми значениями для преобразования.

1. Нажмите Ctrl + Alt + V на клавиатуре или щелкните правой кнопкой мыши и выберите Специальная вставка . Это откроет меню Paste Special .
2. Выберите Значения в параметрах Вставить и выберите Умножить в разделе Операция Опции.
3. Нажмите кнопку OK .

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

Преобразование текста в число с помощью функции VALUE

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

Функция ЗНАЧ принимает текстовое значение и возвращает текстовое значение в виде числа.

 = ЗНАЧЕНИЕ ( текст ) 

• текст — это текстовое значение, которое вы хотите преобразовать в числовое значение.

Если текст содержит какие-либо нечисловые символы, функция ЗНАЧ вернет #ЗНАЧ! Ошибка . Он не извлекает числа из текста, он может только преобразовывать числа, введенные в виде текста, в числа.

 = ЗНАЧЕНИЕ ( B3 ) 

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

Преобразование текста в числа с помощью Power Query

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

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

Но в Power Query есть простой способ преобразования типов данных, включая текст, в числа.

Выполните следующие действия после импорта данных в Power Query.

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

Каждый столбец в редакторе Power Query имеет значок типа данных, который отображает текущий тип данных столбца. Если столбцу не был назначен тип данных, показанный значок будет ABC123.

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

В этом случае вы можете выбрать в меню опцию Целое число .

Обратите внимание, что значения в столбце теперь выровнены по правому краю, а значок типа данных изменился? Это визуальные индикаторы того, что тип данных теперь представляет собой целые числа, а не текст.

 = Table.TransformColumnTypes(Source,{{"Text", Int64.Type}}) 

Вы увидите, что к запросу был применен новый шаг Changed Type , и он будет использовать приведенную выше формулу кода M.

Затем вы можете нажать Кнопка «Закрыть и загрузить » на вкладке «Главная », чтобы сохранить результаты и загрузить данные в Excel.

Возможно, вам даже не потребуется применять этот шаг преобразования типа данных к вашему запросу. Когда вы впервые импортируете данные в Power Query, он обычно угадывает тип данных и применяет преобразование за вас!

Преобразование текста в числа с помощью Power Pivot

Если вы хотите проанализировать или обобщить данные после преобразования их из текста в числа, вам может потребоваться выполнить преобразование в Power Pivot.

Power Pivot — это надстройка, позволяющая эффективно анализировать миллионы строк в модели данных с помощью сводных таблиц.

С помощью Power Pivot вы можете создавать вычисления на уровне строк, используя язык формул DAX для создания новых столбцов в ваших наборах данных. Затем вы можете получить доступ к этим новым столбцам в сводных таблицах, подключенных к модели данных.

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

Выполните следующие действия в надстройке Power Pivot, чтобы создать новый вычисляемый столбец, преобразующий текст в числа.

Выберите любую ячейку под заголовком «Добавить столбец».

 =ЗНАЧ(ЧислаТекста[Текст]) 

Вставьте приведенную выше формулу. TextNumbers — это имя таблицы данных в Power Pivot, а Text — это имя столбца, который вы хотите преобразовать.

Дважды щелкните заголовок столбца, чтобы изменить имя.

Нажмите значок «Сохранить» и закройте надстройку Power Pivot.

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

1. Перейдите на вкладку Вставка .
2. Нажмите кнопку сводной таблицы .
3. Выберите From Data Model в доступных опциях.

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

Выводы

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

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

К счастью, существует множество простых способов преобразования текста в числа, такие как проверка ошибок, специальная вставка, базовое умножение и функция VALUE . Это все простые способы преобразования текста внутри сетки.

Если вы импортируете данные с помощью Power Query или Power Pivot, вы можете выполнить преобразование в каждом из этих инструментов. Оба имеют методы, доступные для преобразования текста в числа.

Используете ли вы какой-либо из этих методов для преобразования текстовых значений в числа? Знаете ли вы какие-нибудь другие интересные способы выполнения этого действия? Позвольте мне знать в комментариях ниже!

Об авторе

Джон является Microsoft MVP и квалифицированным актуарием с более чем 15-летним опытом. Он работал в различных отраслях, включая страхование, рекламные технологии и совсем недавно в консалтинге Power Platform. Он умеет решать проблемы и страстно любит использовать технологии для повышения эффективности бизнеса.

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

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

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

Содержание

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

Данные ячейки текст или числа?

Существует несколько способов узнать, отформатировано ли число или набор чисел в столбце или строке как текст в Excel.

Самый простой способ — выбрать ячейку, выбрать меню Home и в группе «Число» на ленте отметить формат числа, отображаемый в раскрывающемся списке.

Если в раскрывающемся списке отображается «текст», вы знаете, что ячейка отформатирована как текстовый формат. Если вы хотите выполнить числовые вычисления в ячейке с помощью формул Excel, вам нужно сначала преобразовать ее.

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

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

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

1. Преобразовать в число

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

1. Сначала выберите ячейки, которые вы хотите преобразовать обратно в числовой формат. Рядом с выбором появится желтый ромб с восклицательным знаком посередине.

2. Выберите этот символ. В раскрывающемся списке выберите Convert to Number .

При этом все выбранные вами текстовые числа будут обновлены до общего числового формата данных.

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

2. Использование текста в столбцах

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

1. Выберите весь столбец данных, которые вы хотите преобразовать из текста в числа.

2. Выберите Данные в меню, а затем выберите Текст в столбцы в разделе «Инструменты данных» на ленте.

3. В окне мастера оставьте выбранным по умолчанию разделитель и выберите Следующий .

4. На следующей странице мастера оставьте выбранной по умолчанию Tab и снова выберите Next .

5. Наконец, на последней странице мастера убедитесь, что Общие выбраны в разделе Формат данных столбца . Для поля Destination вы можете либо выбрать новый столбец, в который вы хотите поместить числовые данные, либо просто сохранить текущий выбранный столбец как есть. Выберите Готово .

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

Примечание : вы заметите, что фактическое форматирование ячейки не меняется с «Текст» на «Общий», хотя сами значения теперь можно использовать как числа. Однако, если вы установите для выходного столбца новый столбец, вы заметите, что для форматирования нового столбца задано значение «Общее». Это лишь косметическая проблема, которая не влияет на поведение чисел в отформатированном столбце «Текст».

3. Изменение формата ячейки

Самый простой и быстрый способ преобразовать текст в числа в Excel — просто изменить формат ячейки в главном меню.

Для этого:

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

2. Выберите меню «Главная» и в группе «Число» на ленте выберите раскрывающийся список с текстом.

3. Вы увидите список форматов на выбор. Выберите General для преобразования в числовой формат. Или вы можете выбрать «Число», «Валюта», «Учет» или «Процент», если хотите, чтобы эти конкретные числовые форматы применялись к вашим числовым данным.

4. Использование значений вставки

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

1. Выберите группу пустых ячеек, в которую вы хотите поместить вывод числовых данных. Выберите Формат ячеек во всплывающем меню.

2. В открывшемся окне убедитесь, что в качестве числового формата выбран Общий, и выберите OK .

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

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

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

5. Использование функции ЗНАЧЕНИЕ

В Excel есть специальная функция, которая преобразует число, отформатированное как текст, в числовое значение. Это функция ЗНАЧЕНИЕ.

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

=ЗНАЧ(G2)

Замените «G2» выше на ячейку с числом, которое вы хотите преобразовать. Если вы конвертируете целый столбец чисел, начните только с первой ячейки.

Нажмите Введите , и вы увидите, что число в текстовом формате было преобразовано в число общего формата.

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

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

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

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

Райан с 2007 года пишет в Интернете статьи с практическими рекомендациями и другие статьи о технологиях. Он имеет степень бакалавра наук в области электротехники, 13 лет проработал в области автоматизации, 5 лет в ИТ, а сейчас работает инженером по приложениям. Прочтите полную биографию Райана

Подпишитесь на YouTube!

Вам понравился этот совет? Если это так, загляните на наш канал YouTube на нашем родственном сайте Online Tech Tips. Мы охватываем Windows, Mac, программное обеспечение и приложения, а также предлагаем множество советов по устранению неполадок и обучающих видеороликов. Нажмите на кнопку ниже, чтобы подписаться!

Подписывайся

Excel Convert Text To Number

Home » Basic-Excel » Convert-Text-To-Number

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

Преобразование текста в число с помощью проверки ошибок Excel

В Excel есть функция проверки ошибок, которая может предупредить вас о наличии ячеек, содержащих текстовые представления чисел. Это будет видно в виде маленького цветного треугольника (индикатор ошибки) в верхнем левом углу ваших ячеек (см. ниже).

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

Для этого:

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

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

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

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

Опция проверки ошибок

Для того чтобы описанный выше метод работал, необходимо убедиться, что у вас включена опция проверки ошибок Excel для чисел, хранящихся в виде текста.

Чтобы получить доступ к этому параметру:

1. Щелкните вкладку Файл (или кнопку Microsoft Office в Excel 2007) и выберите Параметры .
2. В окне Параметры Excel выберите Формулы .
3. В разделе Проверка ошибок убедитесь, что опция Включить b проверка ошибок подтверждения отмечена.
4. В разделе Правила проверки ошибок убедитесь, что опция Проверяются числа в текстовом формате или с предшествующим апостропом h e .
5. Щелкните OK .



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

Команда Excel «Текст в столбцы» также преобразует типы данных Excel. Однако эта команда будет работать только с одним столбцом за раз.

Чтобы использовать Excel Text to Columns для преобразования текста в числа:

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

2. На вкладке Данные на ленте Excel выберите параметр Текст в столбцы (см. справа выше).

   При этом откроется мастер преобразования текста в столбцы. В нем:

Преобразование текста в число с помощью специальной вставки Excel

Вы также можете использовать специальную команду вставки Excel для преобразования текста в числа в диапазоне ячеек Excel.

Метод, описанный ниже, добавляет нулевое значение к каждой ячейке в выбранном диапазоне. Эта процедура преобразует текстовые представления чисел в числовые значения, но оставляет без изменений любые ячейки, содержащие нечисловой текст.

Для этого:

1. Введите ноль в любую свободную ячейку электронной таблицы.
2. Выберите ячейку, содержащую ноль, и скопируйте эту ячейку (самый простой способ — использовать сочетание клавиш   Ctrl + C).
3. Выберите ячейки, содержащие текст, который вы хотите преобразовать в числа.

4. На вкладке Главная ленты Excel выберите параметр Вставить → Вставить S специальный (см. справа выше).

   Обратите внимание, что для этого используется сочетание клавиш   Ctrl + Alt + V.

   Откроется диалоговое окно «Специальная вставка», как показано ниже.

5. В диалоговом окне «Специальная вставка» выберите операцию A d d и нажмите OK .

Преобразование текста в число с помощью функции значения Excel

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

Значение Функция Примеры

Пример 1

Столбец A электронных таблиц ниже содержит текстовые представления чисел. Функция Excel Value используется в столбце B электронной таблицы для преобразования текстовых значений в числа.

Formulas:   A B 1 111 =VALUE( A1 ) 2 112 =VALUE( A2 ) 3 113 =VALUE( A3 ) 4 114 =VALUE( A4 ) 5 115 =VALUE( A5 )

Results:   A B 1 111 111 2 112 112 3 113 113 4 114 114 5 115 115




Пример 2

В смещении. Ниже приведенный в Ниже. числовое значение из текстовой строки в ячейке A1 электронной таблицы.

Формулы:   A B 1 3 в этом месяце1026 =VALUE( LEFT( A1,2 ) )

Results:   A B 1 31 days this month 31

Обратите внимание: если текстовый аргумент, передаваемый функции Value, не может быть интерпретирован как числовое значение, функция возвращает ошибку #ЗНАЧ! ошибка.

Методы преобразования текста Excel в числа обсуждаются далее на веб-сайте службы поддержки Microsoft Office.

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

Вернуться на страницу Basic Excel
Вернуться на главную страницу ExcelFunctions.net

Преобразовать текст в числа в Excel [4 метода + снимки экрана]

A общее разочарование в Excel — это когда числа хранятся в виде текста.

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

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

Вот что мы рассмотрим:

1. Как проверить, является ли значение числовым или текстовым
2. Параметр Excel «Преобразовать в число»
3. Преобразование текста в числа с помощью специальной вставки
4. Преобразование текста в числа с помощью формул

Вы можете следовать этой книге.

1. Как проверить, является ли значение числовым или текстовым

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

Однако есть некоторые вещи, на которые следует обратить внимание, и способы их точной проверки.

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

Это просто подсказка и не гарантируется, потому что пользователи могут изменить выравнивание значений ячеек. Тем не менее, это отличное место для начала и хороший ключ к разгадке.

Другой способ проверки — выбрать диапазон значений и просмотреть расчеты в строке состояния.

Если все значения являются числовыми, вы, вероятно, увидите расчеты среднего, количества и суммы. А если какие-то текстовые, то выполняется только подсчет.

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

Чтобы быть полностью уверенным, мы можем использовать функцию Excel с именем ISNUMBER. Эта функция вернет ИСТИНА, если значение числовое, и ЛОЖЬ, если нет.

Вам нужно только предоставить функции ISNUMBER значение для проверки. В этом примере в ячейку C2 была введена следующая формула.

=ISNUMBER(B2)

Я преобразовал некоторые значения в числа. Таким образом, несмотря на то, что все они выглядят как числа, формула ЕЧИСЛО возвращает ЛОЖЬ, что упрощает определение проблемных значений.

2. Параметр Excel «Преобразовать в число»

Первый способ, который мы рассмотрим для преобразования текста в число, — это параметр «Преобразовать в число», представленный в Excel.

Это быстро и просто, но, к сожалению, не всегда вам доступно.

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

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

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

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

Для преобразования в числа;

1. Выберите диапазон ячеек для изменения.
2. Щелкните значок восклицательного знака и выберите Преобразовать в число .

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

3. Преобразование текста в числа с помощью специальной вставки

Специальная вставка в Excel — потрясающий инструмент. Неудивительно, что он также может помочь нам преобразовать текст в числа.

Для этого нам нужно использовать Специальную вставку для выполнения простого вычисления. Когда в вычислении используется текстовое значение, результат является числовым.

1. Введите число 1 в любую ячейку.
2. Скопируйте значение этой ячейки.
3. Выберите диапазон значений, которые необходимо преобразовать в числа.
4. Щелкните Главная > Вставить стрелку списка и Специальная вставка .

5. Выберите Значения и операцию Умножить . Нажмите Ок .

4. Использование «Текст в столбцы» для преобразования текста

«Текст в столбцы» — еще один отличный способ быстро преобразовать текст в числа.

1. Выберите диапазон значений.
2. Нажмите Данные > Текст в столбцы .
3. Шаг 1 мастера «Текст в столбцы» касается разделения текста между столбцами. Нам это не нужно, поэтому нажимаем Next .

4. На шаге 2 снимите все флажки-разделители.

5. Шаг 3 спрашивает о форматировании значения. Оставьте его как General (параметр даты отлично подходит для работы со значениями даты). Нажмите Готово .

Возможно, вы не изменили параметр в процессе преобразования текста в столбцы, и мы могли нажать кнопку «Готово» перед шагом 3.

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

Мне нравится этот метод преобразования текста в числа. Это быстро и просто.

5. Преобразование текста в числа с помощью формул

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

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

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

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

Это пример, который мы рассмотрим здесь.

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

Используя формулу, номер был успешно извлечен. Однако функции ПРАВИЛЬНО и ДЛСТР, используемые в формуле, являются текстовыми функциями. Таким образом, результат возвращается в виде текста.

Функция ЗНАЧ в Excel преобразует текст, представляющий число, в число.

С помощью этой функции результат функции ПРАВИЛЬНО преобразуется из текста в число.

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

Заключительные мысли и дальнейшее чтение

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

No related posts.