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

Argument ‘Topic id’ is null or empty

Сейчас на форуме

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

ООО «Планета Эксел»
ИНН 7735603520
ОГРН 1147746834949

ИП Павлов Николай Владимирович
ИНН 633015842586
ОГРНИП 310633031600071

Как в 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 выделить имя и фамилию из строки

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

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

Перестановка слов в ФИО в ячейках

Как поставить фамилию после имени и отчества в ячейках Экселя? Например, изначально ФИО стоит так: «Собачкин Андрей Олегович». Мне надо получить «Андрей Олегович Собачкин». Нужна формула, а не макрос.

Отслеживать
Санат Сеитов
задан 2 апр 2021 в 21:45
Санат Сеитов Санат Сеитов
11 3 3 бронзовых знака

Я правильно понял, что Вы хотите, чтобы Эксель как то сам в правильном порядке поставил фамилию, имя и отчество, независимо от того, в каком порядке это напечатал пользователь? Тогда я советую добавить тег Эксель — если тут есть такой. а то люди далекие от «офиса» могут просто не въехать, про какие такие ячейки идёт речь. И, кстати, я сомневаюсь, что есть такая формула, и даже такой макрос.

2 апр 2021 в 21:52

S.H., да, в экселе. Порядок важен. Пользователь уже расписал ФИО в порядке «фамилия, имя, отчество». А мне нужно, что было так: «имя отчество фамилия». Для этого мне нужна формула, а то у меня аж 150 таких ФИО в экселе

2 апр 2021 в 22:07

3 ответа 3

Сортировка: Сброс на вариант по умолчанию

На выбор. Ищем первый пробел и по нему делим текст:

=ПСТР(A2&" "&A2;ПОИСК(" ";A2)+1;ДЛСТР(A2)) =ПСТР(A2;ПОИСК(" ";A2)+1;30)&" "&ЛЕВБ(A2;ПОИСК(" ";A2)-1) =ЗАМЕНИТЬ(A2;1;ПОИСК(" ";A2);)&" "&ЛЕВБ(A2;ПОИСК(" ";A2)-1) =ПОДСТАВИТЬ(A2;ЛЕВБ(A2;ПОИСК(" ";A2));)&" "&ЛЕВБ(A2;ПОИСК(" ";A2)-1)

Функция пользователя ( UDF ), позволяющая расставлять три слова в любом порядке или возвращать повторы слов.

Function fShiftName(sName As String, Optional j1 As Long = 2, _ Optional j2 As Long = 3, Optional j3 As Long = 1) As String Dim aSpl aSpl = Split(sName, " ") fShiftName = aSpl(j1 - 1) & " " & aSpl(j2 - 1) & " " & aSpl(j3 - 1) End Function

Параметры, передаваемые в функцию: ссылка на ячейку (или текст) и три числа, устанавливающие порядок слов. Ссылка обязательна, числа — опционально (по умолчанию слова переставляются в порядке 2, 3, 1 (второе, третье, первое). Порядок по умолчанию можно поменять, изменив значения переменных jx

Функция записывается в общий модуль в редакторе VBA . В ячейке вызывать функцию формулой.

Примеры для исходного текста Первый Второй Третий

=fShiftName(A2) — с параметрами по умолчанию: Второй Третий Первый

=fShiftName(A2;;;1) — то же, что и по умолчанию: Второй Третий Первый

=fShiftName(«Первый Второй Третий»;3;1;2) — другая перестановка: Третий Первый Второй

=fShiftName(«Первый Второй Третий»;3;;3) — повтор слова: Третий Третий Третий

=fShiftName(A2;3;3;1) — повтор с перестановкой: Третий Третий Первый

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