Как извлечь числа или текст из Excel

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

Как извлечь числа или текст из Excel?

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

Номера Excel форматируются как текст

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

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

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

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

Или выберите ячейки и в меню «Числовой формат» на ленте выберите числовой параметр по умолчанию.

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

Применение числового формата с помощью специальной функции Excel «Вставить»

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

Извлечение чисел или текста из ячеек смешанного формата

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

Отделять числа от текста

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

Выберите ячейки, которые вы хотите преобразовать, перейдите в «Данные»> «Текст в столбцы» и с помощью мастера убедитесь, что ячейки отображаются правильно. По большей части вам нужно только нажать «Далее» и «Готово», но убедитесь, что вы выбрали подходящий разделитель; В этом примере это запятая.

Если только числа состоят из 1 и 2 цифр, опция «Фиксированная ширина» также может быть полезна, поскольку она разделяет только первые два или три символа ячейки. Таким образом вы даже можете создать несколько разделений.

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

Извлечь число или текст из строки-разделителя

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

Если вы хотите разделить текст, вы можете использовать функцию ВПРАВО таким же образом. Обратитесь к статье: Функция RIGHT, как использовать функцию для вырезания правой строки в Excel для более подробной информации.

Извлеките числа с одного конца непрерывной строки

Что делать, если между числами и текстом нет разделителя?

При извлечении чисел слева или справа от строки можно использовать вариант формулы LEFT или RIGHT, описанной выше:

= LEFT (A1; SUM (LEN (A1) -LEN (SUBSTITUTE (A1, {“0”, “1”, “2”, “3”, “4”, “5”, “6”, “7” , «8», «9»}, «»)))) = ПРАВО (A1, СУММ (LEN (A1) -LEN (ПОДСТАВИТЬ (A1, {«0», «1», «2», «3» , «4», «5», «6», «7», «8», «9»}, «»))))

Эта команда вернет все числа слева или справа от строки.

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

= МИН (ПОИСК ({0,1,2,3,4,5,6,7,8,9}, A1 & “0123456789”)) = ВПРАВО (A1; LEN (A1) -B1 + 1)

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

Извлеките числа с обоих концов непрерывной строки

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