Дано: таблица Excel с колонкой, содержащей фамилию, имя и отчество (ФИО).
Задача: извлечь при помощи формул из колонки ФИО данные в следующем виде:
Будем использовать формулы с текстовыми функциями.
Будем предполагать, что исходные данные (ФИО) содержатся в ячейке A1 - "Иванов Сергей Олегович".
1. Выделение фамилии из ФИО
Формула извлечения фамилии (в ячейке B1):
2. Выделение Имени Отчества из ФИО
Формула извлечения Имени Отчества (в ячейке C1):
3. Выделение инициалов (И.О.) из ФИО
Формула извлечения И.О. (в ячейке D1):
4. Выделение фамилии и инициалов из ФИО
Формула извлечения в виде Фамилия И.О. (в ячейке E1):
5. Выделение имени из ФИО
Формула извлечения имени из ФИО (в ячейке F1):
Если имеется ячейка с именем отчеством (C1 в нашем примере), то формула схожа с формулой выделения фамилии:
6. Выделение отчества из ФИО
Формула извлечения отчества из ФИО (в ячейке G1):
Если имеется ячейка с именем отчеством (C1 в нашем примере), то формула может иметь вид:
(с) Ella S.
Задача: извлечь при помощи формул из колонки ФИО данные в следующем виде:
- Фамилия
- Имя Отчество
- И.О. (инициалы)
- Фамилия И.О.
- Имя
- Отчество
Будем использовать формулы с текстовыми функциями.
Будем предполагать, что исходные данные (ФИО) содержатся в ячейке 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.
Если Вам понравилась статья, пожалуйста, поставьте лайк, сделайте репост или оставьте комментарий. Если у Вас есть какие-либо замечания, также пишите комментарии.
Косяк какой то в формуле вычленения "имени" из "Фамилии Имени Отчестве". При постановке формулы к примеру Абрамов Дмитрий Геннадьевич имя выводит как "Дмитрий Ген", приходится менять последнее число формулы с "-1" на "-3", но ввиду большого количества имен разной длины приходится в некоторых ячейках в ручную это править. Подправьте пожалуйста, что бы было автоматически. Заранее благодарен!
ОтветитьУдалитьСпасибо. Исправила.
УдалитьЭтот комментарий был удален автором.
УдалитьОгромное Спасибо! Всё хорошо, кроме последней формулы: вытянуть отчество из "Александр Иванович" не получается, а выходит "др Иванович". С другими именами и отчествами (какие у меня есть) получилось.
ОтветитьУдалитьПожалуйста. А в ячейке "Александр Иванович" случайно нет лишнего пробела в начале фразы? Из-за этого может быть ошибка.
УдалитьИсправление к последней формуле - =ПРАВСИМВ(C1;ДЛСТР(C1)-ПОИСК(" *";C1))
УдалитьПодскажите, пожалуйста, для русских ФИО и т.п. все понятно, а вот если Киргиз, у неких есть фамилия типу Иванов Уулу, а имя Аданбек, отчество может быть, может не быть... Можно ли как-то под них тоже автоматизировать процесс?
ОтветитьУдалитьА как система должна понять, что в строке "Иванов Уулу Аданбек" - Уулу - это не имя, а Аданбек - не отчество? В таком случае нужен дополнительный параметр, который будет указывать, что в данном случае надо менять правила обработки строки. Например, добавить еще колонку, которая будет задавать "признак отличия ФИО" (может гражданство или еще как-то), а затем в формуле добавить условие, допустим, если признак "не РФ", то рассчитывать по другой формуле. Иначе никак. Когда разрабатываю какую-либо систему, всегда задаю хранение отдельно имени, отдельно отчества, отдельно фамилии, чтобы не было таких проблем. Формулами из данной статьи приходится пользоваться, если кто-то прислал списки, где ФИО в одной строке, тогда и сложности.
УдалитьДобрый день. подскажите пожалуйста: а как то реально сделать чтобы в ФИО между И. О. был пробел?
ОтветитьУдалитьПросто добавьте пробел после точки в формуле. А так не совсем понятно, из какой исходной строки Вам нужно сделать преобразование. Имеется в виду, что дано "Фамилия И.О.", а надо "Фамилия И. О."? Или Вы говорите о каких-то из приведенных выше формулах?
УдалитьДобрый день! Второй день пытаюсь переделать не получается. Как сделать из Фамилия Имя Отчество = Имя Отчество Ф. ????
ОтветитьУдалитьне знаю на сколько правильно, но у меня получилось вот что =СЦЕПИТЬ(ПРАВСИМВ('составы Ж1'!B2;ДЛСТР('составы Ж1'!B2)-ПОИСК(" ";'составы Ж1'!B2));" ";ЛЕВСИМВ('составы Ж1'!B2);".")
УдалитьДа, варианты могут различные. Вот еще схожие:
Удалить=СЦЕП(ПСТР(A1;ПОИСК(" ";A1)+1;ДЛСТР(A1));" ";ЛЕВСИМВ(A2);".")
=ПСТР(A2;ПОИСК(" ";A2)+1;ДЛСТР(A2)) & " " & ЛЕВСИМВ(A3) & "."
Всегда приятно находить такие скриптики в интернете. Только подумаешь, какая работа тебя ждет... а тут раз и все готово, да еще с красивыми примерами! Большое Вам спасибо!!!!
ОтветитьУдалитьПожалуйста, рада, что пригодилось :)
УдалитьА есть вариант Выделение фамилии и инициалов из ФИО чтоб на выходе было из Иванов иван Иванович
ОтветитьУдалитьИванов Иван
Формула будет схожа с формулой "Фамилия И.О.". Например, так:
Удалить=ЛЕВСИМВ(A1;ПОИСК(" *";A1)-1)&" "&ПСТР(A1;ПОИСК(" *";A1)+1;ПОИСК(" *";A1;ПОИСК(" *";A1)+1)-ПОИСК(" *";A1))
Перенесите в столбцы D и E имена и отчества сотрудников из столбца C, в столбце C оставьте только фамилии: (С - АБИТОВА, D - РОЗА, E - МАРКОВНА). C - АВДЕЕВА АФРОДИТА АБРАМОВНА, D - ? Е - ? Помогите, пожалуйста (дедлайн до 23.03.2022)!
ОтветитьУдалить