Существует пять различных способов преобразования чисел, отформатированных как текст, в реальные числа в Excel.
Часто, например, когда мы копируем данные из другой программы, текстового файла или из Интернета, Excel может сохранять числа в виде текста. Если вы попытаетесь использовать эти текстовые значения в расчетах и формулах, вы получите ошибки. В подобных ситуациях вам необходимо преобразовать эти числа, отформатированные как текст, обратно в числовые значения.
В этой статье вы узнаете о различных методах преобразования текста в числа в Excel.
Как проверить, является ли значение числом или текстом
В Excel значения могут выглядеть как числа, но они не складываются и не работают как числа, как показано ниже.
Есть несколько способов определить, отформатировано ли значение как текст или число в Excel.
- В электронной таблице Excel числа по умолчанию выравниваются по левому краю, а текст выравнивается по правому краю ячейки.
- Если выбрано несколько ячеек с числовыми значениями, в строке состояния Excel внизу будут отображаться значения «Среднее», «Счетчик» и «СУММ», но если выбрано несколько ячеек с текстовыми значениями, в строке состояния будет отображаться только «Счетчик».
- Иногда, если ячейка содержит число, отформатированное как текст, вы увидите небольшой зеленый треугольник в верхнем левом углу ячейки (индикатор ошибки), как показано выше.
- Если выбрана ячейка с индикатором ошибки, вы увидите предупреждающий знак с желтым восклицательным знаком. Наведите курсор на этот знак, и Excel покажет вам возможную проблему с этой ячейкой: «Число в этой ячейке отформатировано как текст или ему предшествует апостроф».
- Кроме того, когда вы пытаетесь суммировать числа в текстовом формате, они не показывают правильную сумму, как показано выше.
Способы преобразования текста в числа
В этой статье демонстрируются пять различных способов преобразования текста в числа:
- Использование функции "текст для нумерации"
- Изменяя формат ячейки
- Использование специального метода вставки
- Использование мастера преобразования текста в столбцы
- Используя формулы
Преобразование текста в числа с помощью функции текста в числа
Начнем с простого и легкого метода, но он не всегда доступен вам.
Функция преобразования текста в число доступна только в том случае, если числа представляют собой форматированный текст в результате импорта данных из внешних источников или когда перед числом добавляется апостроф. В таких случаях вы увидите небольшой зеленый треугольник в верхнем левом углу ячейки, который является индикатором ошибки.
Выберите ячейки, которые вы хотите преобразовать из текста в числа, и щелкните желтый значок предупреждения, который появляется рядом с выбранной ячейкой или диапазоном. В раскрывающемся меню выберите вариант «Преобразовать в число».
Сделанный! Ваши числа конвертируются.
Преобразование текста в число, изменив формат
Еще один быстрый и простой способ преобразовать числа, отформатированные как текст, в числа - это изменить формат ячейки.
Вот как это сделать:
Выделите все ячейки, которые хотите преобразовать. Вы можете выбрать весь столбец, выбрав букву столбца или нажав Ctrl + Пробел. Перейдите на вкладку «Главная», щелкните раскрывающийся список «Формат чисел» в группе «Число» и выберите вариант «Общие» или «Число».
При выборе варианта «Число» будут отображаться десятичные числа, поэтому вариант «Общие» подойдет. Или вы можете выбрать любые другие форматы в раскрывающемся списке. Выбранный формат будет применен к вашим данным.
После выбора «Общие» выбранные ячейки будут отформатированы как числа, и они будут выровнены по правому краю ячеек.
Этот способ не всегда работает, в таких случаях используйте следующие методы.
Преобразование текста в число с помощью специального метода вставки
Третий метод - еще один эффективный метод преобразования текста в числа. В этом методе вы просто выполняете арифметическую операцию с текстом с помощью специального инструмента «Вставить». Вот как это сделать:
Выберите любую пустую ячейку (которую Excel интерпретирует как 0) и скопируйте ее. Чтобы скопировать ячейку, выберите ячейку и нажмите ‘Ctrl + C‘Или щелкните правой кнопкой мыши и выберите« Копировать »в контекстном меню. Затем выберите ячейки, которые нужно преобразовать в числа, щелкните правой кнопкой мыши и выберите параметр «Специальная вставка». Или нажмите ‘Ctrl + Alt + V'Ярлык, чтобы сделать то же самое.
В диалоговом окне «Специальная вставка» выберите «Значения» в разделе «Вставить» (вверху) и «Добавить» в разделе «Операция» (внизу) и нажмите кнопку «ОК».
Это приведет к добавлению «0» к каждой ячейке в выбранном диапазоне и преобразованию чисел, сохраненных в виде текста, в числа.
Результат:
Преобразование текста в число с помощью мастера текста в столбцы
Еще один метод преобразования текста в числа - использование мастера «Текст в столбец». Это немного длительный процесс, но он полезен для преобразования всего столбца значений. Вот как вы это делаете:
Сначала выберите ячейки, которые вы хотите преобразовать из текста в числа, перейдите на вкладку «Данные», нажмите кнопку «Текст в столбцы» в группе «Инструменты для работы с данными». Откроется «Мастер преобразования текста в столбец».
На шаге 1 мастера выберите «С разделителями» в разделе «Исходный тип данных» и нажмите «Далее».
На шаге 2 мастера убедитесь, что установлен флажок «Вкладка», и нажмите кнопку «Далее».
На последнем шаге мастера убедитесь, что в разделе «Формат данных столбца» выбрано «Общие». Для поля «Назначение» вы можете либо указать новый столбец, в котором вы хотите получить результат, либо оставить его как есть, и он заменит исходный набор данных. Затем нажмите кнопку «Готово».
Вы, вероятно, заметили, что не меняли параметр в течение всего процесса, и мы могли пропустить этап «Готово» на шаге 1. Если ваши данные содержат какие-либо разделители, такие как пробелы или точки с запятой, вы должны выбрать их на шаге 2. мастера.
Преобразование текста в число с помощью формул
Последний метод преобразования текста в числа - использование формул. Это простой и понятный способ, не требующий большого ручного вмешательства. С помощью формулы вы можете автоматизировать преобразование.
Преобразование строки в число с помощью функции VALUE
Excel предлагает специальную функцию «ЗНАЧЕНИЕ» для преобразования текста в число.
Особенностью этой функции является то, что она может распознавать числовое значение, окруженное специальными символами, принимает как текстовые строки, так и ссылку на ячейку, содержащую тексты, и создает чистую версию значения в другой ячейке.
Синтаксис функции ЗНАЧЕНИЕ:
= ЗНАЧЕНИЕ (текст)Например, чтобы преобразовать число, отформатированное как текст в A1, используйте следующую функцию ЗНАЧЕНИЕ:
= ЗНАЧЕНИЕ (A2)Сначала выберите ячейку, в которой вы хотите получить результат, и введите указанную выше формулу.
Если вы хотите преобразовать столбец текстовых значений в числа, вставьте формулу в первую ячейку диапазона, а затем используйте маркер заполнения, чтобы скопировать формулу в остальные ячейки.
Функция Excel VALUE с текстовыми функциями
Функцию VALUE можно использовать в сочетании с текстовыми функциями Excel, то есть LEFT, RIGHT и MID, чтобы извлечь число из текстовой строки.
= ЗНАЧЕНИЕ (ПРАВО (A1,5))В приведенной выше формуле функция VALUE извлекает числовое значение из текстовой строки в ячейке A1 с помощью функции RIGHT.
Преобразование текста в число с помощью арифметических операций в Excel
Вы также можете преобразовать текст в число, выполнив простую математическую операцию, которая не изменяет исходное значение.
Например, чтобы преобразовать число, отформатированное как текст в ячейке A1, вы можете добавить ноль, умножить или разделить 1 на это текстовое значение. Эти операции не изменяют значения, а преобразуют их в числа.
= A1 + 0 (или) = A1 * 1 (или) = A1 / 1
В этой статье мы обсудили все возможные методы преобразования текста в числа в Excel.