Преобразование текста в числа в Excel — пошаговое руководство

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

Вот шаги:

• Выделите все ячейки, которые вы хотите преобразовать из текста в числа.
• Зайдите в Дом -> Номер. В раскрывающемся списке Числовой формат выберите Общие.

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

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

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

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

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

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

Вот шаги:

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

Хотя вы все еще можете обнаружить, что результирующие ячейки имеют текстовый формат, а числа по-прежнему выровнены по левому краю, теперь это будет работать в таких функциях, как SUM и AVERAGE.

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

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

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

Предположим, вы хотите преобразовать ячейку A1 из текста в числа, вот формула:
= ЗНАЧЕНИЕ (ОБРЕЗАТЬ (ЧИСТЫЙ (A1)))
Если вы хотите применить это и к другим ячейкам, вы можете скопировать и использовать формулу.

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