Как оставить только фамилию в excel

Как в EXCEL «Иванов Иван Иванович» превратить в «Иванов И.И.»

history 4 апреля 2013 г.

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

Если Фамилия, Имя и Отчество (ФИО) хранятся в одной ячейке (например, в 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

Преобразование имени и отчества в инициалы с помощью формул 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);».»)

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

Источник

Microsoft Excel

трюки • приёмы • решения

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

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

Решением является использование функции НАЙТИ() для поиска пробела, разделяющего две части имени, а затем использование функции ЛЕВСИМВ() или ПРАВСИМВ() для выделения необходимой части. Для выделения левой части (предположим, там находится фамилия) используйте следующую формулу:
=ЛЕВСИМВ(A2;НАЙТИ(«»;A2)-1)

Обратите внимание, что в формуле происходит вычитание единицы из необходимого для выделения числа символов, дабы предотвратить появление пробела. Вы можете использовать данную формулу для любого более общего примера для выделения первого слова из любой строки. Для выделения имени необходимо построить похожую формулу, используя функцию ПРАВСИМВ():
=ПРАВСИМВ(A2;ДЛСТР(A2)-НАЙТИ(«»;A2))

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

Рис. 4.13. Результат выделения фамилии и имени

Здесь же в столбце D вы видите результат объединения двух предыдущих формул для изменения порядка следования слов. Обратите внимание, что данные формулы выведут ошибку, в случае если строка содержит только одно слово. Для предотвращения этого используйте функцию ЕСЛИОШИБКА():
=ЕСЛИОШИБКА(ЛЕВСИМВ

Если полное имя человека, с которым вы работаете, содержит фамилию, имя и отчество, вы не сможете применять вышеуказанные формулы. В этом случае первая формула для выделения фамилии будет работать нормально, но выделить правую часть таким образом не получится. Существует несколько способов выйти из этой ситуации, мы с вами рассмотрим решение с применением функций ПОИСК() и НАЙТИ().

Например, нам дано следующее имя: Булгаков Виктор Кирсанович. Предполагая, что имя находится в ячейке A2, формула =НАЙТИ(» «;A2) , вернет 9 — позицию первого пробела. Для нахождения позиции следующего пробела необходимо указать позицию первого пробела плюс 1: =НАЙТИ(» «;A2;НАЙТИ(» «;A2)+1 ).

Теперь можно применить данную конструкцию для нахождения самой правой части (в данном случае это отчество): =ПРАВСИМВ(A2;ДЛСТР(A2)-НАЙТИ(» «;A2;НАЙТИ(‘ «;A2)+1)) .

Рис. 4.14. Выделение имени, фамилии и отчества из строки

Для выделения центральной части необходимо определить разницу в положении первого и второго пробела и воспользоваться формулой выделения из любой части строки: =ПСТР(A2;НАЙТИ(» «;;A2)+1;НАЙТИ(» «;A2;НАЙТИ(» «;A2)+1-НАЙТИ(» «;A2)) . На рис. 4.14 показаны результаты работы.

Источник

Разделить ФИО в Excel на ячейки

Как разделить ячейку с ФИО на части

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

Конечно можно написать формулу, которая извлечет нужные данные из ФИО, но даже опытному человеку потребуется 5-7 минут на это. А что если заранее не знаешь расположение фамилии в тексте? Гораздо проще и удобнее пользоваться готовыми функциями из надстройки VBA-Excel.

Найти фамилию в ФИО

После установки надстройки будет доступна новая функция =ФАМИЛИЯ(ФИО), где

• ФИО — текст или ссылка на ячейку, которая содержит ФИО полностью

Задача данной функции — извлечь из ячейки только фамилию. Причем ей не важен порядок. Фамилия может идти как в начале, так в конце или середине текста.

Найти имя в ФИО

Аналогичная функция для имени =ИМЯ(ФИО). Она извлекает имя из текста с ФИО, также не важно где расположенное.

Найти отчество в ФИО

Последняя функция из этой серии — функция =ОТЧЕСТВО(ФИО) для извлечения отчества.

Примеры работы с ФИО в Excel

Вывести фамилию и инициалы

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

где A1 — ссылка на ячейку с фамилией именем и отчеством

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

Также плюс в том, что нам не важно расположение фамилии в тексте, функция определит все автоматически.

Источник

Как сделать фамилию и инициалы в excel?

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

Задача : извлечь при помощи формул из колонки ФИО данные в следующем виде:

Будем использовать формулы с текстовыми функциями.

Будем предполагать, что исходные данные (ФИО) содержатся в ячейке

«Иванов Сергей Олегович» .

1. Выделение фамилии из ФИО Формула извлечения фамилии (в ячейке B1):

2. Выделение Имени Отчества из ФИО Формула извлечения Имени Отчества (в ячейке C1):

3. Выделение инициалов (И.О.) из ФИО Формула извлечения И.О. (в ячейке D1):

4. Выделение фамилии и инициалов из ФИО Формула извлечения в виде Фамилия И.О. (в ячейке E1):

5. Выделение имени из ФИО Формула извлечения имени из ФИО (в ячейке F1):

Если имеется ячейка с именем отчеством (C1 в нашем примере), то формула схожа с формулой выделения фамилии:

6. Выделение отчества из ФИО Формула извлечения отчества из ФИО (в ячейке G1):

Если имеется ячейка с именем отчеством (C1 в нашем примере), то формула может иметь вид:

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

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

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

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

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

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

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

Здесь можно использовать формулу «СЦЕПИТЬ», как в примере =СЦЕПИТЬ(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);».»)

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

29 Сен 2018 19:59:16

29 Янв 2017 17:28:40

28 Июн 2018 15:25:11

21 Сен 2018 06:21:34

29 Сен 2018 09:34:03

28 Сен 2018 09:07:46

29 Сен 2018 09:04:53

28 Сен 2018 12:42:51

Сейчас на форуме (гостей: 418, пользователей: 7, из них скрытых: 2) , , , ,

Сегодня отмечают день рождения (32), (37), (30)

Всего зарегистрированных пользователей: 82848

Источник

Adblock
detector