Разделить содержимое ячеек Excel на разные ячейки
Обновлено: 20.11.2024
Возможны ситуации, когда вам нужно разделить ячейки в 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?
Предположим, что у вас есть список текстовых строк, необходимых для разделения каждой строки на несколько ячеек столбца с помощью запятых, как показано на снимке экрана ниже. Есть ли у вас какие-нибудь быстрые приемы, чтобы справиться с этим? Теперь в этой статье я могу познакомить вас с приемами, которые помогут вам быстро сделать это в Excel.
Разделите содержимое одной ячейки на столбцы/строки разделителем с помощью Kutools for Excel
В Excel есть функция "Текст в столбцы", которая поможет вам решить эту проблему.
<р>1. Выберите ячейки, которые хотите разделить, и щелкните Данные > Текст в столбцы. Смотрите скриншот:
<р>2. На шаге 1 мастера «Текст в столбцы» установите флажок «С разделителями» и нажмите «Далее», чтобы продолжить. Смотрите скриншот:
<р>3. На шаге 2 мастера укажите разделитель, на основе которого вы хотите разделить, в разделе «Разделители», и вы сможете просмотреть строки, разделенные указанным разделителем. В нашем случае мы проверяем только запятую и можем просмотреть каждую строку, разделенную на три столбца. Смотрите скриншот:
<р>4. Нажмите «Далее», чтобы перейти к последнему шагу мастера, и выберите диапазон назначения для выходного содержимого. Смотрите скриншот:
<р>5. Нажмите «Готово», и теперь каждая строка разделена запятой на три ячейки столбца. Смотрите скриншот:
Если вы хотите решить эту проблему гораздо более простым способом, вы можете попробовать использовать Kutools for Excel's Split Cells утилита, которая может быстро разделить содержимое одной ячейки на несколько столбцов или строк с помощью разделителя.
После установки Kutools for Excel сделайте следующее: (Бесплатно загрузите Kutools for Excel сейчас!)
<р>1. Выберите ячейки, которые вы хотите разделить, и нажмите Kutools > Объединить и разделить > Разделить ячейки. Смотрите скриншот:
<р>2. В диалоговом окне «Разделить ячейки» сначала выберите нужный тип разделения, а затем отметьте разделитель, на основе которого необходимо разделить ячейки. В нашем случае мы отмечаем «Разделить на столбцы» в разделе «Тип», отмечаем «Другое» и вводим , в поле позади. Смотрите скриншот:
<р>3. Затем нажмите «ОК» и выберите ячейку для размещения разделенных ячеек. Смотрите скриншот:
<р>4. Нажмите OK, и теперь все строки в каждой ячейке разделены на ячейки столбцов.
Совет. Если вы установите флажок «Разделить на строки» в диалоговом окне «Разделить ячейки», разделенные результаты будут показаны, как показано ниже на снимках экрана:
Если вы хотите разделить строки по определенной длине текста, вы также можете использовать Kutools for Excel Функция разделения ячеек.
После установки Kutools for Excel сделайте следующее: (Бесплатно загрузите Kutools for Excel сейчас!)
Предположим, что у вас есть список числовых строк, и вы хотите разделить числа на отдельные ячейки, как показано на снимке экрана ниже:
<р>2. В диалоговом окне «Разделить ячейки» установите флажок «Тип разделения», который вам нужен, а затем нажмите «Указать ширину» и введите длину, на основе которой вы хотите разделить, в следующее текстовое поле. Смотрите скриншот:
<р>3. Нажмите «ОК», выберите ячейку назначения, чтобы поместить результат, и нажмите «ОК». Теперь каждое число разделено на ячейки.
Если у вас есть текст в одной ячейке, разделенный каким-либо символом (пробел, табуляция, точка с запятой и т. д.), его можно разделить на несколько строк с помощью функций "Текст в столбцы" и "Транспонирование данных" в Excel. Допустим, у вас есть список товаров, разделенных точкой с запятой, как показано на рисунке ниже, и вы хотите разбить список на строки.
<р>1. Сначала разделите значения из ячейки B1 на столбцы на основе разделителя. Выберите текстовую ячейку (B1) и на ленте выберите Данные > Текст в столбцы.
<р>2. На шаге 1 мастера преобразования текста в столбцы оставьте тип файла по умолчанию (с разделителями) и нажмите "Далее".
<р>3. На шаге 2 установите флажок «Точка с запятой» в разделе «Разделители» и нажмите «Далее». В предварительном просмотре данных вы можете увидеть, как эти данные будут разделены.
Другими возможными разделителями являются табуляция, запятая, пробел и определяемый пользователем разделитель.
<р>4. На последнем шаге оставьте формат данных по умолчанию (Общий) и нажмите Готово.
Здесь вы также можете выбрать другие типы данных, например текст или дату. Вы также можете выбрать столбцы по отдельности в предварительном просмотре данных и изменить их типы данных.
Для пункта назначения оставьте исходную ячейку, чтобы разделить данные на столбцы, начиная с ячейки B1.
В результате этой части текст из ячейки B1 разбивается на столбцы B–F с каждой точкой с запятой.
<р>5. Теперь используйте функцию «Транспонировать данные», чтобы перенести значения из строки 1 в столбец B. Начните с ячейки B1 (чтобы текущее значение B1 осталось там) и транспонируйте ячейки C1: F1.
Для этого выберите и щелкните правой кнопкой мыши ячейки, которые необходимо транспонировать (C1:F1), затем нажмите "Копировать" (или используйте сочетание клавиш CTRL + C).
<р>6. Выберите и щелкните правой кнопкой мыши ячейку, в которой вы хотите начать транспонированные данные (B2), и выберите «Транспонировать» в разделе «Параметры вставки».
Наконец, значения из ячейки B1 теперь переносятся в столбец B, ячейки B1:B6. Исходный список разбит на строки.
Разделить несколько текстовых ячеек на строки
Если у вас есть данные в нескольких ячейках, как показано на рисунке ниже, вы также можете разбить текст на строки почти так же, как в случае с одной ячейкой выше.
Выполните следующие действия, чтобы разделить текст в формате B1:B3 на столбцы:
<р>1. Сначала разделите значения из ячеек B1:B3 на столбцы на основе разделителя. Выберите текстовые ячейки, которые нужно разделить (B1:B3), и на ленте выберите Данные > Текст в столбцы.<р>2. На шаге 1 мастера преобразования текста в столбцы оставьте тип файла по умолчанию (с разделителями) и нажмите "Далее".
<р>3. На шаге 2 установите флажок "Точка с запятой" в разделе "Разделители" и нажмите "Далее".
<р>4. На последнем шаге оставьте формат данных по умолчанию (Общий) и нажмите Готово.
В результате этой части текст из ячеек B1:B3 разбивается по точкам с запятой на столбцы B–F.
<р>5. Теперь перенесите значения из строк 1–3 в столбцы B–D. Поскольку данные находятся в нескольких строках, вы должны перенести их в новый диапазон (а затем удалить исходный диапазон данных).
Выделите и щелкните правой кнопкой мыши ячейки, которые нужно транспонировать (B1:F3), и нажмите "Копировать" (или используйте сочетание клавиш CTRL + C).
<р>6. Выберите и щелкните правой кнопкой мыши начальную ячейку для транспонированных данных (B5) и выберите «Транспонировать» в разделе «Параметры вставки».
Наконец, значения из ячеек B1:B3 теперь переносятся в столбцы B–D, ячейки B5:D9. Вы можете удалить исходные данные из ячеек B1:F3.
Разделить текстовую ячейку на строки в Google Таблицах
Вы также можете использовать функции "Разделить текст на столбцы" и "Транспонировать", чтобы разделить текстовую ячейку на несколько строк в Google Таблицах.
<р>1. Сначала щелкните правой кнопкой мыши ячейку, содержащую текст (B1), и в меню выберите Данные > Разделить текст на столбцы.<р>2. Нажмите появившуюся кнопку "Разделитель" и выберите "Точка с запятой".
В результате этого шага текст из B1 теперь разделен на столбцы B:F на основе разделителя с запятой.
<р>3. Теперь вы можете использовать функцию транспонирования данных для переноса значений из строки 1 в столбец B. Начните с ячейки B1 (чтобы значение B1 осталось там) и транспонируйте ячейки C1:F1.
Для этого выберите и щелкните правой кнопкой мыши ячейки, которые необходимо транспонировать (C1:F1), и нажмите "Копировать" (или используйте сочетание клавиш CTRL + C).
<р>4. Выберите и щелкните правой кнопкой мыши ячейку, из которой вы хотите транспонировать данные (B2), выберите «Специальная вставка», затем «Вставить транспонированные».
Наконец, значения из ячейки B1 теперь переносятся в столбец B, ячейки B1:B6. Исходный список разбит на строки.
Разделить несколько текстовых ячеек на строки в Google Таблицах
Теперь вы можете разделить несколько текстовых ячеек на несколько строк, используя один и тот же пример.
<р>1. Выберите и щелкните правой кнопкой мыши ячейки, содержащие текст (B1:B3), и в меню выберите Данные > Разделить текст на столбцы.<р>2. Нажмите появившуюся кнопку "Разделитель" и выберите "Точка с запятой".
В результате этого шага текст из ячеек B1:B3 разбивается по точкам с запятой на столбцы B–F.
<р>3. Теперь перенесите данные из строк 1–3 в столбцы B–D. Поскольку данные находятся в нескольких строках, вы должны перенести их в новый диапазон (а затем удалить исходный диапазон данных).
Выделите и щелкните правой кнопкой мыши ячейки, которые нужно транспонировать (B1:F3), и нажмите "Копировать" (или используйте сочетание клавиш CTRL + C).
<р>4. Выберите и щелкните правой кнопкой мыши начальную ячейку для транспонированных данных (B5), выберите "Специальная вставка" и нажмите "Вставить транспонированные".
Наконец, значения из ячеек B1:B3 теперь переносятся в столбцы B–D, ячейки B5:D9. Вы можете удалить исходные данные из ячеек B1:F3.
Вывод: узнайте, как использовать формулы и функции в Excel для разделения полных имен на столбцы с именами и фамилиями.
Уровень квалификации: средний
Посмотреть обучающее видео
Загрузить файлы Excel
Вы можете скачать файлы до и после ниже. Файл «до» предназначен для того, чтобы вы могли следить за ним, а файл «после» включает все уже написанные формулы.
Разделить-имена-с-формулами-BEGIN.xlsx
Разделение имен с формулами-FINAL.xlsx
Разделение текста на отдельные столбцы
Мы говорили о различных способах разделить текст из одного столбца на два. В частности, мы рассмотрели распространенный пример разделения столбца «Полное имя» на «Имя» и «Фамилия».
Использование 4 функций для построения наших формул
Чтобы разделить столбец "Полное имя" на "Имя" и "Фамилия" с помощью формул, нам нужно использовать четыре разные функции. Мы будем использовать SEARCH и LEFT, чтобы вытащить имя. Затем мы будем использовать LEN и RIGHT, чтобы получить фамилию.
Функция ПОИСК
Ключом к отделению имен от фамилий является то, что Excel определяет, что общего у всех полных имен. Этим общим фактором является пробел, разделяющий два имени. Чтобы помочь нашей формуле идентифицировать все, что находится слева от этого символа пробела, как имя, нам нужно использовать функцию ПОИСК.
Функция ПОИСК возвращает номер символа, под которым найден определенный символ или текстовая строка, читаемая слева направо. Другими словами, под каким номером стоит пробел в строке символов, составляющих полное имя? В моем имени, Джон Акампора, пробел — это четвертый символ (после J, o и n), поэтому функция ПОИСК возвращает число 4.
Для ПОИСКА есть три аргумента.
- Первый аргумент функции ПОИСК – найти_текст. Текст, который мы хотим найти в наших записях, представляет собой символ пробела. Итак, для find_text мы вводим ” “, обязательно включив кавычки.
- Второй аргумент — внутри_текста. Это текст, в котором мы ищем пробел. Это будет ячейка с полным именем.В нашем примере первой ячейкой с полным именем является A2. Поскольку мы работаем с таблицами Excel, формула будет скопирована и изменена на B2, C2 и т. д. для каждой соответствующей строки.
- Третий и последний аргумент — [start_num]. Этот аргумент предназначен для случаев, когда вы хотите игнорировать определенное количество символов в тексте перед началом поиска. В нашем случае мы хотим искать по всему тексту, начиная с самого первого символа, поэтому нам не нужно определять этот аргумент.
Все вместе наша формула выглядит следующим образом: =ПОИСК(" ",A2)
Я начал с функции ПОИСК, потому что она будет использоваться в качестве одного из аргументов для следующей функции, которую мы собираемся рассмотреть. Это ЛЕВАЯ функция,
Функция LEFT
Функция ВЛЕВО возвращает указанное количество символов от начала текстовой строки. Чтобы указать это число, мы будем использовать значение, которое мы только что определили с помощью функции ПОИСК. Функция ВЛЕВО вытянет буквы слева от столбца Полное имя.
Функция LEFT имеет два аргумента.
- Первый аргумент — это текст. Это просто ячейка, из которой извлекается функция — в нашем случае A2.
- Второй аргумент — [num_chars]. Это количество символов, которое должна извлечь функция. Для этого аргумента мы будем использовать формулу, которую мы создали выше, и вычтем из нее 1, потому что мы не хотим фактически включать символ пробела в наши результаты. Так что для нашего примера этот аргумент будет SEARCH(" ",A2)-1
Все вместе наша формула выглядит следующим образом: =ЛЕВЫЙ(A2,ПОИСК(" ",A2)-1)
Теперь, когда мы извлекли имя с помощью функции ВЛЕВО, вы можете догадаться, как мы будем использовать функцию ВПРАВО. Он вытащит фамилию. Но прежде чем мы начнем, позвольте мне объяснить один из компонентов, который нам понадобится для этой формулы. Это функция ДЛСТР.
Функция ДЛСТР
LEN означает ДЛИНА. Эта функция возвращает количество символов в текстовой строке. В моем имени 12 символов: 3 для Джона, 8 для Акампоры и 1 для пробела между ними.
Существует только один аргумент для LEN, и он указывает, из какого текста следует считать символы. В нашем примере мы снова используем A2 для полного имени. Наша формула просто =LEN(A2)
ПРАВИЛЬНАЯ функция
Формула ПРАВИЛЬНО возвращает указанное количество символов с конца текстовой строки. RIGHT имеет два аргумента.
- Первый аргумент — это текст. Это текст, который он просматривает, чтобы вернуть правильные символы. Как и в случае с функцией ВЛЕВО выше, мы смотрим на ячейку A2.
- Второй аргумент — [num_chars]. Для этого аргумента мы хотим вычесть количество символов, которые мы определили с помощью функции ПОИСК, из общего количества символов, которые мы определили с помощью функции ДЛСТР. Это даст нам количество символов в фамилии.
Наша формула, вместе взятая, равна =ПРАВИЛЬНО(A2,ДЛСТР(A2)-ПОИСК(" ",A2))
Обратите внимание, что мы не вычли 1, как делали раньше, потому что мы хотим, чтобы пробел был включен в число, которое вычитается из общей длины.
Плюсы и минусы использования формул для разделения ячеек
Единственным выдающимся преимуществом этого метода разделения текста являются автоматические обновления. При изменении, добавлении или удалении столбца «Полное имя» имя и фамилия также меняются. Это большое преимущество по сравнению с использованием текста в столбцы, которое требует от вас полного повторения процесса при внесении изменений. И даже метод Power Query, который намного проще в обновлении, чем Text to Columns, по-прежнему требует обновления вручную.
Конечно, один очевидный недостаток этого метода заключается в том, что, несмотря на то, что каждая из четырех функций относительно проста для понимания, требуется некоторое время и размышления, чтобы объединить их все и создать формулы, которые работают правильно. Другими словами, это не самое простое решение для реализации из трех представленных методов.
Еще один недостаток, который следует учитывать, заключается в том, что это работает только для сценариев с двумя именами в столбце "Полное имя". Если у вас есть данные, включающие в себя отчества или два имени, метод формул бесполезен без значительных изменений формул. (Домашнее задание! Если вы хотите попробовать, пожалуйста, сделайте это и сообщите нам о своих результатах в комментариях.) Как мы видели в руководстве по Power Query, у вас действительно есть возможность получить больше, чем два имени с этой техникой.
Способы разделения текста
Вот ссылки на другие сообщения о способах разделения текста:
Заключение
Я надеюсь, что вы узнали что-то новое из этого поста, и это будет полезно при разделении данных на отдельные столбцы. Если у вас есть вопросы или замечания, оставьте комментарий ниже!
Читайте также: