преобразовать текст в число с помощью формулы и другими способами
В учебнике показано много разных способов превратить строку в число. в Excel: функция проверки ошибок «Преобразовать в число», формулы, математические операции, специальная вставка и многое другое.
Иногда значения на листах Excel выглядят как числа, но они не складываются, не умножаются и не приводят к ошибкам в формулах. Распространенной причиной этого являются числа, отформатированные как текст. Во многих случаях Microsoft Excel достаточно умен, чтобы автоматически преобразовывать числовые строки, импортированные из других программ, в числа. Но иногда числа остаются в формате текста, что вызывает множество проблем в ваших электронных таблицах. Этот учебник научит вас преобразовывать строки в «настоящие» числа.
Как идентифицировать числа, отформатированные как текст в Excel
В Excel есть встроенная функция проверки ошибок, которая предупреждает вас о возможных проблемах со значениями ячеек. Это выглядит как маленький зеленый треугольник в верхнем левом углу ячейки. При выборе ячейки с индикатором ошибки отображается предупреждающий знак с желтым восклицательным знаком (см. скриншот ниже). Наведите указатель мыши на знак, и Excel сообщит вам о потенциальной проблеме: Число в этой ячейке отформатировано как текст или ему предшествует апостроф..
В некоторых случаях индикатор ошибки не отображается для чисел, отформатированных как текст. Но есть и другие визуальные индикаторы текста-цифры:
Числа Строки (текстовые значения)
- По умолчанию с выравниванием по правому краю.
- Если выбрано несколько ячеек, в строке состояния отображается Средний, Считать а также СУММА.
- По умолчанию выровнено по левому краю.
- Если выбрано несколько ячеек, в строке состояния отображается только Считать.
- Формат номера поле отображает текстовый формат (во многих случаях, но не всегда).
- В строке формул может быть виден начальный апостроф.
На изображении ниже вы можете видеть текстовые представления чисел справа и фактические числа слева:
Как преобразовать текст в число в Excel
Существует несколько различных способов изменить текст на число Excel. Ниже мы рассмотрим их все, начиная с самых быстрых и простых. Если простые методы не работают для вас, пожалуйста, не расстраивайтесь. Нет задачи, которую нельзя было бы преодолеть. Вам просто нужно будет попробовать другие способы.
Преобразование в число в Excel с проверкой ошибок
Если в ваших ячейках отображается индикатор ошибки (зеленый треугольник в верхнем левом углу), преобразование текстовых строк в числа выполняется двумя щелчками мыши:
- Выберите все ячейки, содержащие числа, отформатированные как текст.
- Щелкните предупреждающий знак и выберите «Преобразовать в число».
Сделанный!
Преобразование текста в число путем изменения формата ячейки
Еще один быстрый способ преобразовать числовые значения, отформатированные как текст, в числа:
- Выберите ячейки с числами в текстовом формате.
- На Дом вкладка, в Число выберите «Общие» или «Число» в Формат номера выпадающий список.
Примечание. Этот метод не работает в некоторых сценариях. Например, если применить к ячейке текстовый формат, ввести число, а затем изменить формат ячейки на числовой, ячейка останется в текстовом формате.
Измените текст на число с помощью специальной вставки
По сравнению с предыдущими методами этот метод преобразования текста в число требует на несколько шагов больше, но работает почти в 100% случаев.
Чтобы исправить числа, отформатированные как текст, с помощью Специальная вставкавот что вы делаете:
- Выберите ячейки текстового числа и установите для них формат «Общий», как описано выше.
- Скопируйте пустую ячейку. Для этого либо выделите ячейку и нажмите Ctrl+C, либо щелкните правой кнопкой мыши и выберите Копировать из контекстного меню.
- Выберите ячейки, которые вы хотите преобразовать в числа, щелкните правой кнопкой мыши и выберите «Специальная вставка». Либо нажмите сочетание клавиш Ctrl + Alt + V.
- в Специальная вставка диалоговом окне выберите Значения в Вставить раздел и добавить в Операция раздел.
- Нажмите ХОРОШО.
Если все сделано правильно, ваши значения изменят выравнивание по умолчанию слева направо, то есть Excel теперь воспринимает их как числа.
Преобразование строки в число с текстом в столбцы
Это еще один способ преобразования текста в числа в Excel без использования формул. При использовании для других целей, например для разделения ячеек, мастер «Текст в столбцы» представляет собой многоэтапный процесс. Чтобы выполнить преобразование текста в число, щелкните значок Заканчивать кнопка на самом первом шаге 🙂
- Выберите ячейки, которые вы хотите преобразовать в числа, и убедитесь, что их формат установлен на «Общий».
- Переключиться на Данные вкладка, Инструменты данных группу и нажмите кнопку «Текст в столбцы».
- На шаге 1 из Мастер преобразования текста в столбцывыберите Разделители в разделе Исходный тип данныхи нажмите Готово.
Вот и все!
Преобразование текста в число с помощью формулы
До сих пор мы обсуждали встроенные функции, которые можно использовать для преобразования текста в числа в Excel. Во многих случаях преобразование можно выполнить еще быстрее, используя формулу.
Формула 1. Преобразование строки в число в Excel
В Microsoft Excel есть специальная функция для преобразования строки в число — функция ЗНАЧ. Функция принимает как текстовую строку, заключенную в кавычки, так и ссылку на ячейку, содержащую преобразуемый текст.
Функция ЗНАЧ может даже распознать число, окруженное какими-то «лишними» символами — этого не может сделать ни один из предыдущих методов.
Например, формула ЗНАЧ распознает число, введенное с символом валюты и разделителем тысяч:
=ЗНАЧ(«1000$»)
=ЗНАЧ(A2)
Чтобы преобразовать столбец текстовых значений, введите формулу в первую ячейку и перетащите маркер заполнения, чтобы скопировать формулу вниз по столбцу:
Для получения дополнительной информации см. формулу ЗНАЧЕНИЕ для преобразования текста в число.
Формула 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 с помощью формул и встроенных функций. Я благодарю вас за чтение и надеюсь увидеть вас в нашем блоге на следующей неделе!
