Как разделить имя и фамилию в Excel

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

Если у вас есть полное имя человека, возможно, вам придется сосредоточиться только на его имени или фамилии. Например, если вы отправляете дружественное автоматическое электронное письмо своим клиентам, вам необходимо использовать их имена, чтобы это не звучало безлично. Если вы просматриваете список респондентов опроса, возможно, стоит просто использовать их фамилии или скрыть их фамилии для сохранения анонимности.

Excel упрощает этот процесс, и вы можете использовать множество различных подходов. Вот руководство, которое поможет вам создать отдельные столбцы имени и фамилии с помощью формул. Мы также затрагиваем проблему вторых имен.

Формулы Excel для разделения имен на части

С чего начать?

Разделение имен

Это общая формула:

=ВЛЕВО(ячейка,НАЙТИ(» «,ячейка,1)-1)

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

=ВЛЕВО(A2,НАЙТИ(” “,A2,1)-1)

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

=ВЛЕВО(ячейка;НАЙТИ(» «;ячейка;1)-1)

В примере вы должны использовать:

=ВЛЕВО(A2,НАЙТИ(” “,A2,1)-1)

Теперь вы можете просто перетащить маркер заполнения до конца столбца «Имя».

Функция LEFT позволяет разделить строку, начиная с левого конца текста. Часть этой формулы НАЙТИ находит первый пробел в полном имени, поэтому вы получаете часть вашего полного имени, которая стоит перед пустым пробелом.

Следовательно, имена, написанные через дефис, остаются вместе, как и имена, содержащие специальные символы. Но ваш столбец «Полное имя» не будет содержать отчества или отчества.

Запятая или точка с запятой?

Почему формула не одинакова для всех?

Многие пользователи Excel используют функции Excel для разделения входных данных запятыми. Но на некоторых устройствах региональные настройки другие.

Чтобы узнать, какой символ используется в вашем Excel, просто начните вводить формулу. Когда вы начнете вводить =ЛЕВО(вы увидите текст при наведении, который предложит правильное форматирование.

Разделение фамилий

Примените тот же подход к разделению фамилий. На этот раз вам следует использовать формулу RIGHT, которая разделяет строки, начиная с правой стороны.

Вам нужна формула:

=ВПРАВО(ячейка, ДЛСТР(ячейка) – ПОИСК(“#”, ПОДСТАВИТЬ(ячейка”, “, “#”, ДЛСТР(ячейка) – ДЛСТР(ЗАСТАВИТЬ(ячейка, ” “, “”)))))

В приведенном выше примере вы должны использовать следующую формулу в ячейке C2:

=ВПРАВО(A2,ДЛСТР(A2)-ПОИСК(“#”,ЗАСТАВИТЬ(A2,” “,”#”,ДЛСТР(A2)-ДЛСТР(ЗАСТАВИТЬ(A2,” “,””)))))

Еще раз вам может потребоваться заменить запятую на точку с запятой, а это означает, что вам может потребоваться использовать:

=ВПРАВО(A2;ДЛСТР(A2)-ПОИСК(“#”;ПОДСТАВИТЬ(A2;” “;”#”;ДЛСТР(A2)-ДЛСТР(ЗАСТАВИТЬ(A2;” “;””)))))

Фамилии, написанные через дефис, а также фамилии со специальными символами остаются неизменными.

Почему эта формула сложнее формулы для имен? Из фамилий сложнее отделить отчество и отчество.

Если вы хотите, чтобы отчества и инициалы были указаны вместе с фамилиями, вы можете использовать формулу:

=ВПРАВО(ячейка, ДЛСТР(ячейка) – ПОИСК(» «, ячейка))

или:

=ВПРАВО(A2,ЛЕН(A2)-ПОИСК(” “,A2))

или:

=ВПРАВО(A2;ДЛСТР(A2)-ПОИСК(” “;A2))

Но что, если вы хотите разделить отчества? Это встречается реже, но знать об этом может быть полезно.

Разделение отчества

Формула отчества следующая:

=MID(ячейка, ПОИСК(» «, ячейка) + 1, ПОИСК(» «, ячейка, ПОИСК(» «, ячейка)+1) – ПОИСК(» «, ячейка)-1)

В приведенном выше примере вы получаете:

=MID(A2,ПОИСК(” “,A2)+1,ПОИСК(” “,A2,ПОИСК(” “,A2)+1)-ПОИСК(” “,A2)-1)

Если в вашем Excel используются точки с запятой, формула будет следующей:

=MID(A2;ПОИСК(” “;A2)+1;ПОИСК(” “;A2;ПОИСК(” “;A2)+1)-ПОИСК(” “;A2)-1)

После ввода формулы перетащите маркер заполнения вниз. Вот столбец «Отчество», добавленный в приведенный выше пример:

Если полное имя не содержит отчества или инициала, в этом столбце вы получите нулевые значения, которые могут отображаться как #ЗНАЧ!. Чтобы получить пустые ячейки вместо #ЗНАЧ!, вы можете использовать функцию ЕСЛИОШИБКА.

Тогда ваша формула будет выглядеть так:

=ЕСЛИОШИБКА(MID(ячейка, ПОИСК(” “, ячейка) + 1, ПОИСК(” “, ячейка, ПОИСК(” “, ячейка)+1) – ПОИСК(” “, ячейка)-1)”,”)

или:

=ЕСЛИОШИБКА(СЕРЕДИНА(A2,ПОИСК(“” “,A2)+1,ПОИСК(“” “,A2,ПОИСК(“” “,A2)+1)-ПОИСК(“” “,A2)-1), “”)

или:

=ЕСЛИОШИБКА(СЕРЕДИНА(A2;ПОИСК(” “;A2)+1;ПОИСК(” “;A2;ПОИСК(” “;A2)+1)-ПОИСК(” “;A2)-1);”)

Один подход к разделению нескольких отчества

Что произойдет, если у кого-то из вашего списка несколько отчеств? Используя приведенную выше формулу, будет получено только их первое второе имя.

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

Эта формула:

=TRIM(MID(ячейка1,LEN(ячейка2)+1,LEN(ячейка1)-LEN(ячейка2&ячейка3)))

Здесь ячейка1 относится к указателю ячейки в столбце «Полное имя», ячейка2 относится к указателю ячейки в столбце «Имя», а ячейка3 относится к указателю ячейки в столбце «Фамилия». В приведенном выше примере мы получаем:

=TRIM(MID(A2,LEN(B2)+1,LEN(A2)-LEN(B2&D2)))

или:

=TRIM(MID(A2;LEN(B2)+1;LEN(A2)-LEN(B2&D2)))

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

Краткий обзор

Вот формулы, которые можно использовать для разделения полных имен на части:

Имена: =ВЛЕВО(ячейка,НАЙТИ(» «,ячейка,1)-1)

Фамилии: =ВПРАВО(ячейка, ДЛСТР(ячейка) – ПОИСК(“#”, ПОДСТАВИТЬ(ячейка”, “, “#”, ДЛСТР(ячейка) – ДЛСТР(ЗАСТАВИТЬ(ячейка, ” “, “”)))))

Средние имена: =ЕСЛИОШИБКА(MID(ячейка, ПОИСК(” “, ячейка) + 1, ПОИСК(” “, ячейка, ПОИСК(” “, ячейка)+1) – ПОИСК(” “, ячейка)-1)”,”)

Альтернативная формула для отчества: =TRIM(MID(ячейка1,LEN(ячейка2)+1,LEN(ячейка1)-LEN(ячейка2&ячейка3)))

Разделение имени и фамилии без использования формул

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

1. Убедитесь, что Данные вкладка выбрана в меню вверху и выделите столбец, который вы хотите преобразовать.
2. Затем нажмите Текст в столбцы.
3. Далее убедитесь, с разделителями выбран и щелкните Следующий.
4. Теперь выберите Космос из вариантов и нажмите Следующий.
5. Затем измените Место назначения к “$B$2» и нажмите Заканчивать.Конечный результат должен выглядеть так.

Последнее слово

Есть много других способов решить эту проблему в Excel. Если ни один из доступных вариантов не дает того, что вам нужно, проведите дополнительное исследование.

Использовать формулы относительно просто и не зависит от используемой версии Excel. Но, к сожалению, вы все равно можете столкнуться с ошибками.

Например, если полное имя человека начинается с фамилии, оно будет разделено неправильно. В формулах также возникнут проблемы с фамилиями, содержащими префиксы или суффиксы, например Ле Карре или Ван Гог. Если чье-то имя заканчивается на «младший», оно будет указано как его фамилия.

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