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

Обновлено: 21.11.2024

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

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

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

Использование собственных функций
Использование комбинации функций Excel
Энергетический запрос
Visual Basic для приложений (VBA)

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

Для приведенных ниже примеров мы импортировали данные из разных источников, таких как Airtable, Google Sheets и BigQuery, с помощью Coupler.io. Это мощное решение для передачи данных в Excel из нескольких приложений и веб-API без написания кода. Основное преимущество заключается в том, что вы можете синхронизировать книгу с источником данных по индивидуальному расписанию!

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

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

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

Откроется мастер, в котором вам нужно будет выполнить три шага:

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

Разделить текст в ячейке Excel на несколько ячеек по пробелу

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

В результате мы разделили текст в ячейке Excel на несколько ячеек (20!) пробелами!

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

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

В этом случае разделителем является символ: @ . Итак, нам нужно указать это.

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

Что делать, если ваша ячейка содержит несколько строк, например адрес, и вы хотите, чтобы каждая строка находилась в отдельной ячейке?

Чтобы разделить эту строку, необходимо указать разрыв строки в качестве разделителя. Для этого выберите в качестве разделителя Другое и нажмите Ctrl + J.

Вот результат:

Как разделить объединенные ячейки в Excel

Разделение объединенных ячеек — это несколько иная концепция, поскольку она не влияет на содержимое ячеек. Это легко сделать с помощью кнопки "Объединить и центрировать".

Выберите объединенные ячейки, откройте раскрывающийся список и нажмите "Разъединить ячейки".

Разделить ячейки на строки в Excel

К сожалению, в Excel нет отдельной кнопки или функции для разделения ячейки на строки или вертикального разделения ячеек. Тем не менее сделать это можно с помощью функций Excel или Power Query. Но сначала ознакомьтесь с этим простым методом разделения ячеек на строки.

Легко разделить ячейку Excel на две строки

Вы должны сделать два шага:

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

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

Возьмем предыдущий пример разделения полного имени: Margeaux Kuhnke .

Нам понадобится две формулы для каждой строки:

Эта формула извлекает строку перед указанным разделителем: НАЙТИ(" ",ячейка) .

Эта формула извлекает строку после указанного разделителя.

Если у вас есть два или более разделителя, обе формулы будут работать только с первым разделителем, например:

Если по какой-то причине вам нужно разделить ячейку вторым или третьим разделителем, вам нужно обновить формулу НАЙТИ, учитывая следующую логику:

starting_position — укажите начальную позицию (символ), чтобы пропустить ненужные разделители.

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

Разделить ячейки в Excel на несколько строк

К сожалению, эти формулы не применимы к случаю «Пабло Пикассо», где количество разделителей равно 19! В таких случаях лучше работает следующая формула:

Это преобразует содержимое ячейки в строку XML, а затем извлекает из нее данные.Эта формула должна выполняться как формула массива; то есть вам нужно выбрать ячейку, на которую нужно разделить, и нажать Ctrl+Shift+Enter. Вот как это работает с полным именем Пабло Пикассо:

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

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

Как динамически разделить ячейки на строки или столбцы в Excel без формул

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

Теперь каждое значение, добавленное в столбец A, будет разделено на строки по пробелу. Просто нажмите «Обновить», чтобы увидеть разделенные значения.

С помощью Power Query вы также можете разделить ячейки на столбцы с помощью разных разделителей.

Бонус: как разделить ячейки по диагонали в Excel

Под диагональным разделением пользователи обычно подразумевают пересечение ячейки с диагональной границей следующим образом:

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

Есть ли другие варианты разделения ячеек в Excel?

В Excel нет специальной функции для разделения данных, такой как SPLIT в Google Sheets. Однако, используя Excel VBA, вы можете создать пользовательскую функцию VBA для решения конкретных задач разделения. Для этого требуется знание Microsoft Visual Basic для приложений.

Однако в большинстве случаев описанные выше варианты подойдут. Удачи в разделении ваших данных!

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

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

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

Это руководство охватывает:

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

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

Использование функции "Текст в столбцы".
Использование текстовых функций Excel.
Использование быстрой заливки (доступно в 2013 и 2016 годах).

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

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

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

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

Примечание.

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

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

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

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

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

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

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

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

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

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

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

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

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

Затем эта фамилия извлекается с помощью функции RIGHT.

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

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

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

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

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

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

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

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

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

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

В случае отсутствия отчества функция MID возвращает ошибку. Чтобы избежать ошибки, она заключена в функцию ЕСЛИОШИБКА.

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

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

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

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

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

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

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

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

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

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

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

Как работает мгновенное заполнение?

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

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

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

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

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

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

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

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

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

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

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

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

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

При работе с данными и электронными таблицами большое значение имеют удобочитаемость и структура.

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

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

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

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

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

Лучший способ решить две вышеупомянутые проблемы – разбить один столбец на несколько столбцов.

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

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

Оглавление

Как разделить один столбец на несколько столбцов

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

Выберите столбец, который вы хотите разделить
На ленте данных выберите «Текст в столбцы» (в группе Инструменты данных). Откроется мастер преобразования текста в столбцы.
Здесь вы увидите параметр, позволяющий задать способ разделения данных в выбранных ячейках. Убедитесь, что этот параметр выбран. Если вы не знакомы с термином 'разделитель', это символ, указывающий, как данные в ячейках отделены друг от друга, например, имя и фамилия в каждой ячейке. разделены пробелом. Это означает, что разделителем здесь является символ пробела.
Нажмите "Далее".
Вот настройки, которые необходимо выполнить на втором этапе мастера Tetx to Columns:
- По умолчанию разделитель Tab установлен. Но мы не хотим этим пользоваться. Мы хотим использовать разделитель Пробел. Поэтому снимите флажок с разделителя Tab и установите флажок Пробел (1).
- Есть также флажок, который позволяет вам указать, хотите ли вы рассматривать последовательные разделители как один (2). Это означает, что если у вас по ошибке есть два пробела между именами, вы хотите рассматривать их как один пробел?
- Вы можете увидеть, как ваши данные будут выглядеть после разделения, в области предварительного просмотра данных (3) в нижней части диалогового окна. Обратите внимание, что при выборе пробела мы получаем именно тот результат, который нам нужен.
- Наконец нажмите «Далее» (4)
Теперь вы увидите параметр, в котором можно указать формат данных в столбцах. По умолчанию выбран параметр Общие, который гарантирует, что столбцы имеют тот же формат, что и исходные ячейки. Оставьте его с выбранным параметром Общие и нажмите "Готово".

Теперь у нас есть два столбца данных: имя в столбце A и фамилия в столбце B.

Важно отметить, что при разделении содержимого ячейки Excel не вставляет новые ячейки для хранения содержимого.

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

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

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

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

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

Как разделить несколько строк в ячейке на несколько ячеек

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

Возьмите, например, лист ниже.

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

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

Выберите столбец, который вы хотите разделить
На ленте данных выберите «Текст в столбцы» (в группе «Инструменты данных»). Откроется мастер преобразования текста в столбцы.
Здесь вы увидите параметр, позволяющий задать способ разделения данных в выбранных ячейках. Убедитесь, что этот параметр выбран.
Нажмите "Далее".
По умолчанию разделитель табуляции отмечен флажком. Снимите все отмеченные разделители и выберите вариант «Другое». В маленьком поле рядом с этой опцией вам нужно указать символ-разделитель, который вы хотите использовать. Если вы хотите указать символ разрыва строки. Нажмите Ctrl + J на клавиатуре. Это покажет крошечную мигающую точку внутри коробки. Это означает, что был вставлен разделитель разрыва строки.

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

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

Нажмите "Далее".
Теперь вы увидите параметр, в котором можно указать формат данных в столбцах. По умолчанию выбран параметр "Общие", который гарантирует, что столбцы имеют тот же формат, что и исходные ячейки.
Здесь мы также хотим, чтобы все столбцы отображались, начиная со столбца B, чтобы новые ячейки не перезаписывали существующий столбец. Рядом с местом назначения мы видим написанную ячейку «$ A $ 2». Мы можем изменить это, выбрав нужную ячейку назначения «$B$2» и нажав «Готово».
Вы можете получить диалоговое окно с вопросом, хотите ли вы заменить данные, которые уже присутствуют в целевых ячейках. Нажмите "ОК".

После этого вы найдете столбцы с B по E, каждый из которых содержит отдельный элемент адреса, который присутствовал в столбце A.

Как разделить объединенную ячейку

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

Нажмите объединенную ячейку. Если вы хотите разъединить несколько ячеек, выберите их все.
В разделе инструментов Выравнивание вкладки Главная вы увидите раскрывающийся список рядом с параметром Объединить и центрировать. Нажмите на стрелку раскрывающегося списка и выберите "Разъединить ячейки".
Это приведет к разделению объединенной ячейки на исходное количество ячеек.

Обратите внимание, что в Excel нет возможности разбить необъединенную ячейку на ячейки меньшего размера (как это возможно в MS Word).

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

Вышеупомянутые шаги были указаны при условии, что вы используете версии Excel с 2013 по 2019. Однако Microsoft продолжает обновлять свои меню, вкладки и параметры в каждой новой версии, которую она выпускает.

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

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

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

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

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

Есть два способа разделить ячейку Excel: с помощью разделителей или с использованием фиксированной ширины. Эти два параметра имеют небольшие различия.

Столбцы с разделителями и фиксированной ширины

При выборе параметра "Разделители" ваши данные будут разделены с помощью запятых, пробелов, точек, тире или других знаков форматирования, которые могут быть в одной ячейке. Например, если вы написали чье-то имя как "Смит, Джон", Смит и Джон будут разделены на отдельные столбцы.
Параметр «Фиксированная ширина» позволяет вручную выбрать, где Excel будет разделять данные. Например, если у вас есть список имен, вы можете указать Excel разделить данные так, чтобы в одном столбце была первая буква имени каждого, а во втором — остальная часть имени.

Как разделить ячейки на столбцы с помощью разделителя

Выберите диапазон ячеек, которые вы хотите разделить, и выберите «Текст в столбцы» на вкладке «Данные». Дэйв Джонсон/Инсайдер

Наиболее распространенным разделителем является запятая, но используйте тот (или те), которые подходят для ваших данных. Дэйв Джонсон/Инсайдер

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

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