Вы можете использовать Excel, чтобы разбить информацию на более мелкие части. Поиск нужных данных и манипулирование ими — важная цель для многих пользователей Excel.
Если у вас есть полное имя человека, вам может понадобиться указать только его имя или фамилию. Например, если вы отправляете дружественное автоматическое электронное письмо своим клиентам, вам нужно использовать их имена, чтобы не звучать безлично. Если вы просматриваете список респондентов, возможно, важно просто использовать их фамилии или скрыть их фамилии, чтобы сохранить анонимность.
Excel делает этот процесс простым, и есть много разных подходов, которые вы можете использовать. Вот учебник, который поможет вам создать отдельные столбцы Имя и Фамилия, используя формулы. Мы также рассмотрим проблему отчеств.
Формулы Excel для разделения имен на части
Быстрые ссылки
- Формулы Excel для разделения имен на части
- Разделение имен
- Запятая или точка с запятой?
- Разделение фамилий
- Разделение отчеств
- Один подход к разделению нескольких средних имен
- Быстрый обзор
- Последнее слово
С чего начать?
Разделение имен
Это общая формула:
= ВЛЕВО (ячейка, НАЙТИ (”“, ячейка, 1) -1)
Чтобы выполнить это, замените клетка с указателем ячейки, который содержит первое полное имя, которое вы хотите разделить. В этом примере вы хотите выбрать B2 и ввести формулу:
= ВЛЕВО (A2, НАЙТИ (”“, A2,1) -1)
Однако важно отметить, что на некоторых устройствах в этой формуле вместо запятых используются точки с запятой. Поэтому, если приведенная выше формула не работает для вас, вам может потребоваться использовать следующую версию:
= LEFT (ячейка; НАЙТИ (”“; ячейка; 1) -1)
В этом примере вы бы использовали:
= ВЛЕВО (A2; НАЙТИ (”“; A2; 1) -1)
Теперь вы можете просто перетащить маркер заполнения вниз до конца столбца Имя.
Функция LEFT позволяет вам отделить строку, начиная с левого конца текста. Часть FIND этой формулы находит первый пробел в полном имени, поэтому вы получаете часть вашего полного имени, которая стоит перед пустым пробелом.
Следовательно, дефисные имена остаются вместе, как и имена, содержащие специальные символы. Но ваш столбец Полное имя не будет содержать отчества или инициалы.
Запятая или точка с запятой?
Почему формула не одинакова для всех?
Для многих пользователей Excel функции Excel используют запятые для разделения входных данных. Но на некоторых устройствах региональные настройки разные.
Чтобы узнать, какой символ используется вашим Excel, просто начните вводить в формуле. Когда вы начинаете вводить = ЛЕВЫЙ (, Вы увидите всплывающий текст, который предложит правильное форматирование.
Разделение фамилий
Используйте тот же подход к разделению фамилий. На этот раз вы должны использовать ПРАВИЛЬНУЮ формулу, которая разделяет строки, начиная с правой стороны.
Формула, которая вам нужна:
= ВПРАВО (ячейка, LEN (ячейка) — ПОИСК («#», ЗАМЕНА (ячейка »,« # », LEN (ячейка) — LEN (ЗАМЕНА (ячейка,« «,« »)))))
В приведенном выше примере вы должны использовать следующую формулу в ячейке C2:
= ВПРАВО (A2, LEN (A2) — ПОИСК («#», ЗАМЕНА (A2, ««, «#», LEN (A2) — LEN (ЗАМЕНА (A2, ««, «»)))))
Еще раз, вам может потребоваться переключиться с запятой на точку с запятой, а это означает, что вам может понадобиться использовать:
= ВПРАВО (A2; LEN (A2) — ПОИСК («#»; ЗАМЕНА (A2; »«; «#»; LEN (A2) — LEN (ЗАМЕНА (A2; »;« »))))))
Фамилии и фамилии со специальными символами остаются без изменений.
Почему эта формула более сложна, чем формула для имен? Труднее отделить отчество и фамилию от фамилии.
Если вы хотите, чтобы отчество и инициалы были указаны с фамилиями, вы можете использовать формулу:
= ВПРАВО (ячейка, LEN (ячейка) — ПОИСК (”«, ячейка))
или же:
= ПРАВО (A2, LEN (A2) — ПОИСК (”“, A2))
или же:
= ВПРАВО (A2; LEN (A2) — ПОИСК (”“; A2))
Но что, если вы хотите отделить вторые имена? Это не так часто, но полезно знать.
Разделение отчеств
Формула для отчеств следующая:
= MID (ячейка, SEARCH (»«, ячейка) + 1, SEARCH (»«, ячейка, SEARCH (»«, ячейка) +1) — SEARCH (»«, ячейка) -1)
В приведенном выше примере вы получите:
= MID (A2, ПОИСК (»«, A2) + 1, ПОИСК (»«, A2, ПОИСК (»«, A2) +1) — ПОИСК (»,« A2) -1)
Если ваш Excel использует точки с запятой, формула:
= MID (A2; ПОИСК (”“; A2) + 1; ПОИСК (”“; A2; ПОИСК (”“; A2) +1) — ПОИСК (”“; A2) -1)
После ввода формулы перетащите маркер заполнения вниз. Вот столбец среднего имени, добавленный к примеру выше:
Если полное имя не содержит отчества или инициала, в этом столбце вы получите нулевые значения, которые могут отображаться как # ЗНАЧ !. Чтобы получить пустые ячейки вместо # ЗНАЧ!, Вы можете использовать функцию IFERROR.
Тогда ваша формула становится:
= IFERROR (MID (ячейка, SEARCH (», ячейка) + 1, SEARCH (», ячейка, SEARCH (», ячейка) +1) — SEARCH (», ячейка) -1), 0)
или же:
= IFERROR (MID (A2, ПОИСК (»,« A2) + 1, ПОИСК (»,« A2, ПОИСК (»,« A2) +1) — ПОИСК (»,« A2) -1), 0)
или же:
= IFERROR (MID (A2; ПОИСК (”;; A2) + 1; ПОИСК (” “; A2; ПОИСК (” “; A2) +1) — ПОИСК (” “; A2) -1); 0)
Один подход к разделению нескольких средних имен
Что произойдет, если кто-то в вашем списке имеет несколько отчеств? Используя приведенную выше формулу, будет получено только их первое отчество.
Чтобы решить эту проблему, вы можете попробовать другой подход к разделению отчеств. Если у вас есть созданные столбцы имени и фамилии, вы можете просто их обрезать. Все, что осталось, будет считаться отчеством.
Эта формула:
= TRIM (MID (cell1, LEN (CELL2) + 1, LEN (cell1) -LEN (cell2cell3)))
Здесь cell1 ссылается на указатель ячейки в столбце Full Name, cell2 ссылается на указатель ячейки в столбце Name, а cell3 ссылается на указатель ячейки в столбце Last Name. В приведенном выше примере мы получаем:
= TRIM (MID (А2, LEN (В2) + 1, LEN (А2) -LEN (B2D2)))
или же:
= TRIM (MID (А2; LEN (В2) + 1; LEN (А2) -LEN (B2D2)))
Если вы воспользуетесь этой формулой, вам не придется беспокоиться о нулевых значениях.
Быстрый обзор
Вот формулы, которые вы можете использовать для разбиения полных имен на части:
Имена: = ВЛЕВО (ячейка, НАЙТИ (”“, ячейка, 1) -1)
Фамилии: = ВПРАВО (ячейка, LEN (ячейка) — ПОИСК («#», ЗАМЕНА (ячейка »,« # », LEN (ячейка) — LEN (ЗАМЕНА (ячейка,« «,« »)))))
Отчество: = IFERROR (MID (ячейка, SEARCH (», ячейка) + 1, SEARCH (», ячейка, SEARCH (», ячейка) +1) — SEARCH (», ячейка) -1), 0)
Альтернативная формула для отчеств: = TRIM (MID (cell1, LEN (CELL2) + 1, LEN (cell1) -LEN (cell2cell3)))
Последнее слово
Есть много других способов решить эту проблему в Excel. Например, вы можете использовать инструмент Text to Columns. Как и формулы выше, этот инструмент разбивает имена на части путем поиска пробелов.
Использование формул относительно просто и не зависит от используемой версии Excel. Но, к сожалению, вы все равно можете столкнуться с ошибками.
Например, если полное имя человека начинается с его фамилии, оно будет неправильно разделено на части. У формул также будут проблемы с фамилиями, которые содержат префиксы или суффиксы, такие как le Carré или van Gogh. Если чье-то имя оканчивается на младшего, это будет указано как его фамилия.
Однако есть изменения, которые вы можете добавить, чтобы решить эти проблемы по мере их появления. Работа с формулами дает вам гибкость, необходимую для решения этих сложностей.
