В Excel очень легко разделить имя, отчество и фамилию, которые появляются в одном столбце, на отдельные столбцы, используя разные способы.
Предположим, вы получили список контактов, в котором все имена указаны в виде полных имен в одном столбце, и вам, вероятно, потребуется разделить имя, отчество и фамилию и разделить их на отдельные столбцы. Существует несколько различных способов разделения имен - с помощью функции «Текст в столбцы», Flash-заливки и формул.
В Excel очень легко разделить имена из одного столбца на два или несколько столбцов. В этой статье мы покажем вам, как разбить имена на разные столбцы в Excel разными способами.
Как разделить имена в Excel
Есть три простых способа разделить имена в Excel. В зависимости от структуры данных и того, хотите ли вы, чтобы имена разделения были статическими или динамическими, выберите один из следующих методов:
- Разделить имена с помощью функции текста в столбцы
- Разделяйте имена с помощью формул
- Разделяйте имена с помощью Flash Fill
Разделение имен с помощью мастера текста для столбца
Мастер преобразования текста в столбец - один из наиболее распространенных способов разделения имени и фамилии в Excel, а также отчества. Убедитесь, что рядом с именами, которые вы собираетесь разделить, есть пустые столбцы, потому что полные имена будут разделены на отдельные столбцы.
Например, у вас есть приведенный ниже набор данных с полными именами, и вы хотите разделить / разделить имя и фамилию и сохранить их в отдельные ячейки.
Сначала выделите столбец с полными именами, которые вы хотите разделить. Затем перейдите на вкладку «Данные» и нажмите «Текст в столбцы» в разделе «Инструменты для работы с данными».
Откроется мастер преобразования текста в столбцы. На первом шаге мастера выберите вариант «С разделителями» и нажмите «Далее».
На шаге 2 из 3 мастера преобразования текста в столбцы выберите разделитель, разделяющий ваши данные, снимите все остальные флажки и нажмите «Далее». В нашем случае «пробел» разделяет имя и фамилию, поэтому мы выбираем этот разделитель.
Раздел предварительного просмотра данных ниже в окне показывает, как анализируются ваши имена.
На шаге 3 из 3 вы выбираете формат данных и место назначения и нажимаете «Готово».
Обычно значение по умолчанию «Общие» отлично подходит для большинства типов данных. В поле «Место назначения» укажите место, где вы хотите отображать вывод. Вы должны указать адрес первой ячейки в столбце, в котором вы хотите получить результаты (в нашем случае B2).
Помните, что если вы не укажете целевую ячейку, мастер перезапишет исходные данные, поэтому обязательно выберите пустой столбец.
После того, как вы нажмете кнопку «Готово», полные имена сразу же разделятся на два отдельных столбца (Имя и Фамилия).
Выполните те же действия, если у вас есть имя, отчество и фамилия, и ваши имена будут разделены на три столбца вместо двух.
Примечание: Результат этого метода статичен. Это означает, что если вы измените исходное имя, вам нужно будет сделать это снова, чтобы разделить имена.
Разделить имена, разделенные запятыми
Если имя и фамилия разделены запятыми, выполните следующие действия, чтобы удалить запятую и разделить имя и фамилию.
В следующем примере имена отформатированы в обратном формате (Фамилия, Имя), где сначала идет фамилия, за которой следует запятая, а затем имя.
Выберите имена и перейдите в Data -> Text to Column. На шаге 1 выберите «Разделитель» и нажмите «Далее». На шаге 2 в разделе «Разделители» установите флажок «Запятая» (,) в качестве разделителя, поскольку ваши имена разделяются запятой.
На последнем шаге вы выбираете формат данных «Общий», указываете место назначения и нажимаете «Готово».
Теперь у вас будут имена в отдельных столбцах.
Разделяйте имена с помощью формул
Мастер преобразования текста в столбцы позволяет быстро и легко разделять имена. Однако, если вы хотите отредактировать исходные имена и хотите, чтобы динамический метод обновлялся автоматически каждый раз, когда вы меняете имена, то правильным выбором будет разделение имен с помощью формул. Вы можете использовать функции LEFT, RIGHT, MID, LEN и SEARCH или FIND для разделения имен.
Разделите имя и фамилию в Excel с помощью формул
Получите имя
Допустим, у вас есть набор данных ниже, и вы хотите выделить имя в отдельную ячейку. Вам нужно объединить функции НАЙТИ и ВЛЕВО в одну формулу, чтобы получить имя.
Используйте следующую формулу, чтобы получить имя:
= ЛЕВЫЙ (A2; НАЙТИ (""; A2) -1)Эта формула использует функцию НАЙТИ, чтобы найти положение символа пробела («») между именем и фамилией, и вычитает 1, чтобы исключить сам пробел. Этот номер затем передается в функцию LEFT, которая использует этот номер позиции для извлечения всего текста перед ним. Вы также можете использовать функцию ПОИСК вместо функции НАЙТИ.
После ввода формулы в пустую ячейку (B2) перетащите маркер заполнения вниз в другие ячейки, чтобы применить эту формулу, и все имена будут разделены в столбец B, как показано ниже:
Вы можете вложить функции ПОИСК и НАЙТИ внутрь функции LEFT, чтобы извлечь имя. Основное различие между этими двумя функциями заключается в том, что FIND чувствителен к регистру, а SEARCH - без учета регистра.
Если у вас есть запятая (,) вместо пробела между именем и фамилией, используйте запятую в качестве первого аргумента в функции НАЙТИ:
= ЛЕВЫЙ (A2; НАЙТИ (";"; A2) -1)Получить фамилию
Теперь, если вам нужно извлечь фамилию, воспользуйтесь функцией ВПРАВО. Следующая формула извлечет фамилию из того же набора данных:
= ВПРАВО (A2; LEN (A2) -FIND (""; A2))Формула сначала находит позицию символа пробела, вычитает это число из общей длины строки (которая возвращается функцией LEN), и это число затем передается в функцию RIGHT, чтобы извлечь такое количество символов из конца строки. (название).
Разделите имя, отчество и фамилию в Excel с помощью формул
Для разделения имен, включающих отчество, нужны разные формулы, в зависимости от используемого формата имени.
Чтобы получить имя если у вас есть отчество или отчество, используйте ту же формулу LEFT FIND, с которой вы уже знакомы.
Получить фамилию
Вышеупомянутая формула ПРАВО НАЙТИ хорошо работает, когда есть только имя и фамилия, она не будет иметь большого смысла, если ваши исходные имена содержат отчество или отчество. Это потому, что вы не учли два пробела в имени.
Чтобы получить фамилию, когда у вас есть отчество, используйте эту формулу:
= ВПРАВО (A2; LEN (A2) -ПОИСК (""; A2; ПОИСК (""; A2,1) +1))Чтобы извлечь фамилию, сначала определите позицию второго символа пробела с помощью вложенных функций ПОИСК, добавьте 1 к ПОИСКУ (”“, A2,1), чтобы начать извлечение со следующего символа. Затем вычтите позицию 2-го пробела из общей длины строки и получите длину фамилии в качестве числа результата. Затем передайте полученное число функции ВПРАВО, чтобы извлечь количество символов из конца строки.
Получите отчество
Функция MID использует три аргумента: первый аргумент указывает текст или адрес ячейки, второй указывает начальную позицию, а последний аргумент сообщает количество символов для извлечения среднего имени из этой позиции.
Синтаксис:
= MID (текст; начальное_число; число_знаков)Чтобы получить отчество, введите эту формулу в пустую ячейку:
= MID (A2; ПОИСК (""; A2) + 1; ПОИСК (""; A2, ПОИСК (""; A2) +1) -ПОИСК ("", A2) -1)Давайте посмотрим, как работает эта сложная формула:
Чтобы извлечь отчество или инициал отчества, необходимо определить положение обоих пробелов в полном имени. Чтобы найти позицию первого символа пробела, введите это ПОИСК (""; A2) в аргументе ‘start_num’ и добавьте 1, чтобы начать извлечение со следующего символа.
Затем, чтобы узнать длину отчества, поставьте это ПОИСК (""; A2; ПОИСК (""; A2) +1) -ПОИСК ("", A2) -1 вложенная функция в аргументе «num_chars», которая вычитает позицию 1-го пробела из положения 2-го пробела и вычитает 1 из результата, чтобы удалить конечный пробел. Окончательный результат покажет вам, сколько символов нужно извлечь.
Теперь функция MID с начальной позицией отчества и числовых символов для извлечения отделяет отчество от полного имени (A2).
Разделение имен в Excel с помощью Flash-заливки
Флэш-заливка в основном используется для автоматического заполнения данных определенного рисунка. Его также можно использовать для разделения имен. Он доступен только в Excel 2013, 2016, 2019 и 365.
Допустим, у вас есть приведенный ниже набор данных, и вы хотите получить только первые имена из полных имен.
В ячейке рядом с исходным именем введите имя. В этом случае введите «Стив» в ячейку B2.
Затем начните вводить имя во второй ячейке столбца. Если во время набора текста Excel распознает узор, Flash Fill автоматически покажет вам список имен в других ячейках (серым цветом).
Когда вы видите список имен серым цветом и если эти имена верны, просто нажмите клавишу «Enter», и Flash Fill автоматически заполнит оставшуюся часть столбца именами.
Выполните ту же процедуру, чтобы выделить фамилии в отдельный столбец.
Результат:
Flash Fill работает, обнаруживая шаблон в этих данных и следуя этому шаблону, предоставляя вам измененные данные. Сначала, когда вы вводите имя в первую ячейку, Flash Fill не распознает узор. Но когда вы снова начинаете вводить имя во второй ячейке, Flash Fill распознает шаблон и показывает вам предложение по разделению имен. Затем просто нажмите клавишу «Enter».
Как правило, функция Flash Fill включена по умолчанию. Если это не работает в вашем Excel, после ввода первого имени в первой ячейке вы можете просто выбрать вторую ячейку и нажать кнопку «Flash Fill» в группе Data Tools на вкладке «Data».
Кроме того, вы также можете нажать «Ctrl» + «E», чтобы получить те же результаты.
Теперь остальные ячейки будут заполнены именами.
Иногда вы можете не увидеть предложенный шаблон серым цветом, в этом случае вы можете использовать дескриптор заполнения, чтобы получить результат Flash Fill.
Сначала введите имена вручную в две ячейки и выберите обе эти ячейки. Затем наведите курсор на правый нижний угол выделения. Вы заметите, что курсор изменится с маленького зеленого квадрата (значок заполнителя) на значок плюса.
Затем дважды щелкните значок плюса. Это заполнит остальные ячейки. На этом этапе результаты неверны, вы увидите, что оба имени повторяются снова и снова. Затем в правом нижнем углу полученных данных вы увидите небольшой значок автозаполнения, как показано ниже. Нажмите на этот значок «Автозаполнение» и выберите «Flash Fill».
Это заполнит имена во всех ячейках:
Удалить отчество
Вы также можете использовать инструмент Flash Fill, чтобы избавиться от отчества из полного имени.
Например, предположим, что у вас есть набор данных ниже, и вы хотите получить только имя и фамилию без отчества или инициала отчества.
Чтобы получить имена без отчества или инициалов, введите вручную «Лорд Старк» в соседней ячейке. Затем во второй соседней ячейке введите «Дейенерис Таргариен». Пока вы набираете текст, Flash Fill распознает узор и покажет вам список имен без отчества (серым цветом).
Если предложение верное, нажмите клавишу «Enter», и Flash Fill автоматически заполнит остальные ячейки именами без отчества.
Если вы хотите получить только отчество без имени и фамилии, введите отчество в первые две ячейки и используйте инструмент Flash Fill, чтобы получить отчество из всех полных имен в столбце.
В этом руководстве показано, как разделять имена при работе с текстовыми данными. Эти методы также могут быть полезны при работе с другими формами данных, такими как адреса, названия продуктов, торговые марки и т. Д.