Как выделить фамилию из фио в эксель

Обновлено: 02.07.2024

Иногда в работе необходимо преобразовать имена и отчества людей в инициалы. Это можно быстро сделать при помощи приведенных ниже формул (если полное имя расположено в ячейке A1):

Для преобразования Иванов Иван Иванович —> Иванов И.И.:

2. Для преобразования Иванов Иван Иванович —> И.И. Иванов:

3. Для преобразования Иван Иванович Иванов —> Иванов И.И.:

4. Для преобразования Иван Иванович Иванов —> И.И. Иванов:

Ну а быстро вводить данные формулы поможет наша надстройка Subex для Excel:

Превращаем полное ФИО в Фамилию + Инициалы.

Если Фамилия, Имя и Отчество (ФИО) хранятся в одной ячейке (например, в 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)&"." То же относится и к другим строковым значениям: Адрес (индекс, страна, город, улица); Название книги (автор, название), банковские реквизиты (номер счета, банк, БИК) и др. Формулы становятся значительно проще, если разные по смыслу данные хранятся в отдельных ячейках.

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

Фамилия, имя и отчество в разных ячейках

Когда фамилия, имя и отчество записаны в разные ячейки, работать с ними легче, чем, когда они расположены в одной ячейке. Перечислим преимущества:

  • при заполнении таких ячеек в таблицах срабатывает автоподбор значений, так как имена и отчества часто повторяются;
  • для заполнения таких ячеек можно использовать раскрывающиеся списки наиболее распространенных имен и отчеств;
  • фамилия, имя и отчество, записанные в разные ячейки, легко объединить в одну, а имя и отчество заменить инициалами.

Итак, объединяем фамилию, имя и отчество полностью из разных ячеек в одну:


В следующем примере мы также объединим фамилию, имя и отчество из разных ячеек в одну, при этом имя и отчество заменив на инициалы:


Фамилия, имя и отчество в одной ячейке

Использование фамилии, имени и отчества в одной ячейке имеет тоже свои преимущества:

  • уменьшается количество колонок в таблице;
  • в большинство документов* требуется внесение фамилии, имени и отчества в полном написании.

*Если документы генерируются на основе этой таблицы, то вставка ФИО не потребует дополнительных преобразований.

Лично мне в работе не приходилось преобразовывать фамилию, имя и отчество из одной ячейки в разные, но могу предположить, что для кого-то это бывает необходимо:


Для этого преобразования используются следующие формулы в соответствующих ячейках:

  • ячейка 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?

Как из полного фио сделать инициалы Эксель?

Как разделить содержимое ячейки в Эксель?

Разделение содержимого ячейки на несколько ячеек

  1. Выделите одну или несколько ячеек, которые хотите разделить. …
  2. На вкладке Данные в группе Работа с данными нажмите кнопку Текст по столбцам. …
  3. Установите переключатель С разделителями, если выбран другой вариант, и нажмите кнопку Далее.

Как разделить число в Excel?

Итак, как в экселе разделить число на число?

  1. ввести число-делимое в одну ячейку, например, А1.
  2. ввести число-делитель в другую ячейку, например, А2.
  3. в ячейке, в которой нужно получить результат деления, ввести формулу: =А1/А2 и нажать клавишу Enter.

Как разделить текст по строкам в Excel?

Как в Excel разделить текст в одной ячейке на две строки?

Чтобы разбить текст на строки в одной ячейке Excel нужно нажать комбинацию клавиш Alt+Enter.

Как вставить текст в несколько ячеек Excel?

Как добавить одинаковый текст в несколько ячеек Excel?

  1. Выделите все ячейки, в которые вы хотите вставить текст.
  2. После выделения сразу введите текст.
  3. Когда это сделано вместо Enter, нажмите Ctrl+Enter.

Как найти имя в таблице Excel?

Самый простой способ — выполнить поиск. Для этого можно нажать клавиатурную комбинацию CTRL + F (от англ. Find), откроется окно поиска слов. Для нажатия клавиатурной комбинации, нажмите клавишу клавиатуры CTRL и, удерживая ее, нажмите клавишу F (на английский язык переходить не нужно).

Что такое фамилия и инициалы?

Как в Эксель разделить ячейку по диагонали и написать текст?

Как разделить ячейку в Excel по диагонали?

Как в Excel разбить число на несколько ячеек?

Выделите ячейки с номерами, которые нужно разбить на цифры, затем нажмите Kutools > Слияние и разделение > Разделить клетки. Смотрите скриншот: 2. в Разделить клетки диалоговое окно, выберите Разделить на столбцы вариант в Тип и в разделе Указать разделитель выберите Указать ширину и введите число 1 в текстовое поле.

Как расцепить текст в ячейке?

На вкладке Данные нажмите кнопку Текст по столбцам . В мастере распределения текста по столбцам установите переключатель с разделителями и нажмите кнопку Далее . Выберите разделители для своих данных. Например, запятую и пробел .

Excel works!

Excel работает за вас

Excel works!

Как разделить текст в Excel по столбцам?

Предположим, у вас есть большой список ФИО. Необходимо разделить текст в Excel по столбцам, чтобы имя, отчество и фамилия были в разных ячейках. Как разделить текст в Excel по столбцам?

разделить текст в Excel

Итак, существует два способа разделить текст по столбцам.

1-й способ разделить текст по столбцам. Мастер текста

Выделите столбец с текстом который хотите разделить. Затем выберите в верхней панели Данные — Текст по столбцам . Появится окно Мастера текста (разбор).

разделить текст в Excel

В открывшемся меню выбираем пробел или нужный символ.

Разделить пробел

Если все данные имеют одинаковую длину, можно разделить по фиксированной ширине (см. картинку 2)

2-й способ. Разделить текст в Excel формулами. Функция ПСТР

Если необходимо разделить текст по столбцам с использованием формулы, воспользуйтесь функциями =ПСТР() и =НАЙТИ().

Для того чтобы найти Фамилию в ячейку В2 занесите формулу

Где функция Найти будет находить кол-во символов до пробела, а функция =ПСТР() возвращать это кол-во символов.

Чтобы получить Имя формула усложняется (необходимо дать функциям знать, где начинается имя т.е. заканчивается 1-й пробел), поэтому введите в ячейку C3

Чтобы найти Отчество в ячейку D3 занесите формулу

Выглядит тяжеловато, но если разобраться вы увидите, что все несложно, особенно с примером:

Как сцепить ячейки Имя, Отчество, Фамилия

Для того чтобы сцепить данные в ячейках воспользуйтесь функцией = СЦЕПИТЬ () или структурой с использованием & (амперсант) — символа сцепки, например, внесите в ячейку D3 формулу

Вы можете использовать Excel, чтобы разбить информацию на более мелкие части. Поиск нужных данных и манипулирование ими — важная цель для многих пользователей Excel.

Если у вас есть полное имя человека, вам может понадобиться указать только его имя или фамилию. Например, если вы отправляете дружественное автоматическое электронное письмо своим клиентам, вам нужно использовать их имена, чтобы не звучать безлично. Если вы просматриваете список респондентов, возможно, важно просто использовать их фамилии или скрыть их фамилии, чтобы сохранить анонимность.

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

Формулы Excel для разделения имен на части

  • Формулы Excel для разделения имен на части
    • Разделение имен
    • Запятая или точка с запятой?
    • Разделение фамилий
    • Разделение отчеств
    • Один подход к разделению нескольких средних имен

    Разделение имен

    Это общая формула:

    = ВЛЕВО (ячейка, НАЙТИ (”“, ячейка, 1) -1)

    Чтобы выполнить это, замените клетка с указателем ячейки, который содержит первое полное имя, которое вы хотите разделить. В этом примере вы хотите выбрать B2 и ввести формулу:

    = ВЛЕВО (A2, НАЙТИ (”“, A2,1) -1)

    Однако важно отметить, что на некоторых устройствах в этой формуле вместо запятых используются точки с запятой. Поэтому, если приведенная выше формула не работает для вас, вам может потребоваться использовать следующую версию:

    = LEFT (ячейка; НАЙТИ (”“; ячейка; 1) -1)

    В этом примере вы бы использовали:

    = ВЛЕВО (A2; НАЙТИ (”“; A2; 1) -1)

    Теперь вы можете просто перетащить маркер заполнения вниз до конца столбца Имя.


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

    Следовательно, дефисные имена остаются вместе, как и имена, содержащие специальные символы. Но ваш столбец Полное имя не будет содержать отчества или инициалы.

    Запятая или точка с запятой?

    Почему формула не одинакова для всех?

    Для многих пользователей Excel функции Excel используют запятые для разделения входных данных. Но на некоторых устройствах региональные настройки разные.

    Чтобы узнать, какой символ используется вашим Excel, просто начните вводить в формуле. Когда вы начинаете вводить = ЛЕВЫЙ (, Вы увидите всплывающий текст, который предложит правильное форматирование.

    Разделение фамилий

    Используйте тот же подход к разделению фамилий. На этот раз вы должны использовать ПРАВИЛЬНУЮ формулу, которая разделяет строки, начиная с правой стороны.

    Формула, которая вам нужна:

    В приведенном выше примере вы должны использовать следующую формулу в ячейке C2:

    Еще раз, вам может потребоваться переключиться с запятой на точку с запятой, а это означает, что вам может понадобиться использовать:


    Фамилии и фамилии со специальными символами остаются без изменений.

    Почему эта формула более сложна, чем формула для имен? Труднее отделить отчество и фамилию от фамилии.

    Если вы хотите, чтобы отчество и инициалы были указаны с фамилиями, вы можете использовать формулу:

    = ВПРАВО (ячейка, LEN (ячейка) — ПОИСК (”«, ячейка))

    = ПРАВО (A2, LEN (A2) — ПОИСК (”“, A2))

    = ВПРАВО (A2; LEN (A2) — ПОИСК (”“; A2))

    Но что, если вы хотите отделить вторые имена? Это не так часто, но полезно знать.

    Разделение отчеств

    Формула для отчеств следующая:

    В приведенном выше примере вы получите:

    Если ваш Excel использует точки с запятой, формула:

    = MID (A2; ПОИСК (”“; A2) + 1; ПОИСК (”“; A2; ПОИСК (”“; A2) +1) — ПОИСК (”“; A2) -1)

    После ввода формулы перетащите маркер заполнения вниз. Вот столбец среднего имени, добавленный к примеру выше:


    Тогда ваша формула становится:

    = IFERROR (MID (A2; ПОИСК (”;; A2) + 1; ПОИСК (” “; A2; ПОИСК (” “; A2) +1) — ПОИСК (” “; A2) -1); 0)

    Один подход к разделению нескольких средних имен

    Что произойдет, если кто-то в вашем списке имеет несколько отчеств? Используя приведенную выше формулу, будет получено только их первое отчество.

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

    = TRIM (MID (cell1, LEN (CELL2) + 1, LEN (cell1) -LEN (cell2cell3)))

    Здесь cell1 ссылается на указатель ячейки в столбце Full Name, cell2 ссылается на указатель ячейки в столбце Name, а cell3 ссылается на указатель ячейки в столбце Last Name. В приведенном выше примере мы получаем:

    = TRIM (MID (А2, LEN (В2) + 1, LEN (А2) -LEN (B2D2)))

    = TRIM (MID (А2; LEN (В2) + 1; LEN (А2) -LEN (B2D2)))

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


    Быстрый обзор

    Вот формулы, которые вы можете использовать для разбиения полных имен на части:

    Имена: = ВЛЕВО (ячейка, НАЙТИ (”“, ячейка, 1) -1)

    Альтернативная формула для отчеств: = TRIM (MID (cell1, LEN (CELL2) + 1, LEN (cell1) -LEN (cell2cell3)))

    Последнее слово

    Есть много других способов решить эту проблему в Excel. Например, вы можете использовать инструмент Text to Columns. Как и формулы выше, этот инструмент разбивает имена на части путем поиска пробелов.

    Использование формул относительно просто и не зависит от используемой версии Excel. Но, к сожалению, вы все равно можете столкнуться с ошибками.

    Например, если полное имя человека начинается с его фамилии, оно будет неправильно разделено на части. У формул также будут проблемы с фамилиями, которые содержат префиксы или суффиксы, такие как le Carré или van Gogh. Если чье-то имя оканчивается на младшего, это будет указано как его фамилия.

    Однако есть изменения, которые вы можете добавить, чтобы решить эти проблемы по мере их появления. Работа с формулами дает вам гибкость, необходимую для решения этих сложностей.

    Иногда в работе необходимо преобразовать имена и отчества людей в инициалы. Это можно быстро сделать при помощи приведенных ниже формул (если полное имя расположено в ячейке A1):

    Для преобразования Иванов Иван Иванович —> Иванов И.И.:

    2. Для преобразования Иванов Иван Иванович —> И.И. Иванов:

    3. Для преобразования Иван Иванович Иванов —> Иванов И.И.:

    4. Для преобразования Иван Иванович Иванов —> И.И. Иванов:

    Ну а быстро вводить данные формулы поможет наша надстройка Subex для Excel:

    Превращаем полное ФИО в Фамилию + Инициалы.

    Если Фамилия, Имя и Отчество (ФИО) хранятся в одной ячейке (например, в 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)&"." То же относится и к другим строковым значениям: Адрес (индекс, страна, город, улица); Название книги (автор, название), банковские реквизиты (номер счета, банк, БИК) и др. Формулы становятся значительно проще, если разные по смыслу данные хранятся в отдельных ячейках.

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

    Фамилия, имя и отчество в разных ячейках

    Когда фамилия, имя и отчество записаны в разные ячейки, работать с ними легче, чем, когда они расположены в одной ячейке. Перечислим преимущества:

    • при заполнении таких ячеек в таблицах срабатывает автоподбор значений, так как имена и отчества часто повторяются;
    • для заполнения таких ячеек можно использовать раскрывающиеся списки наиболее распространенных имен и отчеств;
    • фамилия, имя и отчество, записанные в разные ячейки, легко объединить в одну, а имя и отчество заменить инициалами.

    Итак, объединяем фамилию, имя и отчество полностью из разных ячеек в одну:


    В следующем примере мы также объединим фамилию, имя и отчество из разных ячеек в одну, при этом имя и отчество заменив на инициалы:


    Фамилия, имя и отчество в одной ячейке

    Использование фамилии, имени и отчества в одной ячейке имеет тоже свои преимущества:

    • уменьшается количество колонок в таблице;
    • в большинство документов* требуется внесение фамилии, имени и отчества в полном написании.

    *Если документы генерируются на основе этой таблицы, то вставка ФИО не потребует дополнительных преобразований.

    Лично мне в работе не приходилось преобразовывать фамилию, имя и отчество из одной ячейки в разные, но могу предположить, что для кого-то это бывает необходимо:


    Для этого преобразования используются следующие формулы в соответствующих ячейках:

    • ячейка 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);".")

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

    Читайте также: