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

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

    81 комментарий для “Работа с фамилией, именем, отчеством и инициалами в 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);".")

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

    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 не пишут функцию разделяющую текст в ячейке по столбцам?
      Функция могла бы выглядеть вот так — РАЗДЕЛИТЬ(разделитель; текст)

    10. Светлана

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

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

    12. Здравствуйте!
      У меня вот такая задачка, столбец содержит полные ФИО сотрудников и не ФИО, типа ячейки с содержанием «итого».
      Как я могу вытащить из этого столбца только данные с ФИО? Думала может как-то тянуть ячейки, которые содержат 3 слова или еще как-то…
      Подскажите, пожалуйста.

      Вообще я хотела искать номер строки, который содержит ФИО, у меня там большая формула задумана…

    13. У меня работают два брата Иванов Александр Владимирович и Иванов Антон Владимирович. Как преобразовать инициалы в Иванов Ал.В. и Иванов Ан.В.

      1. Евгений

        Здравствуйте, Андрей!
        Используйте такую формулу:
        =СЦЕПИТЬ(ЛЕВСИМВ(A1;НАЙТИ(" ";A1;1));" ";ПСТР(A1;НАЙТИ(" ";A1;1)+1;2);".";ПСТР(A1;НАЙТИ(" ";A1;НАЙТИ(" ";A1;1)+1)+1;1);".")

    14. Екатерина

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

      1. Евгений

        Здравствуйте, Екатерина!
        Могу предложить только вариант, когда в первом столбце таблицы перечислены фамилии с инициалами:

        Если у нескольких человек одинаковые фамилии и инициалы, выведен будет первый по списку, как на скриншоте.

    15. в ячейке есть фамилия имя отчество как в другую ячейку оставить только имя и фамилию

      1. Евгений

        Привет, ket!
        Используйте следующую формулу:
        =ПСТР(A1;НАЙТИ(" ";A1;1)+1;НАЙТИ(" ";A1;НАЙТИ(" ";A1;1)+1)-НАЙТИ(" ";A1;1)-1) & " " & ЛЕВСИМВ(A1;НАЙТИ(" ";A1;1)-1)

    16. Александр
      А В С D E F
      1 10.11.2021 А*****р Е*****ч Ж*****в А*****р Е*****ч Ж*****в
      2 10.11.2021 А*****р А*****ч Б*****в А*****р А*****ч Б*****в
      3 10.11.2021 А*****й В*****ч П*****о А*****й В*****ч П*****о

      Добрый день, есть формула для автоматического разделения F строки на C,D,Е и второй вопрос есть чёрный список «М» столбец фамилии как можно подсвечивать фамилии совпадающие с «Е» столбцом. Условным форматированием подсвечиваются все совпадения в «Е» столбце и получается каша.

      1. Евгений

        Здравствуйте, Александр!
        Формулы для первой строки:

        • ячейка C1 — =ЛЕВСИМВ(F1;НАЙТИ(" ";F1;1)-1)
        • ячейка D1 — =ПСТР(F1;НАЙТИ(" ";F1;1)+1;НАЙТИ(" ";F1;НАЙТИ(" ";F1;1)+1)-НАЙТИ(" ";F1;1)-1)
        • ячейка E1 — =ПРАВСИМВ(F1;ДЛСТР(F1)-НАЙТИ(" ";F1;НАЙТИ(" ";F1;1)+1))

        Второй вопрос не понял.

    17. Здравствуйте!
      Подскажите выводил значение по совпадению фамилии в двух таблицах формулой

      Но формула перестает работать по однофамильцам… как можно дополнить чтобы учитывала еще и имя.
      Пример формата ФИО для формулы Иванов Иван Иванович.
      Спасибо!

      1. Евгений

        Здравствуйте, Артем!
        Попробуйте заменить
        ЛЕВСИМВ($A5;(ПОИСК(" ";$A5;1)))
        на
        ЛЕВСИМВ($A4;(ПОИСК(" ";$A4;ПОИСК(" ";$A4;1)+1))-1)

        1. Спасибо, работает! Ещё вопрос:
          Подскажите пожалуйста как можно выдернуть часть текста одной формулой, которая находится в разном положении в тексте, пример:
          1.Геннадий_Волгоград_врач_ИСКОМЫЙТЕКСТ
          2.СВЕТЛАНА СЕРГЕЕВНА_Нижний Новгород_ИСКОМЫЙТЕКСТ_тренер_вождение
          3.Ростов-на-Дону_ИСКОМЫЙТЕКСТ_ВАЛЕНТИН_студент

          Спасибо!

    18. Здравствуйте!
      Подскажите, пожалуйста, как с помощью формулы извлечь первую букву имени?

        1. Имя идет у меня в таблице вторым словом, поэтому формула не подходит.
          Я использовала формулу — =ПСТР(A2;9;2), но не всегда получается первая буква имени

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

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