Преобразование имени и отчества в инициалы с помощью формул Excel и присоединение их к фамилии. Разделение фамилий, имен и отчеств, расположенных в одной ячейке, по разным ячейкам и наоборот.
Фамилия, имя и отчество в разных ячейках
Когда фамилия, имя и отчество записаны в разные ячейки, работать с ними легче, чем, когда они расположены в одной ячейке. Перечислим преимущества:
- при заполнении таких ячеек в таблицах срабатывает автоподбор значений, так как имена и отчества часто повторяются;
- для заполнения таких ячеек можно использовать раскрывающиеся списки наиболее распространенных имен и отчеств;
- фамилия, имя и отчество, записанные в разные ячейки, легко объединить в одну, а имя и отчество заменить инициалами.
Итак, объединяем фамилию, имя и отчество полностью из разных ячеек в одну:
Здесь можно использовать формулу «СЦЕПИТЬ», как в примере =СЦЕПИТЬ(A1;" ";B1;" ";C1) , или просто соединить строки с помощью & (амперсандов) =A1&" "&B1&" "&C1 , не забыв добавить между словами пробелы.
В следующем примере мы также объединим фамилию, имя и отчество из разных ячеек в одну, при этом имя и отчество заменив на инициалы:
Здесь также можно использовать формулу «СЦЕПИТЬ», как в примере =СЦЕПИТЬ(A1;" ";ЛЕВСИМВ(B1;1);".";ЛЕВСИМВ(C1;1);".") или & (амперсанды) =A1&" "&ЛЕВСИМВ(B1;1)&"."&ЛЕВСИМВ(C1;1)&"." , не забыв добавить между фамилией и инициалами пробел, а к инициалам точки. В этом примере мы извлекаем левые символы из имени и отчества для присоединения их к фамилии в виде инициалов.
Фамилия, имя и отчество в одной ячейке
Использование фамилии, имени и отчества в одной ячейке имеет тоже свои преимущества:
- уменьшается количество колонок в таблице;
- в большинство документов* требуется внесение фамилии, имени и отчества в полном написании.
*Если документы генерируются на основе этой таблицы, то вставка ФИО не потребует дополнительных преобразований.
Лично мне в работе не приходилось преобразовывать фамилию, имя и отчество из одной ячейки в разные, но могу предположить, что для кого-то это бывает необходимо:
Для этого преобразования используются следующие формулы в соответствующих ячейках:
- ячейка B1 — =ЛЕВСИМВ(A1;НАЙТИ(" ";A1;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);".")
Вы можете копировать эти формулы в свои файлы, не забывая изменять адреса ячеек на фактические. Часто инициалы пишут перед фамилией, изменить формулы для такого отображения ФИО несложно.
Работа с фамилией, именем, отчеством и инициалами в Excel : 2 комментария
Фамилия Имя Отчество в Фамилию и инициалы.
Если используется оператор Option Explicit, можно так:
Пусть в A1 полное ФИО. Введите в B1 такую формулу и наслаждайтесь
=ЛЕВСИМВ (A1; ПОИСК (" ";A1)) & ПСТР (A1; ПОИСК (" ";A1)+1; 1) &"." & ПСТР (A1; ПОИСК (" "; A1; ПОИСК (" "; A1)+1)+1; 1) & "."
Превращаем полное ФИО в Фамилию + Инициалы.
Если Фамилия, Имя и Отчество (ФИО) хранятся в одной ячейке (например, в B1) и отсутствуют лишние пробелы, то превратить полное ФИО в Фамилию + инициалы можно с помощью этой формулы (см. файл примера ):
=ЛЕВСИМВ(B1;НАЙТИ(" ";B1;1)+1)&"."&ПСТР(B1;НАЙТИ(" ";B1;НАЙТИ(" ";B1;1)+1)+1;1)&"."
Формула определяет позицию начала Имени и Отчества по пробелам (код символа пробел =32). Очевидно, что порядок следования фамилии и имени с отчеством важен для работы формулы.
Формула НАЙТИ(" ";B1;1) в выражении Иванов Иван Иванович. Соответственно, выражение ЛЕВСИМВ(B1;НАЙТИ(" ";B1;1)+1) выводит фамилию и первый инициал (первую букву имени).
Гарантировать, что в ячейке отсутствуют лишние пробелы можно предварительно применив формулу СЖПРОБЕЛЫ() .
Чтобы превратить Иванов Иван Иванович (в ячейке B2) в И.И. Иванов (инициалы перед фамилией) используйте формулу = ПСТР(B2;НАЙТИ(СИМВОЛ(32);B2;1)+1;1)&"."&ПСТР(B2;ПОИСК(СИМВОЛ(32);B2;НАЙТИ(СИМВОЛ(32);B2;1)+1)+1;1)&". "&ЛЕВСИМВ(B2;НАЙТИ(СИМВОЛ(32);B2;1)-1)
Совет: Если бы фамилия, имя и отчество хранились в отдельных ячейках, то формула была бы гораздо проще =ЛЕВСИМВ(B1;1)&"." То же относится и к другим строковым значениям: Адрес (индекс, страна, город, улица); Название книги (автор, название), банковские реквизиты (номер счета, банк, БИК) и др. Формулы становятся значительно проще, если разные по смыслу данные хранятся в отдельных ячейках.