Excel: выделение имени, отчества, инициалов из ФИО

Дано: таблица Excel с колонкой, содержащей фамилию, имя и отчество (ФИО).
Задача: извлечь при помощи формул из колонки ФИО данные в следующем виде:
  • Фамилия
  • Имя Отчество
  • И.О. (инициалы)
  • Фамилия И.О.
  • Имя
  • Отчество


Будем использовать формулы с текстовыми функциями.
Будем предполагать, что исходные данные (ФИО) содержатся в ячейке A1 - "Иванов Сергей Олегович".

1. Выделение фамилии из ФИО

Формула извлечения фамилии (в ячейке B1):
=ЛЕВСИМВ(A1;ПОИСК(" *";A1)-1)



2. Выделение Имени Отчества из ФИО

Формула извлечения Имени Отчества (в ячейке C1):
=ПСТР(A1;ПОИСК(" ";A1)+1;ДЛСТР(A1))


3. Выделение инициалов (И.О.) из ФИО

Формула извлечения И.О. (в ячейке D1):
=ПСТР(A1;ПОИСК(" *";A1)+1;1)&"."&ПСТР(A1;ПОИСК(" *";A1;ПОИСК(" *";A1)+1)+1;1)&"."


4. Выделение фамилии и инициалов из ФИО

Формула извлечения в виде Фамилия И.О. (в ячейке E1):
=ЛЕВСИМВ(A1;ПОИСК(" *";A1)-1)&" "&ПСТР(A1;ПОИСК(" *";A1)+1;1)
&"."&ПСТР(A1;ПОИСК(" *";A1;ПОИСК(" *";A1)+1)+1;1)&"."


5. Выделение имени из ФИО

Формула извлечения имени из ФИО (в ячейке F1):
=ПСТР(A1;ПОИСК(" *";A1)+1;ПОИСК(" *";A1;ПОИСК(" *";A1)+1)-ПОИСК(" *";A1)-1)

Если имеется ячейка с именем отчеством (C1 в нашем примере), то формула схожа с формулой выделения фамилии:
=ЛЕВСИМВ(C1;ПОИСК(" *";C1)-1)

6. Выделение отчества из ФИО

Формула извлечения отчества из ФИО (в ячейке G1):
=ПСТР(A1;ПОИСК(" *";A1;ПОИСК(" *";A1)+1)+1;ДЛСТР(A1))


Если имеется ячейка с именем отчеством (C1 в нашем примере), то формула может иметь вид:
=ПРАВСИМВ(C1;ПОИСК(" *";C1)+1)


(с) Ella S.
Если Вам понравилась статья, пожалуйста, поставьте лайк, сделайте репост или оставьте комментарий. Если у Вас есть какие-либо замечания, также пишите комментарии.

18 комментариев :

  1. Косяк какой то в формуле вычленения "имени" из "Фамилии Имени Отчестве". При постановке формулы к примеру Абрамов Дмитрий Геннадьевич имя выводит как "Дмитрий Ген", приходится менять последнее число формулы с "-1" на "-3", но ввиду большого количества имен разной длины приходится в некоторых ячейках в ручную это править. Подправьте пожалуйста, что бы было автоматически. Заранее благодарен!

    ОтветитьУдалить
  2. Огромное Спасибо! Всё хорошо, кроме последней формулы: вытянуть отчество из "Александр Иванович" не получается, а выходит "др Иванович". С другими именами и отчествами (какие у меня есть) получилось.

    ОтветитьУдалить
    Ответы
    1. Пожалуйста. А в ячейке "Александр Иванович" случайно нет лишнего пробела в начале фразы? Из-за этого может быть ошибка.

      Удалить
    2. Исправление к последней формуле - =ПРАВСИМВ(C1;ДЛСТР(C1)-ПОИСК(" *";C1))

      Удалить
  3. Подскажите, пожалуйста, для русских ФИО и т.п. все понятно, а вот если Киргиз, у неких есть фамилия типу Иванов Уулу, а имя Аданбек, отчество может быть, может не быть... Можно ли как-то под них тоже автоматизировать процесс?

    ОтветитьУдалить
    Ответы
    1. А как система должна понять, что в строке "Иванов Уулу Аданбек" - Уулу - это не имя, а Аданбек - не отчество? В таком случае нужен дополнительный параметр, который будет указывать, что в данном случае надо менять правила обработки строки. Например, добавить еще колонку, которая будет задавать "признак отличия ФИО" (может гражданство или еще как-то), а затем в формуле добавить условие, допустим, если признак "не РФ", то рассчитывать по другой формуле. Иначе никак. Когда разрабатываю какую-либо систему, всегда задаю хранение отдельно имени, отдельно отчества, отдельно фамилии, чтобы не было таких проблем. Формулами из данной статьи приходится пользоваться, если кто-то прислал списки, где ФИО в одной строке, тогда и сложности.

      Удалить
  4. Добрый день. подскажите пожалуйста: а как то реально сделать чтобы в ФИО между И. О. был пробел?

    ОтветитьУдалить
    Ответы
    1. Просто добавьте пробел после точки в формуле. А так не совсем понятно, из какой исходной строки Вам нужно сделать преобразование. Имеется в виду, что дано "Фамилия И.О.", а надо "Фамилия И. О."? Или Вы говорите о каких-то из приведенных выше формулах?

      Удалить
  5. Добрый день! Второй день пытаюсь переделать не получается. Как сделать из Фамилия Имя Отчество = Имя Отчество Ф. ????

    ОтветитьУдалить
    Ответы
    1. не знаю на сколько правильно, но у меня получилось вот что =СЦЕПИТЬ(ПРАВСИМВ('составы Ж1'!B2;ДЛСТР('составы Ж1'!B2)-ПОИСК(" ";'составы Ж1'!B2));" ";ЛЕВСИМВ('составы Ж1'!B2);".")

      Удалить
    2. Да, варианты могут различные. Вот еще схожие:
      =СЦЕП(ПСТР(A1;ПОИСК(" ";A1)+1;ДЛСТР(A1));" ";ЛЕВСИМВ(A2);".")
      =ПСТР(A2;ПОИСК(" ";A2)+1;ДЛСТР(A2)) & " " & ЛЕВСИМВ(A3) & "."

      Удалить
  6. Всегда приятно находить такие скриптики в интернете. Только подумаешь, какая работа тебя ждет... а тут раз и все готово, да еще с красивыми примерами! Большое Вам спасибо!!!!

    ОтветитьУдалить
  7. А есть вариант Выделение фамилии и инициалов из ФИО чтоб на выходе было из Иванов иван Иванович
    Иванов Иван

    ОтветитьУдалить
    Ответы
    1. Формула будет схожа с формулой "Фамилия И.О.". Например, так:
      =ЛЕВСИМВ(A1;ПОИСК(" *";A1)-1)&" "&ПСТР(A1;ПОИСК(" *";A1)+1;ПОИСК(" *";A1;ПОИСК(" *";A1)+1)-ПОИСК(" *";A1))

      Удалить
  8. Перенесите в столбцы D и E имена и отчества сотрудников из столбца C, в столбце C оставьте только фамилии: (С - АБИТОВА, D - РОЗА, E - МАРКОВНА). C - АВДЕЕВА АФРОДИТА АБРАМОВНА, D - ? Е - ? Помогите, пожалуйста (дедлайн до 23.03.2022)!

    ОтветитьУдалить