Преобразование имени и отчества в инициалы с помощью формул 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)&"."
Вы можете копировать эти формулы в свои файлы, не забывая изменять адреса ячеек на фактические. Часто инициалы пишут перед фамилией, изменить формулы для такого отображения ФИО несложно.
Фамилия Имя Отчество в Фамилию и инициалы.
Если используется оператор Option Explicit, можно так:
Здравствуйте. Подскажите, а как из строки в экселе, которой указан текс выбрать только ФИО Например, БАБАЕВ КАЗИМ Иосифович//643,35,26,,ГРАФСКИЙ Х,,СТЕПНАЯ УЛ,38,,// ПАО СБЕРБАНК Г.ПУПКИН 258445564484484, чтоб осталось только БАБАЕВ КАЗИМ Иосифович
Здравствуйте, Алексей!
Для текста в ячейке
А1:=ЛЕВСИМВ(A1;НАЙТИ("/";A1;1)-1)Спасибо
Подскажите как сделать чтобы ФИО не повторялось
Т.е. чтобы ранее записанное ФИО не записывать повторно
Здравствуйте, Игорь!
Если есть такая возможность, отсортируйте таблицу по алфавиту по столбцу с ФИО и добавляйте новые строки в алфавитном порядке.
Или другой вариант: примените к таблице автофильтр, при нажатии на кнопку автофильтра у заголовка столбца с ФИО раскроется список в алфавитном порядке, где вы легко обнаружите, есть ли уже такое ФИО, которое вы хотите записать.
Здравствуйте! А как посчитать дубликаты ФИО? Список большой
Здравствуйте, Конкордия!
Отсортируйте список по алфавиту. Допустим, список в столбце A, тогда в ячейку B2 введите формулу
=ЕСЛИ(ИЛИ(A1=A2;A2=A3);1;"")и протяните ее вниз до конца списка. Количество единиц (сумма) будет соответствовать количеству дублей. Не будет учтен только дубль в ячейке A1, если он там есть.Для этого преобразования используется следующая формула в ячейке B1 – =СЦЕПИТЬ(ЛЕВСИМВ(A1;НАЙТИ(» «;A1;1));» «;ПСТР(A1;НАЙТИ(» «;A1;1)+1;1);».»;ПСТР(A1;НАЙТИ(» «;A1;НАЙТИ(» «;A1;1)+1)+1;1);».») Вопрос. Как помнять формулу что-бы инициалы были спереди. Заранее спасибо
Хотя побаловался с формулой. Попереставлял местами. Все получилось поменять. Формула прикольная. Теперь инициалы пишет спереди. Получилось такое. =СЦЕПИТЬ(ПСТР(A4;НАЙТИ(» «;A4;1)+1;1);».»;ПСТР(A4;НАЙТИ(» «;A4;НАЙТИ(» «;A4;1)+1)+1;1);».»;» «;ЛЕВСИМВ(A4;НАЙТИ(» «;A4;1)))
Здравствуйте! В массиве есть столбец с полными ФИО. Как определить тех, которые отличаются только отчеством?
Добрый день, Лалик!
Разделите ФИО на фамилию, имя и отчество в соседних ячейках, как описано в статье и примените к ним автофильтр (выделите заголовки и выберите в меню: Данные >> Фильтр).
Умные люди, подскажите мне, пожалуйста как перевести фамилию, имя и отчество полностью в инициалы?
Если в ячейке «A1» содержится значение — «Иванов Сидор Петрович», тогда, чтобы в другой ячейке получить «И.С.П.», надо вставить формулу:
=СЦЕПИТЬ(ЛЕВСИМВ(A1);".";ПСТР(A1;НАЙТИ(" ";A1;1)+1;1);".";ПСТР(A1;НАЙТИ(" ";A1;НАЙТИ(" ";A1;1)+1)+1;1);".")Евгений, даже не передать как я Вам благодарна!!!
Добрый день! А как перевести только фамилию и имя в инициалы?
Добрый день! Можете помочь? В одной ячейке фамилия и имя. Как сделать в одну ячейку инициалы?
Добрый день, Анна!
Если в ячейке «A1» содержится значение — «Сидоров Иван», тогда, чтобы в другой ячейке получить «С.И.», надо вставить формулу:
=СЦЕПИТЬ(ЛЕВСИМВ(A1);".";ПСТР(A1;НАЙТИ(" ";A1;1)+1;1);".")Евгений, Вы гений)) Спасибо Вам большое!!!
Здравствуйте, спасибо большое за полезную статью. Подскажите, пожалуйста, можно ли добавить в список из Фамилий и имен, как то добавить отчества, если нет соответствующего списка, есть только исходный список с фио, где будут ещё встречаться и люди.
Здравствуйте! Подскажите, пожалуйста, как написать такую формулу: в ячейке А на листе 1 текст, содержащий фамилию и инициалы. Нужно подтянуть в него с ячейки W ( в ней только фамилия и инициалы) на листе 2 по совпадению по фио информацию в столбцы В,С,D на листе 1 из ячеек Х, Y, Z с листа 2.
Проще говоря, в ячейке А данные о бронировании авиабилетов, содержащих фамилию пассажира, в столбцах В,С,D нужно заполнить время вылета, дату вылета и номер рейса. Эти данные содержаться на листе 2 в хаотичном порядке, как подтянуть эту информацию в ячейки на первом листе со второго по совпадению по фио?
Здравствуйте, Жанна!
Используйте функцию ВПР. Вставьте ее через мастер функций в каждую ячейку первой строки. Перед копированием вниз сделайте ссылку на диапазон на Листе2 абсолютной (F4). Получится у вас что-то вроде этого (формула для ячейки B1):
=ВПР(A1;Лист2!$A$1:$E$50;2;ЛОЖЬ). Первый столбец указанного диапазона на Листе2 — столбец с ФИО.Кто нить знает причину по которой разработчики Excel не пишут функцию разделяющую текст в ячейке по столбцам?
Функция могла бы выглядеть вот так — РАЗДЕЛИТЬ(разделитель; текст)
Добрый день, подскажите пожалуйста, если надо перенести из таблицы только Фамилию и Имя и что бы они начинались обязательно с заглавных букв даже если в исходном тексте указано в разнобой
Добрый день, Светлана!
Не вижу решения по вашему условию.
Благодарю, очень помогли!!!
Добрый день! Подскажите, пожалуйста, как удалить повторяющиеся фамилию и имя, если в таблице они отражаются по-разному (например, костина света и света костина), при этом в таблице есть и другой диапазон — сумма, которая имеет своё уникальное значение к обоим вариантам повторяющегося имени, т. е. удалять нельзя? Спасибо!
Здравствуйте!
У меня вот такая задачка, столбец содержит полные ФИО сотрудников и не ФИО, типа ячейки с содержанием «итого».
Как я могу вытащить из этого столбца только данные с ФИО? Думала может как-то тянуть ячейки, которые содержат 3 слова или еще как-то…
Подскажите, пожалуйста.
Вообще я хотела искать номер строки, который содержит ФИО, у меня там большая формула задумана…
У меня работают два брата Иванов Александр Владимирович и Иванов Антон Владимирович. Как преобразовать инициалы в Иванов Ал.В. и Иванов Ан.В.
Здравствуйте, Андрей!
Используйте такую формулу:
=СЦЕПИТЬ(ЛЕВСИМВ(A1;НАЙТИ(" ";A1;1));" ";ПСТР(A1;НАЙТИ(" ";A1;1)+1;2);".";ПСТР(A1;НАЙТИ(" ";A1;НАЙТИ(" ";A1;1)+1)+1;1);".")Спасибо огромное!!!
Добрый день, известны фамилия и инициалы сотрудника, как из списка вытянуть подходящие по смыслу полные данные фамилия имя отчество. Спасибо
Здравствуйте, Екатерина!
Могу предложить только вариант, когда в первом столбце таблицы перечислены фамилии с инициалами:
Если у нескольких человек одинаковые фамилии и инициалы, выведен будет первый по списку, как на скриншоте.
в ячейке есть фамилия имя отчество как в другую ячейку оставить только имя и фамилию
Привет, ket!
Используйте следующую формулу:
=ПСТР(A1;НАЙТИ(" ";A1;1)+1;НАЙТИ(" ";A1;НАЙТИ(" ";A1;1)+1)-НАЙТИ(" ";A1;1)-1) & " " & ЛЕВСИМВ(A1;НАЙТИ(" ";A1;1)-1)Добрый день, есть формула для автоматического разделения F строки на C,D,Е и второй вопрос есть чёрный список «М» столбец фамилии как можно подсвечивать фамилии совпадающие с «Е» столбцом. Условным форматированием подсвечиваются все совпадения в «Е» столбце и получается каша.
Здравствуйте, Александр!
Формулы для первой строки:
=ЛЕВСИМВ(F1;НАЙТИ(" ";F1;1)-1)=ПСТР(F1;НАЙТИ(" ";F1;1)+1;НАЙТИ(" ";F1;НАЙТИ(" ";F1;1)+1)-НАЙТИ(" ";F1;1)-1)=ПРАВСИМВ(F1;ДЛСТР(F1)-НАЙТИ(" ";F1;НАЙТИ(" ";F1;1)+1))Второй вопрос не понял.
Здравствуйте!
Подскажите выводил значение по совпадению фамилии в двух таблицах формулой
Но формула перестает работать по однофамильцам… как можно дополнить чтобы учитывала еще и имя.
Пример формата ФИО для формулы Иванов Иван Иванович.
Спасибо!
Здравствуйте, Артем!
Попробуйте заменить
ЛЕВСИМВ($A5;(ПОИСК(" ";$A5;1)))на
ЛЕВСИМВ($A4;(ПОИСК(" ";$A4;ПОИСК(" ";$A4;1)+1))-1)Спасибо, работает! Ещё вопрос:
Подскажите пожалуйста как можно выдернуть часть текста одной формулой, которая находится в разном положении в тексте, пример:
1.Геннадий_Волгоград_врач_ИСКОМЫЙТЕКСТ
2.СВЕТЛАНА СЕРГЕЕВНА_Нижний Новгород_ИСКОМЫЙТЕКСТ_тренер_вождение
3.Ростов-на-Дону_ИСКОМЫЙТЕКСТ_ВАЛЕНТИН_студент
Спасибо!
Если искомый текст неизвестен, то извлечь его не получится.
Известен, есть несколько искомых значений, к примеру: ДМ, ДК, ДФК
Здравствуйте!
Подскажите, пожалуйста, как с помощью формулы извлечь первую букву имени?
Здравствуйте, Олеся!
Если имя в ячейке A1, тогда формула:
=ЛЕВСИМВ(A1;1)Имя идет у меня в таблице вторым словом, поэтому формула не подходит.
Я использовала формулу —
=ПСТР(A2;9;2), но не всегда получается первая буква имениТогда так:
=ПСТР(A1;НАЙТИ(" ";A1;1)+1;1)Евгений, здравствуйте!
Все подошло, спасибо огромное!
Зынк