преобразовать текст в число с помощью формулы и другими способами

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

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

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

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

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

Числа Строки (текстовые значения)

  • По умолчанию с выравниванием по правому краю.
  • Если выбрано несколько ячеек, в строке состояния отображается Средний, Считать а также СУММА.
  • По умолчанию выровнено по левому краю.
  • Если выбрано несколько ячеек, в строке состояния отображается только Считать.
  • Формат номера поле отображает текстовый формат (во многих случаях, но не всегда).
  • В строке формул может быть виден начальный апостроф.

На изображении ниже вы можете видеть текстовые представления чисел справа и фактические числа слева:
Текстовые представления чисел и фактические числа в Excel

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

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

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

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

  1. Выберите все ячейки, содержащие числа, отформатированные как текст.
  2. Щелкните предупреждающий знак и выберите «Преобразовать в число».

Сделанный!
Функция Excel Convert to Number для проверки ошибок

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

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

  1. Выберите ячейки с числами в текстовом формате.
  2. На Дом вкладка, в Число выберите «Общие» или «Число» в Формат номера выпадающий список.

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

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

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

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

Чтобы исправить числа, отформатированные как текст, с помощью Специальная вставкавот что вы делаете:

  1. Выберите ячейки текстового числа и установите для них формат «Общий», как описано выше.
  2. Скопируйте пустую ячейку. Для этого либо выделите ячейку и нажмите Ctrl+C, либо щелкните правой кнопкой мыши и выберите Копировать из контекстного меню.
  3. Выберите ячейки, которые вы хотите преобразовать в числа, щелкните правой кнопкой мыши и выберите «Специальная вставка». Либо нажмите сочетание клавиш Ctrl + Alt + V.
  4. в Специальная вставка диалоговом окне выберите Значения в Вставить раздел и добавить в Операция раздел.
  5. Нажмите ХОРОШО.

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

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

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

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

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

Вот и все!
Преобразование строки в число с текстом в столбцы.

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

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

Формула 1. Преобразование строки в число в Excel

В Microsoft Excel есть специальная функция для преобразования строки в число — функция ЗНАЧ. Функция принимает как текстовую строку, заключенную в кавычки, так и ссылку на ячейку, содержащую преобразуемый текст.

Функция ЗНАЧ может даже распознать число, окруженное какими-то «лишними» символами — этого не может сделать ни один из предыдущих методов.

Например, формула ЗНАЧ распознает число, введенное с символом валюты и разделителем тысяч:

=ЗНАЧ(“1000$”)

=ЗНАЧ(A2)

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

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

Формула 2. Преобразование строки в дату

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

Например:

=ЗНАЧ(“1 января 2018”)

Или же

=ЗНАЧ(A2)

Где A2 содержит текстовую дату.

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

Того же результата можно добиться, используя функцию ДАТАЗНАЧ:

=ДАТАЗНАЧ(A2)

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

Формула 3. Извлечь число из строки

Функция ЗНАЧЕНИЕ также удобна, когда вы извлекаете число из текстовой строки с помощью одной из текстовых функций, таких как ЛЕВЫЙ, ПРАВЫЙ и СРЕДНИЙ.

Например, чтобы получить последние 3 символа из текстовой строки в формате A2 и вернуть результат в виде числа, используйте следующую формулу:

=ЗНАЧ(ПРАВО(A2,3))

На снимке экрана ниже показана наша формула преобразования текста в число в действии:
Извлечь число из текстовой строки.

Если не обернуть функцию ПРАВИЛЬНО в ЗНАЧЕНИЕ, результат будет возвращен в виде текста, точнее числовой строки, что делает невозможным любые вычисления с извлеченными значениями.

Для получения дополнительной информации см. Как извлечь число из строки в Excel.

Изменить строку Excel на число с помощью математических операций

Еще один простой способ преобразовать текстовое значение в число в Excel — выполнить простую арифметическую операцию, которая фактически не изменяет исходное значение. Что это может быть? Например, добавление нуля, умножение или деление на 1.

=А2+0

=А2*1

=А2/1

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

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

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

Вас также могут заинтересовать

Похожие записи

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

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