разделите имя и фамилию на разные столбцы
В учебнике показано, как разделить имя и фамилию в Excel с помощью формул или текста в столбцы, а также как быстро разделить столбец имен в различных форматах на имя, фамилию и отчество, приветствия и суффиксы.
В Excel очень распространена ситуация, когда ваш рабочий лист содержит столбец с полными именами, и вы хотите разделить имя и фамилию на отдельные столбцы. Задачу можно выполнить несколькими способами — с помощью функции «Текст в столбцы», формул и инструмента «Разделить имена». Ниже вы найдете полную информацию о каждой технике.
Как разделить имена в Excel с текстом на столбцы
В ситуациях, когда у вас есть столбец имен одного и того же шаблона, например, только имя и фамилия или имя, отчество и фамилия, проще всего разделить их на отдельные столбцы следующим образом:
- Выберите столбец полных имен, которые вы хотите разделить.
- Направляйтесь к Данные вкладка > Инструменты данных группу и щелкните Текст в столбцы.
- На первом шаге Мастер преобразования текста в столбцывыберите параметр «С разделителями» и нажмите Следующий.
- На следующем шаге выберите один или несколько разделителей и нажмите Следующий.
В нашем случае разные части имен разделены пробелами, поэтому мы выбираем этот разделитель. Предварительный просмотр данных показывает, что все наши имена анализируются просто отлично.
Кончик. Если вы имеете дело с именами, разделенными запятой и пробелом, например Андерсон, Роннизатем проверьте Запятая а также Пространство ящики под Разделителии выберите Рассматривать последовательные разделители как один флажок (обычно установлен по умолчанию).
- На последнем шаге вы выбираете формат данных и место назначения и нажимаете Заканчивать.
По умолчанию Общий формат работает хорошо в большинстве случаев. Как Назначенияукажите самую верхнюю ячейку в столбце, в который вы хотите вывести результаты (имейте в виду, что это перезапишет все существующие данные, поэтому обязательно выберите пустой столбец).
Сделанный! Имя, отчество и фамилия разделены на отдельные столбцы:
Разделяйте имя и фамилию в Excel с помощью формул
Как вы только что видели, Текст в столбцы функция быстрая и простая. Однако, если вы планируете внести какие-либо изменения в исходные имена и ищете динамическое решение, которое будет обновляться автоматически, вам лучше разделить имена с помощью формул.
Как отделить имя и фамилию от полного имени с пробелом
Эти формулы охватывают наиболее типичный сценарий, когда у вас есть имя и фамилия в одном столбце, разделенные одним пробелом.
Формула для получения имени
Имя можно легко извлечь с помощью этой общей формулы:
ОСТАВИЛ(клеткаПОИСК(» «, клетка) — 1)
Вы используете функцию ПОИСК или НАЙТИ, чтобы получить положение символа пробела («») в ячейке, из которого вы вычитаете 1, чтобы исключить сам пробел. Это число передается функции ВЛЕВО как количество символов, которые необходимо извлечь, начиная с левой стороны строки.
Формула для получения фамилии
Общая формула для извлечения фамилии такова:
ПРАВИЛЬНО(клеткаЛЕН(клетка) — ПОИСК(» «, клетка))
В этой формуле вы также используете функцию ПОИСК, чтобы найти положение символа пробела, вычесть это число из общей длины строки (возвращенной функцией ДЛСТР) и получить функцию ПРАВИЛЬНО, чтобы извлечь столько символов из правой части строки. Струна.
С полным именем в ячейке A2 формулы выглядят следующим образом:
Получить имя:
=ВЛЕВО(A2,ПОИСК(» «,A2)-1)
Получить фамилию:
=ВПРАВО(A2,ДЛСТР(A2)-ПОИСК(» «,A2,1))
Вы вводите формулы в ячейки B2 и C2 соответственно и перетаскиваете маркер заполнения, чтобы скопировать формулы вниз по столбцам. Результат будет выглядеть примерно так:
Если некоторые из исходных имен содержат отчество или отчество, вам понадобится немного более сложная формула для извлечения фамилии:
=ПРАВО(A2, ДЛСТР(A2) — ПОИСК(«#», ПОДСТАВИТЬ(A2,» «, «#», ДЛСТР(A2) — ДЛСТР(ПОДСТАВИТЬ(A2, » «, «»))))))))))
Вот высокоуровневое объяснение логики формулы: вы заменяете последний пробел в имени знаком решетки (#) или любым другим символом, который не встречается ни в одном имени, и определяете позицию этого символа. После этого вы вычитаете указанное выше число из общей длины строки, чтобы получить длину фамилии, и функция ПРАВИЛЬНО извлекает столько символов.
Итак, вот как вы можете разделить имя и фамилию в Excel, когда некоторые из исходных имен включают отчество:
Как отделить имя и фамилию от имени запятой
Если у вас есть столбец имен в Фамилия Имя формат, вы можете разделить их на отдельные столбцы, используя следующие формулы.
Формула для извлечения имени
ПРАВИЛЬНО(клеткаЛЕН(клетка) — ПОИСК(» «, клетка))
Как и в приведенном выше примере, вы используете функцию ПОИСК, чтобы определить положение символа пробела, а затем вычитаете его из общей длины строки, чтобы получить длину имени. Этот номер идет непосредственно к num_chars аргумент функции ПРАВИЛЬНО, указывающий, сколько символов нужно извлечь из конца строки.
Формула для извлечения фамилии
ОСТАВИЛ(клеткаПОИСК(» «, клетка) — 2)
Чтобы получить фамилию, вы используете комбинацию ПОИСК ВЛЕВО, обсуждавшуюся в предыдущий пример с той разницей, что вы вычитаете 2 вместо 1, чтобы учесть два дополнительных символа, запятую и пробел.
С полным именем в ячейке A2 формулы принимают следующий вид:
Получить имя:
=ВПРАВО(A2, ДЛИН(A2) — ПОИСК(» «, A2))
Получить фамилию:
=ВЛЕВО(A2, ПОИСК(» «, A2) — 2)
На скриншоте ниже показаны результаты:
Как разделить полное имя на имя, фамилию и отчество
Разделение имен, содержащих отчество или отчество, требует немного разных подходов, в зависимости от формата имени.
Если ваши имена есть в Имя Второе Имя Фамилия формат, приведенные ниже формулы будут работать с удовольствием:
A B C D 1 Полное имя Имя Отчество Фамилия 2 Имя Отчество Фамилия =LEFT(A2,ПОИСК(» «, A2)-1) =MID(A2, ПОИСК(» «, A2) + 1, ПОИСК(» «, A2 , ПОИСК(» «, A2)+1) — ПОИСК(» «, A2)-1) =ВПРАВО(A2,LEN(A2) — ПОИСК(» «, A2, ПОИСК(» «, A2,1)+1 )) Результат: Дэвид Марк Уайт Дэвид Марк Уайт
Чтобы получить имя, вы используете уже знакомую формулу ЛЕВЫЙ ПОИСК.
Чтобы получить фамилию, определите позицию 2-го пробела с помощью вложенных функций ПОИСК, вычтите позицию из общей длины строки и получите в результате длину фамилии. Затем вы передаете вышеуказанное число функции RIGHT, инструктируя ее извлечь это количество символов из конца строки.
Чтобы извлечь отчество, нужно знать положение обоих пробелов в имени. Чтобы определить положение первого пробела, используйте простую функцию SEARCH(» «,A2), к которой вы добавляете 1, чтобы начать извлечение со следующего символа. Этот номер идет на start_num аргумент функции MID. Чтобы вычислить длину отчества, вы вычитаете положение 1-го пробела из положения 2-го пробела, вычитаете 1 из результата, чтобы избавиться от замыкающего пробела, и помещаете это число в num_chars аргумент MID, сообщающий, сколько символов нужно извлечь.
А вот и формулы для разделения имен Фамилия, Имя Отчество тип:
A B C D 1 Полное имя Имя Отчество Фамилия 2 Фамилия, Имя Отчество =MID(A2, ПОИСК(» «,A2) + 1, ПОИСК(» «, A2, ПОИСК(» «, A2) + 1) — ПОИСК( » «, A2) -1) =ВПРАВО(A2, ДЛИН(A2) — ПОИСК(» «, A2, ПОИСК(» «, A2, 1)+1)) = ВЛЕВО(A2, ПОИСК(» «,A2, 1)-2) Результат: Уайт, Дэвид Марк Дэвид Марк Уайт
Аналогичный подход можно использовать для разделения имен с суффиксами:
A B C D 1 Полное имя Имя Фамилия Суффикс 2 Имя Фамилия, Суффикс =LEFT(A2, ПОИСК(» «,A2)-1) =MID(A2, ПОИСК(» «,A2) + 1, ПОИСК(«,», A2) — ПОИСК(» «,A2)-1) =ПРАВО(A2, ДЛСТР(A2) — ПОИСК(» «, A2, ПОИСК(» «,A2)+1)) Результат: Роберт Фурлан-младший Роберт Фурлан младший
Вот как вы можете разделить имена в Excel, используя различные комбинации функций. Чтобы лучше понять и, возможно, перепроектировать формулы, вы можете загрузить наш образец рабочей тетради на Отдельные имена в Excel.
Кончик. В Excel 365 вы можете использовать функцию ТЕКСПЛИТ для разделения имен любым указанным разделителем.
Отдельное имя в Excel 2013, 2016 и 2019 с быстрой заливкой
Всем известно, что Flash Fill в Excel может быстро заполнять данные по определенному шаблону. Но знаете ли вы, что он также может разделять данные? Вот как:
- Добавьте новый столбец рядом со столбцом с исходными именами и введите часть имени, которую вы хотите извлечь, в первую ячейку (имя в этом примере).
- Начните вводить имя во второй ячейке. Если Excel обнаружит шаблон (в большинстве случаев это так), он автоматически заполнит имена во всех остальных ячейках.
- Все, что вам нужно сделать сейчас, это нажать клавишу Enter 🙂
Кончик. Обычно функция мгновенного заполнения включена по умолчанию. Если это не работает в Excel, нажмите кнопку «Быстрая заливка» на Данные вкладка > Инструменты данных группа. Если это все еще не работает, перейдите к Файл > Опциинажмите Передовойи убедитесь, что в разделе Параметры редактирования.
Простой или сложный, текст в столбцы, мгновенное заполнение и формулы хорошо работают только для однородных наборов данных, где все имена относятся к одному типу. Если вы имеете дело с разными форматами имен, описанные выше методы испортят ваши рабочие листы, поместив некоторые части имени в неправильные столбцы или вернув ошибки, например:
В таких ситуациях вы можете доверить работу нашему инструменту «Разделить имена», который отлично распознает имена, состоящие из нескольких частей, более 80 приветствий и около 30 различных суффиксов и без проблем работает во всех версиях Excel 2016 — Excel 2007.
С нашим Ultimate Suite, установленным в вашем Excel, столбец имен в различных форматах можно разделить на 2 простых шага:
- Выберите любую ячейку, содержащую имя, которое вы хотите разделить, и щелкните значок «Разделить имена» в Данные об аблебитах вкладка > Текст группа.
- Выберите нужные части имен (в нашем случае все) по клику Расколоть.
Сделанный! Различные части имен распределены по нескольким столбцам именно так, как должны, а заголовки столбцов добавляются автоматически для вашего удобства. Никаких формул, никакой возни с запятыми и пробелами, вообще никакой боли.
Если вам интересно попробовать инструмент «Разделить имена» на своих рабочих листах, не стесняйтесь скачать пробную версию Ultimate Suite для Excel.
Доступные загрузки
Формулы для разделения имен в Excel (файл .xlsx)
Ultimate Suite 14-дневная полнофункциональная версия (файл .exe)
