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

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

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

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

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

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

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

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

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

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

=LEFT(A2,FIND(" ",A2,1)-1)

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

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

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

=LEFT(A2,FIND(" ",A2,1)-1)

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

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

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

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

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

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

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

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

Используйте тот же подход к разделению фамилий. На этот раз вы должны использовать ПРАВИЛЬНУЮ формулу, которая разделяет строки, начиная с правой стороны.

Формула, которая вам нужна:

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

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

=RIGHT(A2,LEN(A2)-SEARCH("#",SUBSTITUTE(A2," ","#",LEN(A2)-LEN(SUBSTITUTE(A2," ","")))))

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

=RIGHT(A2;LEN(A2)-SEARCH("#";SUBSTITUTE(A2;" ";"#";LEN(A2)-LEN(SUBSTITUTE(A2;" ";"")))))

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

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

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

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

или:

=RIGHT(A2,LEN(A2)-SEARCH(" ",A2))

или:

=RIGHT(A2;LEN(A2)-SEARCH(" ";A2))

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

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

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

=СРЕДН(ячейка, ПОИСК(" ", ячейка) + 1, ПОИСК(" ", ячейка, ПОИСК(" ", ячейка)+1) – ПОИСК(" ", ячейка)-1)

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

=MID(A2,SEARCH(" ",A2)+1,SEARCH(" ",A2,SEARCH(" ",A2)+1)-SEARCH(" ",A2)-1)

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

=MID(A2;SEARCH(" ";A2)+1;SEARCH(" ";A2;SEARCH(" ";A2)+1)-SEARCH(" ";A2)-1)

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

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

Тогда ваша формула станет такой:

=ЕСЛИОШИБКА(СРЕДН(ячейка, ПОИСК(" ", ячейка) + 1, ПОИСК(" ", ячейка, ПОИСК(" ", ячейка)+1) – ПОИСК(" ", ячейка)-1)"")

или:

=IFERROR(MID(A2,SEARCH(" ",A2)+1,SEARCH(" ",A2,SEARCH(" ",A2)+1)-SEARCH(" ",A2)-1),"")

или:

=IFERROR(MID(A2;SEARCH(" ";A2)+1;SEARCH(" ";A2;SEARCH(" ";A2)+1)-SEARCH(" ";A2)-1);"")

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

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

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

Эта формула:

=TRIM(MID(cell1,LEN(cell2)+1,LEN(cell1)-LEN(cell2&cell3)))

Здесь ячейка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,ДЛСТР(ячейка2)+1,ДЛСТР(ячейка1)-ДЛСТР(ячейка2&ячейка3)))

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

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

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

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

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

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

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

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