Как разделить число в ячейке 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 на несколько столбцов (с помощью преобразования текста в столбцы, формул и быстрой заливки)

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

Рисунок 1. Разделение текста и чисел с помощью формулы

Настройка данных

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

Мы также введем должность, имя и дату рождения в ячейки B3, ячейки C3 и ячейки D3 соответственно.

Рисунок 2. Настройка данных

Основная формула

Мы можем использовать базовую формулу для разделения текста и чисел из ячейки в соответствии с функцией ВЛЕВО или ВПРАВО в соответствии с требованиями.

Определение положения ПЕРВОГО ЧИСЛА в ячейках A4 и A8

Мы вставим приведенную ниже формулу в ячейку B4.

Рисунок 3: Позиция первого числа

Рисунок 4: Позиция первого числа

Как показано на рис. 4, эта формула возвращает позицию первого числа в ячейке A4. JOHN состоит из четырех букв, следующее значение — не текст, а число, следовательно, позиция первого числа — 5
Мы будем использовать функцию раскрывающегося списка, чтобы получить позиции первого числа для оставшихся четырех учащихся.

Рисунок 5: Позиция первого числа

Извлечение текста с помощью функции LEFT

Мы будем использовать приведенную ниже формулу для извлечения текста (имен) из столбца A:

Мы вставим эту формулу в ячейку C4 и воспользуемся функцией раскрывающегося списка, чтобы заполнить оставшиеся ячейки.

Рис. 6. Разделение текста и чисел с помощью функции ВЛЕВО

Извлечение чисел с помощью функции ПРАВИЛЬНО

Мы будем использовать приведенную ниже формулу для извлечения чисел (даты рождения) из столбца А:

=ВПРАВО(A4,ДЛСТР(A4)-B4+1)

Мы вставим эту формулу в ячейку D4 и воспользуемся функцией раскрывающегося списка, чтобы заполнить оставшиеся ячейки.

Рис. 7. Разделение текста и чисел с помощью функции RIGHT

Мгновенное подключение к эксперту через нашу службу Excelchat

В большинстве случаев проблема, которую вам нужно решить, будет более сложной, чем простое применение формулы или функции. Если вы хотите сэкономить часы исследований и разочарований, попробуйте наш онлайн-сервис Excelchat! Наши эксперты по Excel доступны круглосуточно и без выходных, чтобы ответить на любой вопрос, который у вас может возникнуть. Мы гарантируем подключение в течение 30 секунд и индивидуальное решение в течение 20 минут.

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

Разделить ячейку

Используйте следующий прием, чтобы "разделить" ячейку в Excel.

<р>1. Например, задача Б начинается в 13:00 и требует для выполнения 2 часов.

Предположим, задача Б начинается в 13:30.Мы хотели бы разделить ячейку B3 и раскрасить правую половину.

<р>2. Выберите столбец C.

<р>3. Щелкните правой кнопкой мыши и выберите "Вставить".

<р>4. Ширина столбца по умолчанию составляет 64 пикселя. Измените ширину столбцов B и C на 32 пикселя.

<р>5. Выберите ячейку B1 и ячейку C1.

<р>6. На вкладке "Главная" в группе "Выравнивание" нажмите стрелку вниз рядом с элементом "Объединить и центрировать" и выберите "Объединить ячейки".

<р>7. Повторите шаги 5–6 для ячеек B2 и C2 (а также для ячеек B4 и C4).

<р>8. Измените цвет фона ячейки B3 на «Без заливки».

Примечание: предположим, что задача А заканчивается в 15:30. Используйте трюк, описанный выше, чтобы «разделить» ячейку E2. Загрузите файл Excel и попробуйте.

Текст в столбцы

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

<р>1. Выберите диапазон с полными именами.

<р>2. На вкладке "Данные" в группе "Инструменты данных" нажмите "Текст в столбцы".

Появится следующее диалоговое окно.

<р>3. Выберите «С разделителями» и нажмите «Далее».

<р>4. Снимите все флажки в разделе "Разделители", кроме флажка "Запятая и пробел".

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

Быстрая заливка

Вам нравится Магия? Вместо использования мастера «Текст в столбцы» используйте мгновенное заполнение, чтобы быстро разделить данные на несколько столбцов.

<р>1. Сначала разделите содержимое одной ячейки на несколько ячеек.

<р>2. Выделите ячейку B1 и нажмите CTRL + E (быстрое заполнение).

<р>3. Выберите ячейку C1 и нажмите CTRL + E.

<р>4. Выберите ячейку D1 и нажмите CTRL + E.

Примечание. Мгновенное заполнение в Excel работает, только если оно распознает шаблон. Загрузите файл Excel и попробуйте. Посетите нашу страницу о Flash Fill, чтобы узнать больше об этом замечательном инструменте Excel.

Формулы для разделения ячеек

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

<р>1. Приведенная ниже формула возвращает имя.

<р>2. Приведенная ниже формула возвращает фамилию.

<р>3. Выберите диапазон B2:C2 и перетащите его вниз.

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

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