Работа с фамилией, именем, отчеством и инициалами в Excel

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

Фамилия, имя и отчество в разных ячейках

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

  • при заполнении таких ячеек в таблицах срабатывает автоподбор значений, так как имена и отчества часто повторяются;
  • для заполнения таких ячеек можно использовать раскрывающиеся списки наиболее распространенных имен и отчеств;
  • фамилия, имя и отчество, записанные в разные ячейки, легко объединить в одну, а имя и отчество заменить инициалами.

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

Объединение фамилии, имени и отчества полностью из разных ячеек в одну

Здесь можно использовать формулу «СЦЕПИТЬ», как в примере =СЦЕПИТЬ(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);".")

Здесь тоже функцию «СЦЕПИТЬ» можно заменить & (амперсандами) – =ЛЕВСИМВ(A1;НАЙТИ(" ";A1;1))&" "&ПСТР(A1;НАЙТИ(" ";A1;1)+1;1)&"."&ПСТР(A1;НАЙТИ(" ";A1;НАЙТИ(" ";A1;1)+1)+1;1)&"."

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

19 комментариев для “Работа с фамилией, именем, отчеством и инициалами в Excel”

  1. Дмитрий

    Фамилия Имя Отчество в Фамилию и инициалы.

  2. Евгений

    Если используется оператор Option Explicit, можно так:

  3. Алексей

    Здравствуйте. Подскажите, а как из строки в экселе, которой указан текс выбрать только ФИО Например, БАБАЕВ КАЗИМ Иосифович//643,35,26,,ГРАФСКИЙ Х,,СТЕПНАЯ УЛ,38,,// ПАО СБЕРБАНК Г.ПУПКИН 258445564484484, чтоб осталось только БАБАЕВ КАЗИМ Иосифович

  4. Для этого преобразования используется следующая формула в ячейке B1 – =СЦЕПИТЬ(ЛЕВСИМВ(A1;НАЙТИ(” “;A1;1));” “;ПСТР(A1;НАЙТИ(” “;A1;1)+1;1);”.”;ПСТР(A1;НАЙТИ(” “;A1;НАЙТИ(” “;A1;1)+1)+1;1);”.”) Вопрос. Как помнять формулу что-бы инициалы были спереди. Заранее спасибо

    1. Хотя побаловался с формулой. Попереставлял местами. Все получилось поменять. Формула прикольная. Теперь инициалы пишет спереди. Получилось такое. =СЦЕПИТЬ(ПСТР(A4;НАЙТИ(” “;A4;1)+1;1);”.”;ПСТР(A4;НАЙТИ(” “;A4;НАЙТИ(” “;A4;1)+1)+1;1);”.”;” “;ЛЕВСИМВ(A4;НАЙТИ(” “;A4;1)))

  5. Здравствуйте! В массиве есть столбец с полными ФИО. Как определить тех, которые отличаются только отчеством?

    1. Евгений

      Добрый день, Лалик!
      Разделите ФИО на фамилию, имя и отчество в соседних ячейках, как описано в статье и примените к ним автофильтр (выделите заголовки и выберите в меню: Данные >> Фильтр).

  6. Умные люди, подскажите мне, пожалуйста как перевести фамилию, имя и отчество полностью в инициалы?

    1. Евгений

      Если в ячейке «A1» содержится значение – «Иванов Сидор Петрович», тогда, чтобы в другой ячейке получить «И.С.П.», надо вставить формулу: =СЦЕПИТЬ(ЛЕВСИМВ(A1);".";ПСТР(A1;НАЙТИ(" ";A1;1)+1;1);".";ПСТР(A1;НАЙТИ(" ";A1;НАЙТИ(" ";A1;1)+1)+1;1);".")

  7. Добрый день! Можете помочь? В одной ячейке фамилия и имя. Как сделать в одну ячейку инициалы?

    1. Евгений

      Добрый день, Анна!
      Если в ячейке «A1» содержится значение – «Сидоров Иван», тогда, чтобы в другой ячейке получить «С.И.», надо вставить формулу: =СЦЕПИТЬ(ЛЕВСИМВ(A1);".";ПСТР(A1;НАЙТИ(" ";A1;1)+1;1);".")

  8. Здравствуйте! Подскажите, пожалуйста, как написать такую формулу: в ячейке А на листе 1 текст, содержащий фамилию и инициалы. Нужно подтянуть в него с ячейки W ( в ней только фамилия и инициалы) на листе 2 по совпадению по фио информацию в столбцы В,С,D на листе 1 из ячеек Х, Y, Z с листа 2.
    Проще говоря, в ячейке А данные о бронировании авиабилетов, содержащих фамилию пассажира, в столбцах В,С,D нужно заполнить время вылета, дату вылета и номер рейса. Эти данные содержаться на листе 2 в хаотичном порядке, как подтянуть эту информацию в ячейки на первом листе со второго по совпадению по фио?

    1. Евгений

      Здравствуйте, Жанна!
      Используйте функцию ВПР. Вставьте ее через мастер функций в каждую ячейку первой строки. Перед копированием вниз сделайте ссылку на диапазон на Листе2 абсолютной (F4). Получится у вас что-то вроде этого (формула для ячейки B1): =ВПР(A1;Лист2!$A$1:$E$50;2;ЛОЖЬ). Первый столбец указанного диапазона на Листе2 – столбец с ФИО.

  9. Кто нить знает причину по которой разработчики Excel не пишут функцию разделяющую текст в ячейке по столбцам?
    Функция могла бы выглядеть вот так – РАЗДЕЛИТЬ(разделитель; текст)

Добавить комментарий

Ваш комментарий будет опубликован после прохождения обязательной модерации. Исходящие ссылки не допускаются. Время модерации составит от нескольких минут до нескольких часов в зависимости от времени суток и занятости модератора.