Работа с фамилией, именем, отчеством и инициалами в 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)&"."

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

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

  1. Дмитрий

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

  2. Евгений

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

  3. Алексей

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

    1. Для этого преобразования используется следующая формула в ячейке 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)))

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

      1. Евгений

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

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

      1. Евгений

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

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

        1. Евгений

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

          1. Здравствуйте, спасибо большое за полезную статью. Подскажите, пожалуйста, можно ли добавить в список из Фамилий и имен, как то добавить отчества, если нет соответствующего списка, есть только исходный список с фио, где будут ещё встречаться и люди.

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

          1. Евгений

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

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

        4. Светлана

          Добрый день, подскажите пожалуйста, если надо перенести из таблицы только Фамилию и Имя и что бы они начинались обязательно с заглавных букв даже если в исходном тексте указано в разнобой

        5. Добрый день! Подскажите, пожалуйста, как удалить повторяющиеся фамилию и имя, если в таблице они отражаются по-разному (например, костина света и света костина), при этом в таблице есть и другой диапазон — сумма, которая имеет своё уникальное значение к обоим вариантам повторяющегося имени, т. е. удалять нельзя? Спасибо!

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

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