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

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

• Если вы не хотите импортировать какой-либо столбец, отображаемый в предварительном просмотре данных, выберите этот столбец и установите переключатель Не импортировать столбец (пропускать) в разделе Формат данных столбца.
• Невозможно импортировать разделенные данные в другую таблицу или книгу. Если вы попытаетесь сделать это, вы получите сообщение об ошибке недопустимого адресата.

Как разделить текст фиксированной ширины

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

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

Поскольку все идентификаторы продуктов содержат 9 символов, вариант с фиксированной шириной идеально подходит для этой работы:

1. Запустите мастер Преобразовать текст в столбцы, как описано в приведенном выше примере. На первом шаге мастера выберите «Фиксированная ширина» и нажмите Далее.
   
2. Установите ширину каждого столбца в разделе предварительного просмотра данных. Как показано на снимке экрана ниже, вертикальная линия представляет собой разрыв столбца, и для создания новой строки разрыва достаточно щелкнуть в нужном месте (в нашем случае 9 символов):
   
   Чтобы удалить разрыв , дважды щелкните строку; чтобы переместить разрыв в другое место, просто перетащите линию мышью.
3. На следующем шаге выберите формат данных и место назначения для разделенных ячеек точно так же, как в предыдущем примере, и нажмите кнопку Готово, чтобы завершить разделение.

Как разделить ячейки Excel с помощью Fill Flash

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

Возьмем столбец данных из нашего первого примера и посмотрим, как Flash Fill в Excel может помочь нам разделить ячейку пополам:




1. Вставьте новый столбец рядом со столбцом с исходными данными и введите нужную часть текста в первую ячейку (в данном примере имя участника).
2. Введите текст еще в пару ячеек. Как только Excel обнаружит шаблон, он автоматически заполнит аналогичные данные в других ячейках. В нашем случае Excel потребовалось 3 ячейки, чтобы вычислить шаблон:
   
3. Если вас устраивает то, что вы видите, нажмите клавишу Ввод, и все имена будут сразу скопированы в отдельный столбец.

Как разделить ячейку в Excel с помощью формул

Какую бы разнообразную информацию ни содержали ваши ячейки, формула разделения ячейки в Excel сводится к поиску положения разделителя (запятая, пробел и т. д.) и извлечению подстроки до, после или между разделителями. Как правило, для определения положения разделителя используются функции ПОИСК или НАЙТИ, а для получения подстроки — одну из текстовых функций (ЛЕВАЯ, ПРАВАЯ или СРЕДНЯЯ).

Например, вы можете использовать следующие формулы для разделения данных в ячейке A2, разделенных запятой и пробелом (см. снимок экрана ниже):

Чтобы извлечь имя из B2:
=LEFT(A2, SEARCH(",",A2)-1)

Чтобы извлечь страну из C2:
=RIGHT(A2,LEN(A2)-SEARCH(",",A2)-1)




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

Разделить ячейки с помощью функции «Разделить текст»

Теперь, когда вы знакомы со встроенными функциями, позвольте мне показать вам альтернативный способ разделения ячеек в Excel. Я имею в виду инструмент «Разделить текст», входящий в состав Ultimate Suite for Excel. Он может выполнять следующие операции:

• Разделить ячейку по символу
• Разделить ячейку по строке
• Разделить ячейку по маске (шаблону)

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




1. Выберите ячейки, которые хотите разделить, и нажмите значок "Разделить текст" на вкладке Данные Ablebits в разделе Текст
   
2. На панели Разделить текст настройте следующие параметры:
   • В разделе Разделить по символу выберите в качестве разделителей запятую и пробел.
   • Установите флажок Рассматривать последовательные разделители как один.
   • В разделе "Выбор способа разделения ячеек" выберите "Разделить на столбцы".
   • Нажмите кнопку "Разделить".




Готово! Четыре новых столбца с разделенными данными вставляются между исходными столбцами, и вам нужно только дать этим столбцам соответствующие имена:


• Инструмент «Разделить текст» предоставляет еще два параметра для разделения ячейки в Excel. Дополнительные сведения см. в разделах Разделение ячеек по строке и Разделение ячеек по маске.
• Чтобы разделить столбец имен на имя, фамилию и отчество, используйте специальный инструмент "Разделить имена".

Если вам интересно увидеть инструменты «Разделить текст» и «Разделить имена» в действии, мы приглашаем вас загрузить 14-дневную пробную версию нашего Ultimate Suite для Excel.
Я благодарю вас за чтение и надеюсь увидеть вас в нашем блоге на следующей неделе!

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




В этом примере вы хотите разделить имена и числа из столбца A на столбцы B и C. Это можно сделать тремя способами.

Вариант 1. Быстрое заполнение

Мгновенная заливка — это функция, представленная в Excel 2013. Если у вас более старая версия или версия для Mac, вы не сможете ее использовать.

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

Чтобы использовать Flash Fill, вам сначала нужно предоставить ему пример того, что вы пытаетесь сделать, как показано выше. Предоставив пример, нажмите:
Главная > Редактирование > Заливка > Мгновенная заливка

Для этого также можно использовать сочетание клавиш +.




Flash Fill просматривает ваш пример, выясняет, что вы пытаетесь сделать, и автоматически извлекает все остальные имена.

Вы можете повторить тот же процесс для столбца C, и все имена и номера будут извлечены!




Мгновенная заливка более подробно описана в нашем курсе "Основные навыки" наряду с некоторыми более сложными методами мгновенной заливки.

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

Вариант 2. Текст в столбцы

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

В этом случае Текст в столбцы можно использовать для разделения данных, используя пробел в качестве разделителя. Это приведет к 3 столбцам, а не к двум, которые вы хотите, поэтому вам нужно будет затем объединить имена вместе.

Вы можете получить доступ к инструменту Текст в столбцы, щелкнув: Данные > Работа с данными > Текст в столбцы

Использование Text to Columns подробно рассматривается в нашем курсе Expert Skills.

Вариант 3. Использование формул

Предыдущие варианты предлагают только одноразовое решение. Если бы данные в столбце A поступали из внешнего источника данных, вам не нужно было бы повторно запускать Flash Fill или Text to Columns каждый раз, когда вы обновляете данные.

Имена и числа можно извлечь с помощью формул, но для этого вам потребуются очень сложные формулы.

Поиск позиции первого числа

Первое, что нужно сделать вашей формуле, — найти позицию первого числа в ячейке. Формула для этого:

Это выглядит устрашающе, но на самом деле это не так уж сложно понять, если вы разбираетесь в функции НАЙТИ. Программа FIND подробно описана в нашем курсе "Экспертные навыки".

Числа в фигурных скобках также могут сбивать с толку. Это называется «массивом». Это означает, что функция НАЙТИ будет выполняться 10 раз, по одному разу для каждого числа. Затем функция MIN вернет наименьшее возвращаемое значение (т. е. позицию первого числа в ячейке).

Причина, по которой вы объединяете текст "0123456789", заключается в том, что функция НАЙТИ вернет ошибку, если не найдет то, что ищет, и это предотвратит работу функции МИН. способный вернуть результат. Чтобы убедиться, что FIND всегда может возвращать правильный результат при каждом запуске, вы добавляете все числа в конец искомого текста.

Извлечение имен

Теперь, когда у вас есть позиция первого числа, вы можете использовать функцию LEFT для извлечения имен следующим образом:

=ЛЕВОЕ(A2, МИН(НАЙТИ(,A2&"0123456789″)) -1)

Функция LEFT подробно описана в нашем курсе Expert Skills.

Извлечение чисел

Очень похожим образом числа могут быть извлечены с помощью функций RIGHT и LEN следующим образом:

=ВПРАВО(A2,ДЛСТР(A2)-МИН(НАЙТИ(,A2&"0123456789″)) +1)

Как и в случае с ЛЕВОЙ функцией, функции ПРАВАЯ и ДЛСТРИЯ рассматриваются в нашем курсе повышения квалификации.

Результат

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

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










Это единственные современные книги Excel, изданные в настоящее время и включающие новые функции динамических массивов.

Это также единственные книги, которые научат вас абсолютно всем навыкам работы с Excel, включая Power Pivot, OLAP и DAX.

Некоторые вещи, которые вы узнаете











































Поделиться этой статьей

Последние статьи

Как разделить данные с разделителями в Excel

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




Сочетание клавиш для автозаполнения в Excel

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




Формула Excel для проверки наличия в ячейке текста

Иногда вам нужно знать, содержит ли ячейка конкретный текст или какой-либо другой. В этой статье показано, как это сделать с помощью функций SEARCH, ISNUMBER и FIND.

12 ответов

Исходя из приведенного выше совета, он не может разделять текст и числа, когда числа находятся перед текстом, например. 12345abcde. Есть еще советы?

Все приведенные выше решения должны работать с цифрами перед текстом.

Для подхода на основе формул разница будет заключаться в том, что вам нужно будет искать последнее число, а не первое. Вы можете сделать это, используя функцию MAX вместо функции MIN.
После того, как вы узнали расположение последнего числа, вы сможете использовать функцию ВЛЕВО для извлечения всех чисел.

НА МЕНЯ ЭТО НА САМОМ ДЕЛЕ НЕ РАБОТАЕТ.

Если вы можете уточнить, что вы пытаетесь сделать и почему это не работает, я смогу дать вам совет.

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

Это распространенная проблема на веб-сайтах: статьи написаны с помощью программного обеспечения для обработки текстов, которое заменяет двойные кавычки, набираемые с клавиатуры, более удобными (в Word это называется использованием умных кавычек), предназначенных для публикации. А если этого не происходит, или корректно исправлено потом, то любая обработка при загрузке на сайт тоже может это делать.

У меня также есть ситуация, когда мне нужно разделить числа и текст в одной ячейке, где числа находятся впереди. Я пробовал использовать функцию MAX, и она у меня не работает. Какую формулу я мог бы использовать для создания результата ниже?

Если два кода имеют одинаковую длину, вы сможете легко сделать это с помощью Flash Fill или Text To Columns, как упоминалось выше. Если вам нужно решение на основе формулы, а коды всегда имеют одинаковую длину, вы также сможете легко сделать это, используя функции ВЛЕВО и ВПРАВО. Только в тех случаях, когда коды могут иметь переменную длину, вам понадобится формула, которая ищет позицию последней цифры или первой буквы кода.

Решение MAX будет работать правильно только в том случае, если все числа от 0 до 9 могут быть найдены в цели, поэтому на самом деле это может быть не лучший подход в подобной ситуации. Существуют формулы, которые могут надежно извлекать последнее число из значения без какого-либо предварительного знания того, какие числа могут присутствовать, но они намного сложнее.

Более простым решением в этом случае было бы найти первую букву вместо последней цифры.Предполагая, что значение находится в ячейке A2, вы можете сделать это, используя:
=MIN(FIND(,A2&”ABCDEFGHIJKLMNOPQRSTUVWXYZ”))-1

Это работает точно так же, как приведенная выше формула, но функция НАЙТИ выполняется 26 раз (по одному разу для каждой буквы алфавита) и возвращает наименьший результат. Затем вы можете извлечь значения с каждой стороны, используя функции ВЛЕВО, ВПРАВО и ДЛСТР.

Теперь, когда вы извлекли обе части значения, вы можете соединить их вместе с пробелом между ними, используя оператор &, например:
=B2&” “&C2

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

Ячейка: A1
a)63Ra b)64Ra c)65Ra d)62Ra e)61Ra f)63Ra g)60Ra h)62Ra

Я бы порекомендовал сначала разбить каждый элемент на отдельные ячейки с помощью функции «Быстрая заливка» или «Текст в столбцы». Вы можете увидеть, как использовать мгновенное заполнение выше, а текст в столбцы рассматривается в этой статье.

На наших курсах подробно рассматриваются оба навыка.

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

Большое спасибо

=MIN(FIND(,A2&”0123456789″))
мне ничего не помогает
я пытался заменить ” и ; и т.д. даже набрал вручную новый, а он не работает.
в строке всегда просто написано
=MIN(FIND(,A2&”0123456789″))
вместо отображения результата.
Есть мысли, как это исправить?

Я только что проверил это, и у меня это отлично сработало. С текстом «Джордж Барнс 197,2″ в ячейке A2 формула: =MIN(НАЙТИ(,A2&”0123456789”)) вернула 15, как и ожидалось. Если вы копируете и вставляете с веб-страницы, вам придется вручную повторно ввести двойные кавычки (кавычки веб-страницы отличаются от кавычек Excel), но вы сказали, что сделали это.

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

Читайте также:

  
• Навигационная программа для Android без интернета для туристов
  
• Фотошоп аварийно завершает работу при сохранении
  
• Код ошибки 1603 при установке AutoCAD 2020
  
• Где находится пипетка в PowerPoint
  
• Как скопировать ссылку на дискорд