Как отделить имя от фамилии в excel
Если вы имеете диапазон ячеек, содержащих имена и фамилии людей, часто бывает необходимо работать только с именем или только с фамилией. Например, вам может потребоваться хранение имен и фамилий отдельно для дальнейшего импортирования в базу данных, в которой хранится ваш аттестат профбухгалтера. Или вам может потребоваться отсортировать имена.
Решением является использование функции НАЙТИ() для поиска пробела, разделяющего две части имени, а затем использование функции ЛЕВСИМВ() или ПРАВСИМВ() для выделения необходимой части. Для выделения левой части (предположим, там находится фамилия) используйте следующую формулу:
=ЛЕВСИМВ(A2;НАЙТИ("";A2)-1)
Обратите внимание, что в формуле происходит вычитание единицы из необходимого для выделения числа символов, дабы предотвратить появление пробела. Вы можете использовать данную формулу для любого более общего примера для выделения первого слова из любой строки. Для выделения имени необходимо построить похожую формулу, используя функцию ПРАВСИМВ():
=ПРАВСИМВ(A2;ДЛСТР(A2)-НАЙТИ("";A2))
Для выделения необходимого числа символов формула берет длину слова и вычитает из него положение пробела. Вы можете использовать данную формулу для любого более общего примера для выделения второго слова из любой строки. На рис. 4.13 показаны результаты работы.
Рис. 4.13. Результат выделения фамилии и имени
Здесь же в столбце D вы видите результат объединения двух предыдущих формул для изменения порядка следования слов. Обратите внимание, что данные формулы выведут ошибку, в случае если строка содержит только одно слово. Для предотвращения этого используйте функцию ЕСЛИОШИБКА(): Если полное имя человека, с которым вы работаете, содержит фамилию, имя и отчество, вы не сможете применять вышеуказанные формулы. В этом случае первая формула для выделения фамилии будет работать нормально, но выделить правую часть таким образом не получится. Существует несколько способов выйти из этой ситуации, мы с вами рассмотрим решение с применением функций ПОИСК() и НАЙТИ(). Например, нам дано следующее имя: Булгаков Виктор Кирсанович. Предполагая, что имя находится в ячейке A2, формула =НАЙТИ(" ";A2) , вернет 9 — позицию первого пробела. Для нахождения позиции следующего пробела необходимо указать позицию первого пробела плюс 1: =НАЙТИ(" ";A2;НАЙТИ(" ";A2)+1 ). Теперь можно применить данную конструкцию для нахождения самой правой части (в данном случае это отчество): =ПРАВСИМВ(A2;ДЛСТР(A2)-НАЙТИ(" ";A2;НАЙТИ(' ";A2)+1)) . Рис. 4.14. Выделение имени, фамилии и отчества из строки Для выделения центральной части необходимо определить разницу в положении первого и второго пробела и воспользоваться формулой выделения из любой части строки: =ПСТР(A2;НАЙТИ(" ";;A2)+1;НАЙТИ(" ";A2;НАЙТИ(" ";A2)+1-НАЙТИ(" ";A2)) . На рис. 4.14 показаны результаты работы. Будем использовать формулы с текстовыми функциями. 1. Выделение фамилии из ФИО Формула извлечения фамилии (в ячейке B1): 2. Выделение Имени Отчества из ФИО Формула извлечения Имени Отчества (в ячейке C1): 3. Выделение инициалов (И.О.) из ФИО Формула извлечения И.О. (в ячейке D1): 4. Выделение фамилии и инициалов из ФИО Формула извлечения в виде Фамилия И.О. (в ячейке E1): 5. Выделение имени из ФИО Формула извлечения имени из ФИО (в ячейке F1): 6. Выделение отчества из ФИО Формула извлечения отчества из ФИО (в ячейке G1): Если Вам понравилась статья, пожалуйста, поставьте лайк, сделайте репост или оставьте комментарий. Если у Вас есть какие-либо замечания, также пишите комментарии. Этот комментарий был удален автором. Огромное Спасибо! Всё хорошо, кроме последней формулы: вытянуть отчество из "Александр Иванович" не получается, а выходит "др Иванович". С другими именами и отчествами (какие у меня есть) получилось. Пожалуйста. А в ячейке "Александр Иванович" случайно нет лишнего пробела в начале фразы? Из-за этого может быть ошибка. Исправление к последней формуле - =ПРАВСИМВ(C1;ДЛСТР(C1)-ПОИСК(" *";C1)) Подскажите, пожалуйста, для русских ФИО и т.п. все понятно, а вот если Киргиз, у неких есть фамилия типу Иванов Уулу, а имя Аданбек, отчество может быть, может не быть. Можно ли как-то под них тоже автоматизировать процесс? А как система должна понять, что в строке "Иванов Уулу Аданбек" - Уулу - это не имя, а Аданбек - не отчество? В таком случае нужен дополнительный параметр, который будет указывать, что в данном случае надо менять правила обработки строки. Например, добавить еще колонку, которая будет задавать "признак отличия ФИО" (может гражданство или еще как-то), а затем в формуле добавить условие, допустим, если признак "не РФ", то рассчитывать по другой формуле. Иначе никак. Когда разрабатываю какую-либо систему, всегда задаю хранение отдельно имени, отдельно отчества, отдельно фамилии, чтобы не было таких проблем. Формулами из данной статьи приходится пользоваться, если кто-то прислал списки, где ФИО в одной строке, тогда и сложности. wikiHow работает по принципу вики, а это значит, что многие наши статьи написаны несколькими авторами. При создании этой статьи над ее редактированием и улучшением работали, в том числе анонимно, 15 человек(а). Если вы работаете с электронными таблицами и получаете таблицу, в которой имя и фамилия находятся в одной ячейке, вы не сможете отсортировать по фамилии. А сортировка по имени и фамилии одновременно не принесет никакой пользы. В этом случае, вам надо отделить имя от фамилии до сортировки. В этом примере наведите курсор над колонкой "B" до появления стрелки вниз, затем, чтобы выделить всю колонку, нажмите левую кнопку мыши. Далее выберите вкладку "Данные" и нажмите на кнопку "Текст по столбцам". Примите во внимание, что у вас должно быть несколько свободных колонок после колонки, которую вы конвертируете. Если надо, выделите колонку и добавьте 2-3 новых. В противном случае, после конвертации данные в соседних колонках будут заменены.
=ЕСЛИОШИБКА(ЛЕВСИМВExcel: выделение имени, отчества, инициалов из ФИО
Будем предполагать, что исходные данные (ФИО) содержатся в ячейке A1 - "Иванов Сергей Олегович".
Если имеется ячейка с именем отчеством (C1 в нашем примере), то формула схожа с формулой выделения фамилии: 18 комментариев :
Если у вас слова разделены запятыми (например, Brown, James), выберите запятую в качестве символа-разделителя, и т.д.
-
Этот параметр меняется только тогда, когда вы имеете дело с числами или датами.
Теперь вы можете поменять заголовки на "Имя" и "Фамилия", и если нужно, отсортировать по фамилии. Так выглядит отсортированная по фамилии электронная таблица с обновленными заголовками.
Когда вы работаете с данными в своей книге Microsoft Excel, это не всегда просто цифры. Может быть, ваша таблица содержит имена клиентов, клиентов, сотрудников или контактов. И в зависимости от того, откуда берутся ваши данные, вам, возможно, придется манипулировать ими, чтобы удовлетворить ваши потребности в списке рассылки или базе данных.
Если вам нужно разделить имя и фамилию в Excel, у вас есть несколько гибких опций. Это включает в себя не только имя и фамилию, но и отчество, префиксы и суффиксы. Вот полезное руководство, которое показывает, как разделить имена в Excel.
Как разделить имена в Excel с помощью мастера
Мастер преобразования текста в столбец это самый простой способ отделить имена и фамилии в Excel в дополнение к отчествам. И инструмент достаточно гибок, чтобы приспособиться к вашим данным.
Убедитесь, что рядом с данными, которые вы собираетесь разделить, есть пустой столбец, потому что результаты будут идти по умолчанию. Если это невозможно, вы все еще будут быть в состоянии изменить место назначения для вашего разделения данных.
Разделенные имена, разделенные пробелами
Во-первых, если имена разделены пробелами, просто выполните следующие действия.
- Выберите столбец или ячейки, содержащие имена, которые вы хотите разделить. Столбец или ячейки будут выделены.
- Нажмите на Данные вкладка и выберите Текст в столбцы в твоей ленте.
- Во всплывающем окне выберите разграниченный для типа файла, который лучше всего описывает ваши данные.
- Нажмите следующий,
- Под Разделители, снимите флажок табуляция и проверить Космос,
- Нажмите следующий,
- Под Формат данных столбца, выбирать Текст, Если вам нужно выбрать другой Место назначения для ваших результатов, введите его в это поле.
- Нажмите Конец,
Этот метод работает для разделения имени и фамилии в Excel, а также отчества или инициала. Итак, если ваши данные находятся в ячейке под именем Сью Салли Смит, Сью С. Смит или Сью С. Смит, все будут работать правильно.
Разделенные имена, разделенные запятыми
Если имя и фамилия разделены запятыми, вам потребуется лишь небольшая корректировка приведенных выше инструкций. На шаге 5 под Разделители, проверять запятая, Вы можете сохранить Космос проверил, если данные также имеют пробелы.
Разделение имен и удаление данных
Допустим, у вас есть имена, которые включают имя, отчество и фамилию, но вы не хотите оставлять отчество. Начните с тех же шагов, что и выше, пока не дойдете до шага 7, затем выполните следующие действия.
- Под Формат данных столбца, Выбрать Не импортировать столбец (Пропустить),
- в Предварительный просмотр выбранных данныхщелкните, чтобы выделить столбец данных, которые вы хотите удалить из результатов.
- Нажмите Конец,
Как разделить имена в Excel с помощью функций
Если вы выполняете одноразовое разделение, описанные выше методы с использованием мастера преобразования текста в столбцы являются наиболее простыми. Но если вы будете добавлять больше данных, которые вы хотите разделить, вы можете использовать формулы Excel
16 формул Excel, которые помогут вам решить реальные проблемы
16 формул Excel, которые помогут вам решить реальные проблемы
Правильный инструмент — это половина работы. Excel может решать вычисления и обрабатывать данные быстрее, чем вы можете найти свой калькулятор. Мы покажем вам основные формулы Excel и покажем, как их использовать.
Прочитайте больше
, Кроме того, хотя мастер обладает гибкостью, у него есть свои ограничения. Итак, мы рассмотрим некоторые функции для особых обстоятельств.
Разделить имя, отчество и фамилию
Выберите ячейку, в которой вы хотите отобразить данные, и вставьте или вставьте их в следующие формулы:
Имя:
Фамилия:
Второе имя:
Замените метки ячеек (комбинация букв и цифр) метками для ячеек, которые вы разделяете.
Имена с префиксом, удалить префикс
Имя:
Фамилия:
Имена с суффиксом, суффикс в отдельной колонке
Имя:
Фамилия:
Суффикс:
Имена из двух частей
Имя:
Фамилия:
Фамилии, состоящие из двух частей
Имя:
Фамилия:
Фамилии, состоящие из трех частей
Имя:
Фамилия:
Еще раз, не забудьте заменить метки ячеек на те для ячеек, которые вы разделяете.
Заполните ваши формулы
После того, как вы введете любую из указанных выше функций, вы можете перетащить, чтобы заполнить оставшуюся часть вашего столбца этими формулами.
Выберите первую ячейку с формулой. Переместитесь в правый нижний угол и когда черный знак плюс появится, перетащите вниз, чтобы заполнить нужное количество ячеек.
В Excel легко разделять имена
Если вы сможете использовать мастер преобразования текста в столбец, вы сможете легко разделить имя и фамилию в Excel. Но если у вас есть сложные данные для работы, по крайней мере, у вас есть варианты разделения имен с помощью функций.
Для получения дополнительных уроков по Excel, подобных этим, посмотрите, как объединять и разбивать ячейки
Как объединить и разделить ячейки в Microsoft Excel
Как объединить и разделить ячейки в Microsoft Excel
Существует причина, по которой наименьшая единица в электронной таблице Excel называется ячейкой: ее нельзя разделить дальше. Тем не менее, вот как объединить и разделить ячейки, как вы будете.
Прочитайте больше
или быстро удалите все пустые ячейки
Как быстро удалить все пустые ячейки в Excel
Как быстро удалить все пустые ячейки в Excel
У вас есть электронная таблица Excel с пустыми строками и / или столбцами? Не тратьте время на удаление их вручную. Этот метод позволяет легко.
Прочитайте больше
в Excel.
Узнайте больше о: Microsoft Excel, Microsoft Office 2016, Microsoft Office 2019, электронная таблица.
Если фамилия, имя и отчество записываются в разные ячейки, работать с ними легче, чем при размещении их в одной ячейке. Перечислим преимущества:
- при заполнении таких ячеек в таблицах срабатывает автоподбор значений, так как имена и отчества часто повторяются;
- для заполнения таких ячеек можно использовать раскрывающиеся списки наиболее распространенных имен и отчеств;
- фамилия, имя и отчество, записанные в разные ячейки, легко объединить в одну, а имя и отчество заменить инициалами.
Итак, объединяем фамилию, имя и отчество полностью из разных ячеек в одну:
Здесь можно использовать формулу «СЦЕПИТЬ», как в примере =СЦЕПИТЬ(A1;" ";B1;" ";C1) , или просто соединить строки с помощью & (амперсандов) =A1&" "&B1&" "&C1 , не забыв добавить между словами пробелы.
В следующем примере мы также объединим фамилию, имя и отчество из разных ячеек в одну, при этом имя и отчество заменив на инициалы:
Здесь также можно использовать формулу «СЦЕПИТЬ», как в примере =СЦЕПИТЬ(A1;" ";ЛЕВСИМВ(B1;1);".";ЛЕВСИМВ(C1;1);".") или & (амперсанды) =A1&" "&ЛЕВСИМВ(B1;1)&"."&ЛЕВСИМВ(C1;1)&"." , не забыв добавить между фамилией и инициалами пробел, а к инициалам точки. В этом примере мы извлекаем левые символы из имени и отчества для присоединения их к фамилии в виде инициалов.
Фамилия, имя и отчество в одной ячейке
Использование фамилии, имени и отчества в одной ячейке имеет тоже свои преимущества:
- уменьшается количество колонок в таблице;
- в большинство документов* требуется внесение фамилии, имени и отчества в полном написании.
*Если документы генерируются на основе этой таблицы, то вставка ФИО не потребует дополнительных преобразований.
Лично мне в работе не приходилось преобразовывать фамилию, имя и отчество из одной ячейки в разные, но могу предположить, что для кого-то это бывает необходимо:
Для этого преобразования используются следующие формулы в соответствующих ячейках:
- ячейка B1 — =ЛЕВСИМВ(A1;НАЙТИ(" ";A1;1)-1)
- ячейка C1 — =ПСТР(A1;НАЙТИ(" ";A1;1)+1;НАЙТИ(" ";A1;НАЙТИ(" ";A1;1)+1)-НАЙТИ(" ";A1;1)-1)
- ячейка D1 — =ПРАВСИМВ(A1;ДЛСТР(A1)-НАЙТИ(" ";A1;НАЙТИ(" ";A1;1)+1))
Чтобы определить начало имени и начало отчества используются позиции первого и второго пробелов, найденных с помощью функции «НАЙТИ».
Необходимость следующего преобразования возникает чаще предыдущего, используется для заполнения различных документов наряду с полным именем:
Для этого преобразования используется следующая формула в ячейке B1 — =СЦЕПИТЬ(ЛЕВСИМВ(A1;НАЙТИ(" ";A1;1));" ";ПСТР(A1;НАЙТИ(" ";A1;1)+1;1);".";ПСТР(A1;НАЙТИ(" ";A1;НАЙТИ(" ";A1;1)+1)+1;1);".")
Вы можете копировать эти формулы в свои файлы, не забывая изменять адреса ячеек на фактические. Часто инициалы пишут перед фамилией, изменить формулы для такого отображения ФИО несложно.
Читайте также: