Содержание
Microsoft Excel отлично работает как с числами, так и с текстом
— но если вы используете оба в одной ячейке, вы можете столкнуться с некоторыми трудностями. К счастью, вы можете извлечь числа или текст из ячеек для более эффективной работы с вашими данными. Мы демонстрируем несколько вариантов, в зависимости от формата, в котором находятся ваши данные.
Числа, хранящиеся как текст
Это обычная ситуация, и, к счастью, с ней очень легко справиться. Иногда ячейки, содержащие только цифры, неправильно помечаются как текстовые, что не позволяет Microsoft Excel использовать их в операциях. На изображении ниже вы можете видеть, что ячейки в столбце A хранятся в виде текста, о чем свидетельствует зеленый флажок на каждой ячейке, а также поле формата, оба из которых выделены ниже.
Преобразовать в число
Чтобы решить эту проблему, просто щелкните в ячейке, нажмите на поле с предупреждением и выберите «Преобразовать в число». Вот и все! Это можно сделать для нескольких ячеек, выбрав их все и щелкнув по полю с предупреждением, но это, вероятно, не так эффективно, как в следующем методе.
Текст в столбцы
Если у вас много ячеек, которые необходимо исправить, использование этого метода может занять колоссальное количество времени. Чтобы ускорить процесс, вы можете использовать Microsoft Excel Text to Columns
функция. Выберите числа, которые вы хотите конвертировать, перейдите на Данные> Текст в столбцы, и используйте мастер, чтобы убедиться, что числа выходят правильно (по большей части, вам просто нужно нажать следующий а также Конец не возиться ни с одной из настроек).
Текст будет преобразован в числа, и вы готовы к работе. Обратите внимание, что это работает только для одного столбца за раз, поэтому, если у вас много столбцов, вы можете использовать этот последний метод.
Специальная паста
Чтобы этот метод работал, вам нужно ввести число 1 в ячейку (важно, чтобы оно было в числовом формате). Выберите эту ячейку и скопируйте ее. Теперь выберите все числа, которые вы хотите преобразовать в числовой формат, и нажмите Edit> Paste Special. Выберите «Умножить» в разделе «Операция» и нажмите «ОК».
Все выбранные вами номера будут преобразованы в общий формат, который распознает номера. Любые текстовые ячейки также будут преобразованы в общий формат, что не должно вызывать проблем.
Извлечение чисел или текста из ячеек смешанного формата
Теперь мы переходим к самой сложной части: извлечению чисел из ячеек, которые содержат несколько форматов ввода. Если у вас есть число и единица измерения (например, «7 лопат», как у нас ниже), вы столкнетесь с этой проблемой. Чтобы решить эту проблему, мы рассмотрим несколько различных способов разделения ячеек на числа и текст, что позволит вам работать с каждой из них в отдельности. Первый метод немного громоздок, но очень хорошо работает с небольшими наборами данных.
ЛЕВО ПРАВО ПОИСК
Основная функция, которую мы будем здесь использовать, это LEFT, которая возвращает самые левые символы из ячейки. Как вы можете видеть в нашем наборе данных выше, у нас есть ячейки с одно-, двух- и трехсимвольными числами, поэтому нам нужно будет вернуть самый левый один, два или три символа из ячеек. Сочетая ВЛЕВО с функцией ПОИСК
, мы можем вернуть все слева от места. Вот функция:
[NOEDIT]
=LEFT(A1, SEARCH(" ", A1, 1))[/NOEDIT]
Это вернет все слева от пространства. Использование ручки заполнения
чтобы применить формулу к остальным ячейкам, вот что мы получаем (вы можете увидеть формулу в функциональной панели в верхней части изображения):
Как видите, теперь у нас есть все изолированные числа, поэтому ими можно манипулировать. Хотите также выделить текст? Мы можем использовать функцию RIGHT таким же образом:
[NOEDIT]
=RIGHT(A1, LEN(A1)-SEARCH(" ", A1, 1))[/NOEDIT]
Это возвращает x символов справа от ячейки, где x — общая длина ячейки минус количество символов слева от пробела.
Теперь текстом можно манипулировать. Хотите объединить их снова? Просто используйте функцию CONCATENATE со всеми ячейками в качестве входных данных:
[NOEDIT]
=CONCATENATE(E1, F1)
[/NOEDIT]
Очевидно, что этот метод работает лучше всего, если у вас есть только цифры и единицы, и ничего больше. Если у вас есть другие форматы ячеек, вам, возможно, придется проявить творческий подход к формулам, чтобы все работало правильно. Если у вас есть гигантский набор данных, это будет стоить времени, которое понадобится для выяснения формулы!
Текст в столбцы
Функция «Текст в столбцы» полезна для столбцов, содержащих только цифры, но она также может упростить жизнь, если у вас есть ячейки смешанного формата. Выберите столбец, с которым хотите работать, и нажмите Данные> Текст в столбцы. Затем вы можете использовать мастер, чтобы выбрать разделитель (пробел, как правило, лучший) и разделить столбец так, как вы хотите.
Если у вас есть только одно- и двузначные числа, опция «Фиксированная ширина» также может быть полезна, поскольку она будет разделять только первые два или три символа ячейки (вы можете создать несколько разбиений, если хотите, но я сохраню полное объяснение разбиения по фиксированной ширине для другой статьи).
Если ваш набор данных содержит много столбцов, и вы не хотите использовать текст в столбцах для каждого из них, вы можете легко получить тот же эффект, используя быстрый экспорт и импорт. Сначала экспортируйте вашу электронную таблицу в виде файла значений, разделенных запятыми (CSV). Нажмите Файл> Сохранить как… и сохраните ваш файл в формате CSV.
Теперь откройте новую электронную таблицу и нажмите Файл> Импорт… Выберите файл CSV и используйте мастер импорта, чтобы разбить данные на два столбца (вы будете использовать те же шаги, что и в мастере «Текст в столбцы»). Выбрав «Пробел» в разделе «Разделители», вы скажете Microsoft Excel разделить данные, где бы они ни находились, чтобы изолировать цифры и текст.
Когда закончите, нажмите «Готово», и вы получите новую электронную таблицу, в которой столбцы разделены на две части. Конечно, если у вас в ячейке более одного пробела, вы получите более двух столбцов, как вы можете видеть здесь:
К сожалению, нет хорошего решения для этого с использованием этого метода; вам просто нужно соединить ячейки вместе.
Более сложные ситуации
Используя приведенные выше стратегии, вы сможете извлечь числа или текст из большинства ячеек смешанного формата, которые доставляют вам неприятности. Даже если они этого не делают, вы можете объединить их с некоторыми мощными текстовыми функциями, включенными в Microsoft Excel, чтобы получить символы, которые вы ищете. Однако, есть гораздо более сложные ситуации, которые требуют более сложных решений.
Например, я нашел сообщение на форуме, где кто-то хотел извлечь числа из строки, например, «45t * 65 /», чтобы в итоге он получил «4565». Другой автор дал следующую формулу в качестве одного из способов сделать это:
[NOEDIT]
=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)
[/NOEDIT]
Честно говоря, я понятия не имею, как это работает. Но, согласно сообщению на форуме, он будет извлекать цифры из сложной цепочки цифр и других символов. Дело в том, что при достаточном времени, терпении и усилиях вы можете извлечь цифры и текст практически из всего! Вам просто нужно найти нужные ресурсы
,
Какую стратегию вы используете?
Теперь, когда у вас есть лучшее представление о том, как извлечь цифры и буквы из ячеек смешанного формата, мы хотим знать, какие другие стратегии вы использовали для того же. Опубликуйте свои сумасшедшие формулы Excel, процессы или что-нибудь еще, что может быть полезным в комментариях, чтобы мы все могли учиться на них!
