Вы можете использовать Excel, чтобы разбить информацию на более мелкие части. Поиск необходимых данных и управление ими - важная цель для многих пользователей Excel.
Если у вас есть полное имя человека, вам может потребоваться сосредоточиться только на его имени или фамилии. Например, если вы отправляете своим клиентам удобное автоматическое электронное письмо, вам нужно использовать их имена, чтобы они не казались безличными. Если вы просматриваете список респондентов опроса, может быть важно просто использовать их фамилии или скрыть их фамилии для сохранения анонимности.
Excel упрощает этот процесс, и вы можете использовать множество различных подходов. Вот руководство, которое поможет вам создать отдельные столбцы «Имя» и «Фамилия» с помощью формул. Мы также затрагиваем проблему отчества.
Формулы Excel для разделения имен на части
С чего начать?
Разделение имен
Это общая формула:
= ЛЕВЫЙ (ячейка; НАЙТИ (""; ячейка; 1) -1)
Для его выполнения замените клетка с указателем ячейки, содержащей первое полное имя, которое вы хотите разделить. В этом примере вы хотите выбрать B2 и ввести формулу:
= ЛЕВЫЙ (A2; НАЙТИ (""; A2,1) -1)
Однако важно отметить, что на некоторых устройствах в этой формуле вместо запятых используются точки с запятой. Поэтому, если приведенная выше формула вам не подходит, возможно, вам придется использовать следующую версию:
= ЛЕВЫЙ (ячейка; НАЙТИ (""; ячейка; 1) -1)
В этом примере вы должны использовать:
= ЛЕВЫЙ (A2; НАЙТИ (""; A2; 1) -1)
Теперь вы можете просто перетащить маркер заполнения в конец столбца «Имя».
Функция LEFT позволяет разделить строку, начиная с левого конца текста. Часть FIND этой формулы определяет местонахождение первого пробела в полном имени, поэтому вы получаете часть своего полного имени, которая стоит перед пустым местом.
Следовательно, имена, поставленные через дефис, остаются вместе, как и имена, содержащие специальные символы. Но в столбце «Полное имя» не будет отчества или отчества.
Запятая или точка с запятой?
Почему формула не одинакова для всех?
Для многих пользователей Excel функции Excel используют запятые для разделения входных данных. Но на некоторых устройствах региональные настройки другие.
Чтобы узнать, какой символ используется в вашем Excel, просто начните вводить формулу. Когда вы начнете входить = ВЛЕВО (, вы увидите наведенный текст, который предложит правильное форматирование.
Разделение фамилий
Используйте тот же подход к разделению фамилий. На этот раз вы должны использовать формулу RIGHT, которая разделяет строки, начиная с правой стороны.
Необходимая вам формула:
= ПРАВО (ячейка, LEN (ячейка) - ПОИСК («#», ПОДСТАВИТЬ (ячейка, »«, «#», LEN (ячейка) - LEN (ПОДСТАВИТЬ (ячейка, «,« »))))))
В приведенном выше примере вы должны использовать следующую формулу в ячейке C2:
= ВПРАВО (A2; LEN (A2) - ПОИСК («#», ПОДСТАВИТЬ (A2, »«, «#», LEN (A2) - LEN (ПОДСТАВИТЬ (A2, «,« »)))))
Еще раз, вам может потребоваться переключиться с запятой на точку с запятой, что означает, что вам может потребоваться использовать:
= ВПРАВО (A2; LEN (A2) - ПОИСК («#»; ПОДСТАВИТЬ (A2; »«; «#»; LEN (A2) - LEN (ПОДСТАВИТЬ (A2; «;« »)))))
Фамилии, поставленные через дефис, и фамилии со специальными символами остаются без изменений.
Почему эта формула сложнее формулы для имен? Сложнее отделить от фамилии отчество и отчество.
Если вы хотите, чтобы отчество и инициалы указывались вместе с фамилиями, вы можете использовать формулу:
= ПРАВО (ячейка; ДЛСТР (ячейка) - ПОИСК (”“, ячейка))
или:
= ВПРАВО (A2; LEN (A2) - ПОИСК (”“, A2))
или:
= ВПРАВО (A2; LEN (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)
После ввода формулы перетащите маркер заполнения вниз. Вот столбец отчества, добавленный в приведенный выше пример:
Если полное имя не содержит отчества или инициала, вы получите нулевые значения в этом столбце, которые могут отображаться как #VALUE !. Чтобы получить пустые ячейки вместо # ЗНАЧ!, Вы можете использовать функцию ЕСЛИОШИБКА.
Тогда ваша формула станет:
= ЕСЛИОШИБКА (MID (ячейка, ПОИСК (»«, ячейка) + 1, ПОИСК (««, ячейка, ПОИСК (»«, ячейка) +1) - ПОИСК (»«, ячейка) -1), 0)
или:
= ЕСЛИОШИБКА (MID (A2; ПОИСК (""; A2) + 1; ПОИСК (""; A2, ПОИСК ("", A2) +1) - ПОИСК ("", A2) -1), 0)
или:
= ЕСЛИОШИБКА (MID (A2; ПОИСК (""; A2) + 1; ПОИСК (""; A2; ПОИСК (""; A2) +1) - ПОИСК (""; A2) -1); 0)
Единый подход к разделению нескольких отчества
Что произойдет, если у кого-то из вашего списка будет несколько отчества? Используя приведенную выше формулу, будет извлечено только их первое отчество.
Чтобы решить эту проблему, вы можете попробовать другой подход к разделению отчества. Если у вас есть столбцы с именем и фамилией, вы можете просто отрезать их. Все, что осталось, будет считаться вторым именем.
Эта формула:
= ОБРЕЗАТЬ (MID (ячейка1; LEN (ячейка2) + 1; LEN (ячейка1) -LEN (ячейка2 & cell3)))
Здесь ячейка 1 относится к указателю ячейки в столбце Полное имя, ячейка 2 относится к указателю ячейки в столбце Имя, а ячейка 3 относится к указателю ячейки в столбце Фамилия. В приведенном выше примере мы получаем:
= ОБРЕЗАТЬ (СРЕДНЕЕ (A2; LEN (B2) + 1; LEN (A2) -LEN (B2 & D2)))
или:
= ОБРЕЗАТЬ (СРЕДНЕЕ (A2; LEN (B2) +1; LEN (A2) -LEN (B2 & D2)))
Если вы воспользуетесь этой формулой, вам не придется беспокоиться о нулевых значениях.
Краткое резюме
Вот формулы, которые можно использовать для разделения полных имен на части:
Имена: = ЛЕВЫЙ (ячейка; НАЙТИ (""; ячейка; 1) -1)
Фамилии: = ПРАВО (ячейка, LEN (ячейка) - ПОИСК («#», ПОДСТАВИТЬ (ячейка, »«, «#», LEN (ячейка) - LEN (ПОДСТАВИТЬ (ячейка, «,« »))))))
Отчество: = ЕСЛИОШИБКА (MID (ячейка, ПОИСК (»«, ячейка) + 1, ПОИСК (««, ячейка, ПОИСК (»«, ячейка) +1) - ПОИСК (»«, ячейка) -1), 0)
Альтернативная формула для отчества: = ОБРЕЗАТЬ (MID (ячейка1; LEN (ячейка2) + 1; LEN (ячейка1) -LEN (ячейка2 & cell3)))
Разделение имени и фамилии без использования формул
Если вам не хочется вводить кучу формул, которые могут быть введены неправильно, воспользуйтесь встроенным в Excel мастером преобразования текста в столбцы.
- Убедитесь, что Данные выберите вкладку в меню вверху и выделите столбец, который вы хотите преобразовать.
- Затем нажмите на Текст в столбцы.
- Далее убедитесь, что С разделителями выбрано и щелкните Следующий
. - Теперь выберите Космос из опций и нажмите Следующий.
- Затем измените Место назначения к "$ 2 млрд”И щелкните Заканчивать.
Конечный результат должен выглядеть так. 
.
Конечный результат должен выглядеть так. 
Последнее слово
Есть много других способов решить эту проблему в Excel. Если ни один из доступных вариантов не делает то, что вам нужно, проведите дополнительное исследование.
Использовать формулы относительно просто, и это не зависит от версии Excel, которую вы используете. Но, к сожалению, вы все равно можете столкнуться с ошибками.
Например, если полное имя человека начинается с его фамилии, оно будет неправильно разделено. В формулах также будут проблемы с фамилиями, которые содержат префиксы или суффиксы, например, le Carré или van Gogh. Если чье-то имя заканчивается на младший, это будет указано как его фамилия.
Однако есть модификации, которые вы можете добавить, чтобы решить эти проблемы по мере их появления. Работа с формулами дает вам гибкость, необходимую для решения этих сложных задач.
