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

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

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

Как разделить ячейки в Excel

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

  • Использование функции Text to Column.
  • Использование текстовых функций Excel.
  • Использование Flash Fill (доступно в 2013 и 2016 годах).

Давайте начнем!

Разделить ячейки в Excel с помощью текста в столбец

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

Вот шаги, чтобы разделить эти имена на имя и фамилию:

  • Выделите ячейки, в которых есть текст, который вы хотите разделить (в данном случае A2: A7).
  • Перейдите на вкладку «Данные».
  • В группе «Инструменты для работы с данными» нажмите «Текст в столбцы».
  • В мастере преобразования текста в столбцы:
    • Шаг 1 из 3 мастера преобразования текста в столбцы : убедитесь, что выбран параметр «С разделителями» (это выбор по умолчанию). Это позволит вам разделить имя и фамилию на основе указанного разделителя (в данном случае пробела).
    • Щелкните Далее.
    • Шаг 2 из 3 Мастер преобразования текста в столбцы : выберите «Пробел» в качестве разделителя и отмените выбор всего остального. Вы можете увидеть, как будет выглядеть ваш результат, в разделе «Предварительный просмотр данных» диалогового окна.
    • Щелкните Далее.
    • Шаг 3 из 3 Мастер преобразования текста в столбцы : на этом шаге вы можете указать формат данных и место, где вы хотите получить результат. Я буду использовать общий формат данных, так как у меня есть текстовые данные для разделения. Назначением по умолчанию является A2, и если вы продолжите, он заменит исходный набор данных. Если вы хотите сохранить исходные данные без изменений, выберите другую ячейку в качестве места назначения. В этом случае выбирается B2.
    • Щелкните Готово.

Это мгновенно разделит текст ячейки на два разных столбца.

Примечание:

  • Функция Text to Column разделяет содержимое ячеек на основе разделителя. Хотя это хорошо работает, если вы хотите разделить имя и фамилию, в случае имени, отчества и фамилии он разделит их на три части.
  • Результат, который вы получаете от использования функции Text to Column, является статическим. Это означает, что если есть какие-либо изменения в исходных данных, вам придется повторить процесс, чтобы получить обновленные результаты.

Разделение ячеек в Excel с помощью текстовых функций

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

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

Разделение имен, содержащих имя и фамилию

Предположим, у вас есть такие же данные, как показано ниже:

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

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

=LEFT(A2,SEARCH(" ",A2)-1)

Эта формула обнаружит первый пробел, а затем вернет весь текст перед этим пробелом:

Эта формула использует функцию SEARCH (ПОИСК), чтобы получить позицию символа пробела. В случае с Брюсом Уэйном пробел находится на 6-й позиции. Затем он извлекает все символы слева от него с помощью функции LEFT.

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

Аналогично, чтобы получить фамилию, используйте следующую формулу:

=RIGHT(A2,LEN(A2)-SEARCH(" ",A2))

Эта формула использует функцию поиска, чтобы найти положение пробела с помощью функции ПОИСК. Затем он вычитает это число из общей длины имени (которая задается функцией LEN). Это дает количество символов в фамилии.

Эта фамилия затем извлекается с помощью функции RIGHT (ВПРАВО).

Примечание. Эти функции могут не работать, если в именах есть начальные, конечные или двойные пробелы. Щелкните здесь, чтобы узнать, как удалить начальные / конечные / двойные пробелы в Excel.

Разделение имен, содержащих Имя, Отчество и Фамилию

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

Формула в таких случаях немного сложна.

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

Чтобы получить имя:

=LEFT(A2,SEARCH(" ",A2)-1)

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

Извлечение отчества

Чтобы получить отчество:

=IFERROR(MID(A2,SEARCH(" ",A2)+1,SEARCH(" ",A2,SEARCH(" ",A2)+1)-SEARCH(" ",A2)),"")

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

В случаях, когда отчество отсутствует, функция MID возвращает ошибку. Чтобы избежать ошибки, он заключен в функцию IFERROR.

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

Чтобы получить фамилию, используйте следующую формулу:

=IF(LEN(A2)-LEN(SUBSTITUTE(A2," ",""))=1,RIGHT(A2,LEN(A2)-SEARCH(" ",A2)),RIGHT(A2,LEN(A2)-SEARCH(" ",A2,SEARCH(" ",A2)+1)))

Эта формула проверяет, есть ли отчество (путем подсчета количества пробелов). Если есть только 1 пробел, он просто возвращает весь текст справа от пробела.

Но если их 2, то он определяет второй пробел и возвращает количество символов после второго пробела.

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

Разделение ячеек в Excel с помощью Flash Fill

Flash Fill — это новая функция, представленная в Excel 2013.

Это может быть очень удобно, когда у вас есть узор, и вы хотите быстро извлечь его часть.

Например, возьмем данные имени и фамилии:

Флэш-заливка работает, идентифицируя шаблоны и реплицируя их для всех остальных ячеек.

Вот как вы можете извлечь первое имя из списка с помощью Flash Fill:

  • В ячейке B2 введите имя Брюса Уэйна (т. Е. Брюс).
  • Выделив ячейку, вы заметите небольшой квадрат в правом конце выделенной ячейки. Дважды щелкните по нему. Это заполнит одно и то же имя во всех ячейках.
  • Когда ячейки будут заполнены, в правом нижнем углу вы увидите значок параметров автозаполнения. Нажмите здесь.
  • Выберите из списка Flash Fill.
  • Как только вы выберете Flash Fill, вы заметите, что все ячейки обновляются и теперь показывают имя для каждого имени.

Как работает Flash Fill?

Flash Fill ищет шаблоны в наборе данных и воспроизводит их.

Flash Fill — это удивительно умная функция, которая в большинстве случаев работает должным образом. Но в некоторых случаях это тоже не удается.

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

Если в таком случае я извлечу отчество, Flash Fill по ошибке вернет фамилию, если имя отсутствует.

Если честно, это все еще хорошее приближение к тенденции. Однако это не то, что я хотел.

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

Вот еще один пример блестящей работы Flash Fill.

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

Чтобы быстро получить город, введите название города для первого адреса (в этом примере введите Лондон в ячейку B2) и используйте автозаполнение, чтобы заполнить все ячейки. Теперь используйте Flash Fill, и вы сразу же получите название города по каждому адресу.

Точно так же вы можете разделить адрес и извлечь любую часть адреса.

Обратите внимание, что для этого потребуется, чтобы адрес был однородным набором данных с одним и тем же разделителем (в данном случае запятая).

Если вы попытаетесь использовать Flash Fill при отсутствии шаблона, появится сообщение об ошибке, как показано ниже:

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

Надеюсь, вы нашли это руководство по Excel полезным.

Как разделить ячейки в Excel (разделить на несколько столбцов)