Как извлечь число или текст из Excel
Продуктивность

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

Вам нужно извлечь число из строки или смешанного текста в Excel? Вот как преобразовать текст и числа в Microsoft Excel

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

Числа Excel, отформатированные как текст

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

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

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

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

В противном случае выделите ячейки и в меню Формат числа на ленте выберите вариант по умолчанию Число

Если вам нужны более детальные параметры, щелкните выделенную ячейку/ячейки правой кнопкой мыши и выберите Формат ячеек , что откроет соответствующее меню. Здесь можно настроить формат чисел, добавить или удалить десятичные знаки, добавить разделитель 1,000 или управлять отрицательными числами

Разумеется, вы также можете использовать ленту или опции Format Cells, описанные выше, чтобы преобразовать число в текст, или текст в валюту, время или любой другой формат по вашему желанию

Применение форматирования чисел с помощью функции вставки в Excel

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

Эта операция применяет формат скопированной ячейки ко всем выделенным ячейкам, даже к текстовым

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

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

Отделение чисел от текста

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

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

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

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

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

Этот метод немного громоздкий, но очень хорошо работает с небольшими наборами данных. Здесь мы предполагаем, что пробел разделяет число и текст, хотя этот метод работает и для любого другого разделителя

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

=LEFT(A1, SEARCH(' ', A1, 1))

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

Как видите, теперь все числа изолированы, и мы можем ими манипулировать. Хотите также выделить текст? Мы можем использовать функцию RIGHT таким же образом:

=RIGHT(A1, LEN(A1)-SEARCH(' ', A1, 1))

Это возвращает X символов из правой части ячейки, где x – общая длина ячейки минус количество символов слева от пробела

Теперь вы можете также манипулировать текстом. Хотите объединить их снова? Просто используйте функцию CONCATENATE со всеми ячейками в качестве входных данных:

=CONCATENATE(E1, F1)

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

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

А что если нет разделителя, разделяющего число и текст?

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

=LEFT(A1,SUM(LEN(A1)-LEN(SUBSTITUTE(A1,{'0','1','2','3','4','5','6','7','8','9'},''))))

 

=RIGHT(A1,SUM(LEN(A1)-LEN(SUBSTITUTE(A1,{'0','1','2','3','4','5','6','7','8','9'},''))))

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

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

=MIN(SEARCH({0,1,2,3,4,5,6,7,8,9},A1&'0123456789'))

 

=RIGHT(A1, LEN(A1)-B1+1)

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

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

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

Например, я нашел сообщение на форуме, где кто-то хотел извлечь цифры из строки типа ’45t*&65/’, чтобы в итоге получить ‘4565’. Другой автор привел следующую формулу как один из способов сделать это:

=SUMPRODUCT(MID(0&A1,LARGE(INDEX(ISNUMBER(--MID(A1,ROW($1:$25),1))*

ROW($1:$25),0),ROW($1:$25))+1,1)*10^ROW($1:$25)/10)

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

Хотите получить еще несколько советов по Excel? Вот как копировать формулы в Excel

Об авторе

Алексей Белоусов

Привет, меня зовут Филипп. Я фрилансер энтузиаст . В свободное время занимаюсь переводом статей и пишу о потребительских технологиях для широкого круга изданий , не переставая питать большую страсть ко всему мобильному =)

Комментировать

Оставить комментарий