В некоторых случаях необходимо разделить ячейки в Excel.Это может быть получение данных из базы данных, копирование данных из Интернета или получение данных от коллеги.
Простой пример, когда вам нужно разделить ячейки в Excel, — это когда у вас есть полные имена и вы хотите разделить их на имя и фамилию.
Или вы получаете адрес, и вы хотите разделить адрес, чтобы вы могли анализировать город или пароль отдельно.
Как разделить ячейки в Excel
В этом уроке вы узнаете, как разделить ячейки в Excel, используя следующие методы:
- Используйте функцию «Текст в столбцы».
- Используйте текстовые функции Excel.
- Используйте мгновенное заполнение (доступно в 2013 и 2016 годах).
Давайте начнем!
Разделить ячейки в Excel с текстом в столбец
Ниже я перечислил имена некоторых из моих любимых вымышленных персонажей, которые я хотел бы разделить на отдельные ячейки. :
Вот шаги, чтобы разделить эти имена на имена и фамилии:
- Выберите ячейку, содержащую текст, который вы хотите разделить (в данном случае A2: A7).
- Перейдите на вкладку Данные
- В группе «Инструменты данных» нажмите «Текст в столбцы».
- В мастере преобразования текста в столбцы:
Это немедленно разделит текст ячейки на два разных столбца.
Примечания:
- Функция «Текст в столбцы» разбивает содержимое ячеек на основе разделителей.Хотя это хорошо работает, если вы хотите разделить имя и фамилию, в случае с именем, отчеством и фамилией он разделит его на три части.
- Результат, полученный с помощью функции Text to Column, является статическим.Это означает, что если в исходных данных есть какие-либо изменения, вам придется повторить процесс, чтобы получить обновленные результаты.
Разделить ячейки в Excel с помощью текстовых функций
Текстовые функции Excel полезны, когда вы хотите нарезать и нарезать текстовые строки.
В то время как функция «Текст в столбец» обеспечивает статические результаты, результаты, полученные с помощью этой функции, являются динамическими и автоматически обновляются при изменении исходных данных.
Разделить имя на имя и фамилию
Предположим, у вас есть такие же данные:
извлечь имя
Чтобы получить имена из этого списка, используйте следующую формулу:
=ВЛЕВО(A2,ПОИСК(" ",A2)-1)
Эта формула найдет первый символ пробела, а затем вернет весь текст до этого символа пробела:
Эта формула использует функцию ПОИСК, чтобы получить положение символа пробела.В примере Брюса Уэйна космический персонаж стоит на 6-м месте.Затем он использует функцию LEFT для извлечения всех символов слева от него.
Извлечь фамилию
Точно так же, чтобы получить фамилию, используйте следующую формулу:
=ВПРАВО(A2,ДЛСТР(A2)-ПОИСК(" ",A2))
Эта формула использует функцию ПОИСК, чтобы найти положение пробела с помощью функции ПОИСК.Затем он вычитает это число из общей длины имени (задаваемой функцией ДЛСТР).Это дает количество символов в фамилии.
Затем используйте функцию ПРАВИЛЬНО, чтобы извлечь эту фамилию.
Примечание:Эти функции могут работать неправильно, если в имени есть начальные, конечные или двойные пробелы.Нажмите здесь, чтобы узнать, как удалить начальные/конечные/двойные пробелы в Excel.
Разделить имена на имя, отчество и фамилию
В некоторых случаях вы можете получить комбинацию имен, некоторые из которых также имеют отчества.
Формула в этом случае немного сложна.
извлечь имя
Чтобы получить имя:
=ВЛЕВО(A2,ПОИСК(" ",A2)-1)
Это та же самая формула, которую мы используем без отчества.Он просто ищет первый символ пробела и возвращает все символы до пробела.
извлечь отчество
Чтобы получить отчество:
=ЕСЛИОШИБКА(СРЕДН(A2,ПОИСК(" ",A2)+1,ПОИСК(" ",A2,ПОИСК(" ",A2)+1)-ПОИСК(" ",A2)),"")
Функция MID начинается с первого символа пробела и использует разницу в положении первого и второго символов пробела для извлечения отчества.
Если отчество отсутствует, функция MID возвращает ошибку.Во избежание ошибок он заключен в функцию ЕСЛИОШИБКА.
Извлечь фамилию
Чтобы получить фамилию, используйте следующую формулу:
=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 с помощью QuickFill
Мгновенное заполнение — это новая функция, представленная в Excel 2013.
Это может быть очень удобно, когда у вас есть шаблон и вы хотите быстро извлечь его части.
Например, давайте получим данные имени и фамилии:
Quick Fill работает, распознавая шаблон и копируя его во все другие ячейки.
Вот как использовать Flash Fill для извлечения имен из списка:
Как работает мгновенное заполнение?
Flash Fill находит шаблон в наборе данных и копирует его.
Flash Fill — удивительно умная функция, которая отлично работает в большинстве случаев.Но и он в некоторых случаях дает сбой.
Например, если у меня есть список имен с комбинациями имен, у некоторых есть отчества, а у других нет.
Если в этом случае я извлеку отчество, Flash Fill неправильно вернет фамилию в случае отсутствия имени.
Честно говоря, это все еще хорошее приближение к тренду.Однако это не то, чего я хочу.
Но это все еще достаточно хороший инструмент, чтобы держать его в своем арсенале и использовать при необходимости.
Вот еще один пример быстрого заполнения, который отлично работает.
У меня есть набор адресов, из которых я хочу быстро извлечь города.
Чтобы быстро получить город, введите название города первого адреса (в этом примере Лондон в ячейке B2) и используйте автозаполнение, чтобы заполнить все ячейки.Теперь используйте Flash Fill, и он мгновенно даст вам название города для каждого адреса.
Точно так же вы можете разделить адрес и извлечь любую часть адреса.
Обратите внимание, что для этого требуется, чтобы адреса представляли собой однородный набор данных с одним и тем же разделителем (в данном случае запятой).
Если вы попытаетесь использовать Flash Fill без шаблона, появится сообщение об ошибке, подобное этому:
В этом уроке я рассмотрел три различных способа разделения ячеек в Excel на несколько столбцов (используя «Текст в столбцы», «Формулы» и «Быстрое заполнение»).
Я надеюсь, что этот учебник по Excel был вам полезен.
