Excel: выделение имени, отчества, инициалов из ФИО
Дано: таблица Excel с колонкой, содержащей фамилию, имя и отчество (ФИО).
Задача: извлечь при помощи формул из колонки ФИО данные в следующем виде:
- Фамилия
- Имя Отчество
- И.О. (инициалы)
- Фамилия И.О.
- Имя
- Отчество
Будем использовать формулы с текстовыми функциями.
Будем предполагать, что исходные данные (ФИО) содержатся в ячейке A1 — «Иванов Сергей Олегович».
1. Выделение фамилии из ФИО
Формула извлечения фамилии (в ячейке B1):
=ЛЕВСИМВ(A1;ПОИСК(" *";A1)-1)
2. Выделение Имени Отчества из ФИО
Формула извлечения Имени Отчества (в ячейке C1):
=ПСТР(A1;ПОИСК(" ";A1)+1;ДЛСТР(A1))
3. Выделение инициалов (И.О.) из ФИО
Формула извлечения И.О. (в ячейке D1):
=ПСТР(A1;ПОИСК(" *";A1)+1;1)&"."&ПСТР(A1;ПОИСК(" *";A1;ПОИСК(" *";A1)+1)+1;1)&"."
4. Выделение фамилии и инициалов из ФИО
Формула извлечения в виде Фамилия И.О. (в ячейке E1):
=ЛЕВСИМВ(A1;ПОИСК(" *";A1)-1)&" "&ПСТР(A1;ПОИСК(" *";A1)+1;1) &"."&ПСТР(A1;ПОИСК(" *";A1;ПОИСК(" *";A1)+1)+1;1)&"."
5. Выделение имени из ФИО
Формула извлечения имени из ФИО (в ячейке F1):
=ПСТР(A1;ПОИСК(" *";A1)+1;ПОИСК(" *";A1;ПОИСК(" *";A1)+1)-ПОИСК(" *";A1)-1)
Если имеется ячейка с именем отчеством (C1 в нашем примере), то формула схожа с формулой выделения фамилии:
=ЛЕВСИМВ(C1;ПОИСК(" *";C1)-1)
6. Выделение отчества из ФИО
Формула извлечения отчества из ФИО (в ячейке G1):
=ПСТР(A1;ПОИСК(" *";A1;ПОИСК(" *";A1)+1)+1;ДЛСТР(A1))
Если имеется ячейка с именем отчеством (C1 в нашем примере), то формула может иметь вид:
=ПРАВСИМВ(C1;ПОИСК(" *";C1)+1)
Если Вам понравилась статья, пожалуйста, поставьте лайк, сделайте репост или оставьте комментарий. Если у Вас есть какие-либо замечания, также пишите комментарии.
Skip to content
ПСТР — одна из текстовых функций, которые Microsoft Excel предоставляет для управления текстовыми строками. На самом базовом уровне она используется для извлечения подстроки из середины текста.
В этом руководстве мы обсудим синтаксис и особенности функции Excel ПСТР (в английской версии – MID), а затем вы узнаете несколько вариантов творческого её использования для выполнения сложных задач.
- Синтаксис и особенности
- Если 2 слова — извлекаем имя и фамилию
- Как получить текст между двумя определенными символами
- Как извлечь любое по счету слово
- Получаем слово с нужными буквами или символами
- Как заставить ПСТР возвращать число?
- Использование регулярных выражений для извлечения части текста
- Как извлечь текст из ячейки с помощью Ultimate Suite
Cинтаксис.
Функция ПСТР возвращает указанное количество знаков, начиная с указанной вами позиции.
Функция Excel ПСТР имеет следующие аргументы:
ПСТР(текст; начальная_позиция; количество_знаков)
Где текст — это исходная текстовая строка. Далее следует позиция первого символа, который вы хотите извлечь, и количество их для извлечения.
Все 3 аргумента обязательны.
Например, чтобы извлечь 6 знаков из A2, начиная с 17-го, используйте эту формулу:
=ПСТР(A2;17;6)
Результат может выглядеть примерно так:
5 вещей, которые вы должны знать о функции Excel ПСТР
Как вы только что убедились, в использовании функции ПСТР в Excel нет ничего страшного. И помня следующие простые факты, вы избежите наиболее распространенных ошибок.
- Функция ПСТР всегда возвращает текстовую строку, даже если извлеченная подстрока содержит только цифры. Это может иметь большое значение, если вы хотите использовать результат формулы ПСТР в других вычислениях. Чтобы преобразовать цифры в число, применяйте ПСТР в сочетании с функцией ЗНАЧЕН (VALUE в английской версии), как показано в этом примере. (ссылка на последний раздел).
- Когда начальная позиция больше, чем общая длина исходного текста, формула Excel ПСТР возвращает пустое значение («»).
- Если начальная позиция меньше 1, формула ПСТР возвращает ошибку #ЗНАЧ!.
- Когда третий аргумент меньше 0 (отрицательное число), формула ПСТР возвращает ошибку #ЗНАЧ!. Если количество знаков для извлечения равно 0, выводится пустая строка (пустая ячейка).
- В случае, если сумма начальной позиции и количества знаков превышает общую длину исходного текста, функция ПСТР в Excel возвращает подстроку начиная с начальной позиции и до последнего символа.
При решении реальных задач в Excel вам чаще всего потребуется использовать ПСТР в сочетании с другими функциями, как показано в следующих примерах.
Как извлечь имя и фамилию.
Если у вас была возможность прочитать наши недавние уроки, вы уже знаете, как вытащить имя с помощью функции ЛЕВСИМВ и получить фамилию с помощью ПРАВСИМВ. Но, как это часто бывает в Excel, одно и то же можно сделать разными способами.
Получаем имя.
Предполагая, что полное имя находится в ячейке A2, имя и фамилия разделены интервалом, вы можете извлечь имя, используя следующую формулу:
=ПСТР(A2;1;ПОИСК(» «;A2)-1)
ПОИСК используется для сканирования исходного значения на предмет пробела (» «) и возврата его позиции, из которой вы вычитаете 1, чтобы избежать пробелов после имени. Затем вы используете ПСТР, чтобы вернуть подстроку, начинающуюся с первого знака и заканчивая предшествующим пробелу, таким образом извлекая первое имя.
Получаем фамилию.
Чтобы извлечь фамилию из A2, используйте эту формулу:
=СЖПРОБЕЛЫ(ПСТР(A2;ПОИСК(» «;A2);ДЛСТР(A2)))
Опять же, вы используете ПОИСК, чтобы определить начальную позицию (пробел). Нам не нужно точно рассчитывать конечную позицию (как вы помните, если вместе взятые начальная позиция и количество символов больше, чем общая длина текста, возвращаются просто все оставшиеся). Итак, в аргументе количество символов вы просто указываете общую первоначальную длину , возвращаемую функцией ДЛСТР . Впрочем, вместо этого вы можете просто ввести число, представляющее самую длинную фамилию, которую вы ожидаете найти, например 100. Наконец, СЖПРОБЕЛЫ удаляет лишние интервалы, и вы получаете следующий результат:
Как выделить подстроку между двумя разделителями.
Продолжим предыдущий пример. А если, помимо имени и фамилии, ячейка A2 также содержит отчество, то как его извлечь?
Технически задача сводится к определению позиций двух пробелов в исходном тексте, и вы можете сделать это следующим образом:
- Как и в предыдущем примере, используйте ПОИСК, чтобы определить позицию первого (» «), к которому вы добавляете 1, потому что вы хотите начать с символа, следующего за ним. Таким образом, вы получаете адрес начальной позиции: ПОИСК (» «; A2) +1
- Затем вычислите позицию 2- го интервала, используя вложенные функции поиска, которые предписывают Excel начать поиск именно со 2-го: ПОИСК (» «; A2, ПОИСК (» «; A2) +1)
Чтобы узнать количество извлекаемых знаков, вычтите позицию первого пробела из положения второго. И затем еще вычтите 1 из результата, поскольку вам не нужны лишние интервалы в получившемся результате. Таким образом, у вас есть второй аргумент для формулы:
ПОИСК(» «; A2; ПОИСК(» «; A2) +1) — ПОИСК(» «; A2)
Соединив все аргументы, мы получаем формулу для извлечения подстроки между двумя пробелами:
=ПСТР(A2;ПОИСК(» «;A2)+1;ПОИСК(» «;A2;ПОИСК(» «;A2)+1)-ПОИСК(» «;A2)-1)
На следующем скриншоте показан результат:
Аналогичным образом вы можете извлечь текст между любыми другими разделителями:
ПСТР( строка ; ПОИСК( разделитель ; строка ) +1; ПОИСК( разделитель ; строка ; ПОИСК( разделитель ; строка ) +1) — ПОИСК( разделитель ; строка ) -1)
Например, чтобы извлечь отрезок, выделенный запятой с пробелом после неё, используйте следующее выражение:
=ПСТР(A2;ПОИСК(«, «;A2)+1;ПОИСК(«, «;A2;ПОИСК(«, «;A2)+1)-ПОИСК(«, «;A2)-1)
На следующем рисунке эта формула используется для извлечения из адреса названия города, и она отлично справляется со своей задачей:
Как получить N-е слово из текста.
Этот пример демонстрирует оригинальное использование сложной формулы ПСТР в Excel, которое включает 5 различных составных частей:
- ДЛСТР — чтобы получить общую длину.
- ПОВТОР — повторение определенного знака заданное количество раз.
- ПОДСТАВИТЬ — заменить один символ другим.
- ПСТР — извлечь подстроку.
- СЖПРОБЕЛЫ — удалить лишние интервалы между словами.
Общая формула выглядит следующим образом:
СЖПРОБЕЛЫ(ПСТР(ПОДСТАВИТЬ( строка ; » «; ПОВТОР (» «; ДЛСТР( строка ))); ( N -1) * ДЛСТР( строка ) +1; ДЛСТР( строка )))
Где:
- Строка — это исходный текст, из которого вы хотите извлечь желаемое слово.
- N – порядковый номер слова, которое нужно получить.
Например, чтобы вытащить второе слово из A2, используйте это выражение:
=СЖПРОБЕЛЫ(ПСТР(ПОДСТАВИТЬ($A$2;» «;ПОВТОР(» «;ДЛСТР($A$2))); (2-1)*ДЛСТР($A$2)+1; ДЛСТР($A$2)))
Или вы можете ввести порядковый номер слова, которое нужно извлечь (N) в какую-либо ячейку, и указать эту ячейку в формуле, как показано на скриншоте ниже:
Как работает эта формула?
По сути, Excel «оборачивает» каждое слово исходного текста множеством пробелов, находит нужный блок «пробелы-слово-пробелы», извлекает его, а затем удаляет лишние интервалы. Чтобы быть более конкретным, это работает по следующей логике:
- ПОДСТАВИТЬ и ПОВТОР заменяют каждый пробел в тексте несколькими. Количество этих дополнительных вставок равно общей длине исходной строки: ПОДСТАВИТЬ($A$2;» «;ПОВТОР(» «;ДЛСТР($A$2)))
Вы можете представить себе промежуточный результат как «астероиды» слов, дрейфующих в пространстве, например: слово1-пробелы-слово2-пробелы-слово3-… Эта длинная строка передается в текстовый аргумент ПСТР.
- Затем вы определяете начальную позицию для извлечения (первый аргумент), используя следующее уравнение: (N-1) * ДЛСТР(A1) +1. Это вычисление возвращает либо позицию первого знака первого слова, либо, чаще, позицию в N-й группе пробелов.
- Количество букв и цифр для извлечения (второй аргумент) — самая простая часть — вы просто берете общую первоначальную длину: ДЛСТР(A2).
- Наконец, СЖПРОБЕЛЫ избавляется от начальных и конечных интервалов в извлечённом тексте.
Приведенная выше формула отлично работает в большинстве ситуаций. Однако, если между словами окажется 2 или более пробелов подряд, это даст неверные результаты (1). Чтобы исправить это, вложите еще одну функцию СЖПРОБЕЛЫ в ПОДСТАВИТЬ, чтобы удалить лишние пропуски между словами, оставив только один, например:
=СЖПРОБЕЛЫ(ПСТР(ПОДСТАВИТЬ(СЖПРОБЕЛЫ($A$2);» «; ПОВТОР(» «;ДЛСТР($A$2))); (B2-1)*ДЛСТР($A$2)+1; ДЛСТР($A$2)))
Следующий рисунок демонстрирует улучшенный вариант (2) в действии:
Если ваш исходный текст содержит несколько пробелов между словами, а также очень большие или очень короткие слова, дополнительно вставьте СЖПРОБЕЛЫ в каждое ДЛСТР, чтобы вы были застрахованы от ошибки:
=СЖПРОБЕЛЫ(ПСТР(ПОДСТАВИТЬ(СЖПРОБЕЛЫ($A$2);» «; ПОВТОР(» «;ДЛСТР(СЖПРОБЕЛЫ ($A$2)))); (B2-1)*ДЛСТР(СЖПРОБЕЛЫ($A$2))+1; ДЛСТР(СЖПРОБЕЛЫ($A$2))))
Я согласен с тем, что это выглядит немного громоздко, но зато безупречно обрабатывает все возможные варианты.
Извлекаем слово, содержащее определенный символ.
В этом примере показана еще одна нестандартная формула Excel ПСТР, которая извлекает слово, содержащее определенную букву или цифру, из любого места:
СЖПРОБЕЛЫ(ПСТР(ПОДСТАВИТЬ ( строка , » «, ПОВТОР(» «, 99)), МАКС(1, НАЙТИ( символ , ПОДСТАВИТЬ( строка , » «, ПОВТОР(» «, 99))) — 50), 99))
Предполагая, что исходный текст находится в ячейке A2, и вы хотите получить слово, содержащее символ «$» (цена), выражение принимает следующую форму:
=СЖПРОБЕЛЫ(ПСТР(ПОДСТАВИТЬ(A2;» «;ПОВТОР(» «;99)); МАКС(1;НАЙТИ(«$»;ПОДСТАВИТЬ(A2;» «;ПОВТОР(» «;99)))-50);99))
Аналогичным образом вы можете извлекать адреса электронной почты (на основе знака «@»), имена веб-сайтов (на основе «www») и так далее.
Теперь разберём пошагово:
Как и в предыдущем примере, ПОДСТАВИТЬ и ПОВТОР превращают каждый пробел в исходном тексте в несколько, точнее, в 99.
НАЙТИ находит позицию нужного символа (в данном примере $), из которой вы вычитаете 50. Это возвращает вас на 50 позиций назад и помещает где-то в середине блока из 99 пробелов, который предшествует слову, содержащему указанный символ.
МАКС используется для обработки ситуации, когда нужное значение появляется в начале исходного текста. В этом случае результат ПОИСК() — 50 будет отрицательным числом, а МАКС(1, ПОИСК() — 50) заменяет его на 1.
С этой начальной точки ПСТР отбирает следующие 99 знаков и возвращает интересующее нас слово, окруженное множеством пробелов. Как обычно, СЖПРОБЕЛЫ помогает избавиться от лишних из них, оставив только один.
Совет. Если извлекаемый отрезок очень большой, замените 99 и 50 на более крупные числа, например 1000 и 500.
Как заставить ПСТР возвращать число?
Как и другие текстовые функции, Excel ПСТР всегда возвращает текст, даже если он содержит только цифры и очень похож на число. Вы можете убедиться с этом, взглянув на пример чуть выше, когда мы получили число «20%» как текст.
Чтобы преобразовать результат в число, просто передайте полученный результат в функцию ЗНАЧЕН (VALUE в английской версии), которая преобразует текстовое значение, состоящее из цифр, в число.
Например, чтобы извлечь подстроку из 3 символов, начинающуюся с 7- го символа, и преобразовать ее в число, используйте:
=ЗНАЧЕН(ПСТР(A2;7;3))
На скриншоте ниже показан результат. Обратите внимание, что числа с выравниванием по правому краю помещены в столбец B, в отличие от исходных текстовых значений с выравниванием по левому краю в столбце A:
Тот же подход работает и для более сложных случаев. В приведенном выше примере, предполагая, что коды ошибок имеют переменную длину, вы можете извлечь их с помощью ПСТР, которая получает подстроку между двумя разделителями, вложенную в ЗНАЧЕН:
=ЗНАЧЕН(ПСТР(A2;ПОИСК(«:»;A2)+1;ПОИСК(«:»;A2;ПОИСК(«:»;A2)+1)-ПОИСК(«:»;A2)-1))
Вот как можно использовать функцию ПСТР в Excel.
Использование регулярных выражений, чтобы извлечь часть текста.
Регулярное выражение – это шаблон, состоящий из последовательности символов, который можно использовать для поиска соответствующей последовательности в другой строке.
Как добавить в Excel пользовательскую функцию работы с регулярными выражениями и как правильно составить шаблон – читайте подробную инструкцию здесь.
Вы можете извлечь из ячейки все числа или все буквы, телефонный номер, адрес электронной почты, дату, время, текст между любыми двумя символами или двумя словами, текст после определенного символа, и еще множество вариантов.
Вот пример: из наименования товара — Коммутатор Optimus U1E-8F/1G/1S, нужно извлечь номер модели. Поскольку этот номер находится в конце наименования, то будем искать то, что записано после последнего пробела.
= RegExpExtract(A1; «([^s.]+)$»)
В результате получим U1E-8F/1G/1S, что и требовалось.
Как извлечь текст из ячейки с помощью Ultimate Suite
Как вы только что видели, Microsoft Excel предоставляет набор различных функций для работы с текстовыми строками. Если вам нужно извлечь какое-то слово или часть текста из ячейки, но вы не уверены, какая функция лучше всего подходит для ваших нужд, передайте работу Ultimate Suite for Excel. Заодно не придётся возиться с формулами.
Вы просто переходите на вкладку Ablebits Data > Текст, выбираете инструмент Split Text и в выпадающем списке нажимаете Извлечь (Extract) :
Теперь вы выбираете исходные ячейки, и какие бы сложные строки они ни содержали, извлечение из них подстроки сводится к этим двум простым действиям:
- Укажите, сколько символов вы хотите получить из начала, конца или середины строки; или выберите извлечение всего текста до или после определенного символа.
- Щелкните Вставить результаты (Insert Results). Готово!
Кроме того, вы можете извлечь любое число символов с начала или в конце текста, из середины текста, между какими-то символами. Например, чтобы извлечь доменные имена из списка адресов электронной почты, вы выбираете чекбокс Все после текста (All after text) и вводите @ в поле рядом с ним. Чтобы извлечь имена пользователей, выберите переключатель Все до текста (All before text), как показано на рисунке ниже.
Помимо скорости и простоты, инструмент «Извлечь текст» имеет дополнительную ценность — он поможет вам изучить формулы Excel в целом и функции подстроки в частности. Как? Выбрав флажок Вставить как формула (Insert as formula) в нижней части панели, вы убедитесь, что результаты выводятся в виде формул, а не просто как значения. Естественно, эти формулы вы можете использовать в других таблицах.
В этом примере, если вы выберете ячейки B2 и C2, вы увидите следующие формулы соответственно:
- Чтобы извлечь имя пользователя:
=ЕСЛИОШИБКА(ЛЕВСИМВ(A2,ПОИСК(«@»,A2)-1),»»)
- Чтобы извлечь домен:
=ЕСЛИОШИБКА(ПРАВСИМВ(A2, ДЛСТР(A2)- ПОИСК(«@»,A2) — ДЛСТР(«@») + 1),»»)
Сколько времени вам потребуется, чтобы самостоятельно составить эти выражения? 
Поскольку результаты представляют собой формулы, извлеченные подстроки будут обновляться автоматически, как только в исходные ячейки будут внесены какие-либо изменения. Когда в ваш набор данных добавляются новые записи, вы можете скопировать формулы в другие ячейки как обычно, без необходимости заново запускать инструмент «Извлечь текст».
Если вам интересно попробовать это, а также множество других полезных функций, включенных в Ultimate Suite for Excel, вы можете загрузить ознакомительную версию. Если вам нравятся инструменты, вы можете получить лицензию по очень специальной цене, которая доступна только для наших русскоязычных пользователей.
Благодарю вас за чтение и надеюсь увидеть вас в нашем блоге на следующей неделе!
Как быстро посчитать количество слов в Excel — В статье объясняется, как подсчитывать слова в Excel с помощью функции ДЛСТР в сочетании с другими функциями Excel, а также приводятся формулы для подсчета общего количества или конкретных слов в… 
Как быстро извлечь число из текста в Excel — В этом кратком руководстве показано, как можно быстро извлекать число из различных текстовых выражений в Excel с помощью формул или специального инструмента «Извлечь». Проблема выделения числа из текста возникает достаточно… 
Как удалить пробелы в ячейках Excel — Вы узнаете, как с помощью формул удалять начальные и конечные пробелы в ячейке, лишние интервалы между словами, избавляться от неразрывных пробелов и непечатаемых символов. В чем самая большая проблема с… 
Функция СЖПРОБЕЛЫ — как пользоваться и примеры — Вы узнаете несколько быстрых и простых способов, чтобы удалить начальные, конечные и лишние пробелы между словами, а также почему функция Excel СЖПРОБЕЛЫ (TRIM в английской версии) не работает и как… 
Функция ПРАВСИМВ в Excel — примеры и советы. — В последних нескольких статьях мы обсуждали различные текстовые функции. Сегодня наше внимание сосредоточено на ПРАВСИМВ (RIGHT в английской версии), которая предназначена для возврата указанного количества символов из крайней правой части… 
Функция ЛЕВСИМВ в Excel. Примеры использования и советы. — В руководстве показано, как использовать функцию ЛЕВСИМВ (LEFT) в Excel, чтобы получить подстроку из начала текстовой строки, извлечь текст перед определенным символом, заставить формулу возвращать число и многое другое. Среди… 
5 примеров с функцией ДЛСТР в Excel. — Вы ищете формулу Excel для подсчета символов в ячейке? Если да, то вы, безусловно, попали на нужную страницу. В этом коротком руководстве вы узнаете, как использовать функцию ДЛСТР (LEN в английской версии)… 
Как быстро сосчитать количество символов в ячейке Excel — В руководстве объясняется, как считать символы в Excel. Вы изучите формулы, позволяющие получить общее количество символов в диапазоне и подсчитывать только определенные символы в одной или нескольких ячейках. В нашем предыдущем…
Иногда кажется, что компьютеры и другие устройства (телефоны, смартфоны, планшеты и прочее) понимают нас с полуслова, с одного жеста, или даже читают мысли. Действительно, современные компьютеры и их «младшие братья и сестры» выполнены классно! Но кто и как сделал их такими понятливыми? Для наглядности рассмотрим этот вопрос на примере, как компьютер может извлечь фамилию из ФИО в Excel.
Содержание:
1. Думают ли компьютеры и можно ли научить их думать
2. Фамилия из ФИО в Excel: как ее извлечь
3. Как извлечь подстроку из строки текста в Excel
4. Функция ПСТР в Excel, чтобы выделить фамилию из ФИО
5. Функция СЖПРОБЕЛЫ в Excel для удаления лишних пробелов в строках текста
6. Извлечение фамилии любой длины из ФИО в Excel
7. Функция НАЙТИ в Excel для определения положения символа в тексте
8. Совместное применение трех функций Excel: ПСТР, СЖПРОБЕЛЫ и НАЙТИ
9. Видео: Извлечение фамилии из ФИО в Excel
10. Все ли задачи под силу программистам
11. Об искусстве программирования
Умеют ли думать компьютеры
Процессоры внутри современных компьютеров и иных устройств остались почти такими, как они были на заре автоматизации. Нет, они, конечно, лучше: уменьшился размер, повысилась скорость работы, возросла разрядность – число двоичных знаков, которые процессор может обрабатывать за один такт работы. Быстродействие процессоров стало колоссальным по сравнению с тем, что было когда-то в начале пути.
И все-таки процессоры внутри устройств остались почти такими же. Современные процессоры по-прежнему работают с ограниченным набором команд. Они понимают лишь нескольких сотен команд. Причем команды так и остались из разряда «сложить», «умножить», «сравнить больше или меньше», «перейти к следующей команде» и тому подобное.
Процессоры умеют считать. Они не умеют думать, и никогда этому не научатся, в том виде, в той конфигурации, как они есть. Их умения сводятся к незамысловатому списку: складывать, умножать, сравнивать. И только программисты могут добиться, чтобы устройство делало вид, что оно думает почти как человек. Почти!
Как научить компьютеры думать
Чтобы компьютер или другое устройство могло уверенно общаться с человеком (пользователем), его нужно запрограммировать. Программисты умеют переводить наши человеческие запросы, чаяния, желания на язык бездушного (но удивительно быстрого!) процессора. Именно программисты делают так, чтобы компьютер превращался в умного помощника.
Перевод нашего человеческого мышления на язык цифр, который понимает компьютер, – это дело рук программистов. Только программисты знают, как оцифровать наше во многом непредсказуемое и потому непрограммируемое человеческое мышление.
Программирование – непростое занятие. Хотя для современных программистов сделано немало полезных приложений, языков программирования, эмуляторов, имитаторов и прочее. Им значительно проще, чем тем, кто начинал подобную работу лет 30-50 тому назад. Все равно современным программистам сложно. Но они – справляются!
Давайте на простом примере покажем отличия нашего человеческого мышления от тех возможностей, которыми на самом деле располагают программисты. Это поможет нам лучше понять, как мыслят и действуют программисты при решении поставленных задач. Понять, как программисты оцифровывают задачи для компьютеров.
Фамилия из ФИО в Excel: как ее извлечь
Пример возьмем следующий. Допустим, перед нами фамилия, имя и отчество некоего человека: Завьялова Анна Петровна. Никто конкретно не имеется в виду, просто случайно выбранная ФИО (фамилия, имя, отчество). Задача для программиста будет следующая: выделить с помощью программного кода из ФИО одну лишь фамилию.
Казалось бы, в чем проблема? Что, разве не видно, что фамилия указанного человека – Завьялова? Да, видно тем, кто умеет читать. Но компьютер не умеет читать. Он не может смотреть как мы и не может думать, как мы. Он умеет только считать. Как «сосчитать» из ФИО одну только фамилию? В этом-то и будет заключаться задача, которую мы ставим перед гипотетическим программистом.
Для решения задачи давайте использовать табличный редактор Microsoft Excel, у которого также есть разные аналоги, например, таблицы Liber Office и др. Итак, допустим, мы – программисты. Сейчас мы попробуем с помощью программного кода извлечь фамилию из ФИО в Excel.
На новом чистом листе табличного редактора Excel в ячейку B2 вводим ФИО (Завьялова Анна Петровна) и выделяем ее желтым цветом для наглядности. Также размечаем таблицу наших последующих действий (рис. 1).
Рис. 1. Ввод ФИО в ячейку B1 и предварительная разметка листа табличного редактора Microsoft Excel.
В столбце с заголовком № п/п мы будем записывать цифры 1, 2, 3 и так далее – это предстоящие попытки извлечения фамилии из ФИО.
Далее в столбце Фамилия будут размещаться результаты наших попыток.
В столбце Вычисления мы покажем, с помощью каких расчетов удается извлечь требуемую фамилию из ФИО.
Извлечение подстроки из строки текста в Excel
Начнем первую попытку. Давайте посмотрим, как можно из строки текста извлечь некоторую его часть. Перед нами строка: Завьялова Анна Петровна. С точки зрения человека, мы видим тут осмысленный текст. Нет никаких проблем что-либо их этого текста извлечь. Вам фамилию? Извольте: Завьялова!
Однако, с точки зрения компьютера, который умеет только считать, перед его «глазами» расположена лишь последовательность символов: З, а, в, ь, я, л, о, в, а, [пробел] (это тоже символ, равный по значению любому другому символу текста), А, н, н… И так далее. Получается, что компьютеру нужно из всей строки взять только несколько первых символов, начиная с символа З, и заканчивая символом а.
Так пусть возьмет! А как? У компьютера нет глаз, рук и прочего. Компьютер умеет только выполнять заложенные в него программы. Программу (код) надо кому-то написать. Вот мы и начнем писать.
Так как процессор компьютера умеет только считать, придется написать код так, чтобы он мог быть выполнен с помощью каких-либо расчетов. Поэтому нам придется «сказать компьютеру» (иными словами, запрограммировать) примерно следующее: «Из строки Завьялова Анна Петровна, пожалуйста, извлеки подстроку, начиная с символа с порядковым номером 1, и заканчивая символом с порядковым номером 9».
В строке Завьялова Анна Петровна первый символ – это З. Девятый символ – это а. А все, что находится между первым и девятым символами строки – это и есть фамилия Завьялова. Согласны? Теперь попробуем нашу мысль, сформулированную по-русски, перевести на язык программирования, понятный компьютеру.
Функция ПСТР в Excel для выделения фамилии из ФИО
Каким языком программирования мы воспользуемся? Формулы Excel вполне для этого подойдут, раз уж мы выбрали Эксель для нашего примера. В Excel есть функция ПСТР (сокращение от «подстрока»). В формуле ПСТР нужно:
- указать исходную строку текста (это ячейка B1 на рис. 1),
- затем ввести первое число (это 1), указывающего на номер начального символа подстроки,
- и ввести второе число (это 9), указывающего на номер последнего символа подстроки.
Таким образом мы пишем следующую расчетную формулу:
= ПСТР (B1; 1; 9)
– вот так это выглядит в исполнении для компьютера, для Excel.
Вводим указанную формулу в ячейку B4, и получаем результат: фамилию Завьялова в ячейке B4 (рис. 2):
Рис. 2. Результат применения формулы ПСТР в табличном редакторе Excel для извлечения Фамилии из ФИО.
Единичку слева мы приписали вручную – это наша первая (удачная!) попытка извлечь фамилию. Формулу справа мы записали лишь для наглядности нашего примера. В реальной рабочей программе дополнительное размещение формулы в соседней ячейке справа не нужно.
Итак, извлечь Фамилию из ФИО в Excel получилось! Мы имеем то, что хотели с помощью программы на компьютере. Мы смогли из ФИО извлечь первые 9 символов – это и есть искомая фамилия. Главное: нам удалось оцифровать задачу.
Мы сумели цифрами объяснить программе, компьютеру, процессору, какие данные он должен откуда взять и как их обработать. Главные цифры нашей небольшой успешной программы – это 1 и 9, начало и конец подстроки текста. Компьютер в цифрах знает толк, понимает их, делает то, что нам нужно. Как видим, ни о каких буквах (а ФИО – это буквы!) тут речь не идет. Вместо букв мы оперируем их порядковыми номерами в строке. Вот вам и оцифровка: по номерам позиций букв в строке!
Но на этом, к сожалению, работа с кодом (программой) не заканчивается. Будем продолжать.
Функция СЖПРОБЕЛЫ в Excel для удаления лишних пробелов в тексте
ФИО мы вводили вручную в ячейку B1. При ручном вводе не исключены ошибки. Допустим, в начале ФИО мы поставили бы пару-тройку лишних пробелов. Какой бы мы получили результат вычислений? Давайте посмотрим (рис. 3):
Рис. 3. Результат работы функции ПСТР в Excel, если в начале исходной строке случайно поставлены лишние пробелы.
Прекрасно видно, что первые три лишних пробела остались в тексте Фамилии в ячейке B4, поэтому фамилия сократилась ровно на 3 символа и стала Завьял. Результат – неверный! Подобные ошибки ввода не исключены, и нам (программистам) надо их предусмотреть.
Для удаления лишних пробелов в тексте в Excel применяется функция «сжать пробелы», которая выглядит так: СЖПРОБЕЛЫ. В качестве аргумента этой функции надо подставить исходную строку.
Функция СЖПРОБЕЛЫ удаляет все пробелы, которые стоят в начале исходной строки. Также она удаляет все пробелы в самом конце исходной строки, и оставляет строго по одному пробелу, если пробелы дублируются в середине строки.
В нашем примере исходный текст ФИО находится в ячейке B1, поэтому мы так и запишем: СЖПРОБЕЛЫ (B1).
Этот новый текст СЖПРОБЕЛЫ (B1) надо подставить вместо ячейки B1. В окончательном виде формула расчета, которую мы запишем в ячейку B5, будет выглядеть так:
=ПСТР ( СЖПРОБЕЛЫ (B1); 1; 9)
Результат налицо: получаем требуемую фамилию Завьялова теперь уже в ячейке B5 (рис. 4).
Рис. 4. Исключение лишних пробелов в исходной строке текста Excel с помощью функции СЖПРОБЕЛЫ.
Слева добавим вручную «двойку» 2, а справа повторим нашу формулу для наглядности. Теперь всё? – спросит нетерпеливый читатель. Ответим, увы, нет! Потому что могут быть еще проблемные ситуации. Следовательно, продолжаем.
Как извлечь фамилию любой длины из ФИО в Excel
Как поведет себя наша программа (код), если мы вместо прежней ФИО подставим в ячейку B1 совсем другую строку? Например, Завгороднев Игорь Петрович (рис. 5).
Рис. 5. Проверяем, как поведет себя написанная программа в Excel после подстановки в нее другой исходной строки ФИО.
Совершенно очевидно, что программа (код) не сработала так, как нужно. Мы получили какой-то кусочек фамилии – Завгородн. Почему так вышло? Потому что мы извлекаем из исходной строки ФИО подстроку строго определенной длины, с первого до девятого символа. Но ведь далеко не все возможные существующие фамилии имеют длину 9 символов!
Значит, придется нам программу (код) изменить так, чтобы компьютер мог извлечь фамилию любой длины. Как это сделать программисту? Встаем в его положение. Он думает, думает, и… понимает, что любая фамилия в ФИО всегда заканчивается пробелом. Пробел отделяет фамилию от имени.
Значит, понимает программист, надо попытаться найти номер позиции в ФИО, в которой стоит пробел. В фамилии Завьялова – это 10-я позиция. В фамилии Завгороднев – это уже12-я позиция. Видите, программист пытается оцифровать задачу, чтобы потом эти цифры донести до компьютера. Иначе – никак, компьютер понимает лишь язык цифр, он умеет только считать!
Функция НАЙТИ в Excel для поиска нужного символа в строке текста
В Excel есть функция, которая позволяет определить номер интересующего символа в строке текста. Это функция НАЙТИ. В ней указывают сначала, что нужно найти в строке, а затем саму строку.
Значит, записываем: НАЙТИ (“ “ ; B1). Что это означает? Ищем пробел, который обрамлен в кавычки – так записывают в Excel отдельные символы. И ищем пробел в строке, которая записана в ячейке B1 – там у нас размещена ФИО. А еще в приведенной функции НАЙТИ вместо B1 нам придется записать функцию СЖПРОБЕЛЫ (B1) для исключения лишних пробелов:
НАЙТИ (“ “ ; СЖПРОБЕЛЫ (B1) )
В результате функция выдаст номер позиции (искомую цифру, именно цифру!!!) в ФИО, где стоит пробел. Мы этот номер уменьшим на единицу (ведь фамилия короче на 1 символ как раз из-за этого разделяющего пробела). И затем подставим функцию НАЙТИ вместо цифры 9 в прежнюю функция ПСТР.
В итоге цифра 9 будет заменена вычислением. Расчет позволяет динамически изменить цифру – это номер последнего символа в фамилии. Значит, теперь при подстановке любой фамилии в ячейку B1, наша формула будет автоматически и правильно находить последний символ фамилии.
Совместное применение функций Excel: ПСТР, СЖПРОБЕЛЫ и НАЙТИ
Итак, пишем формулу расчета для выделения фамилии любой длины из ФИО:
= ПСТР ( СЖПРОБЕЛЫ(B1); 1; НАЙТИ ( “ “; СЖПРОБЕЛЫ ( B1) ) -1 )
Переведем на русский язык, что мы только что записали:
- Из строки, которая образуется на основе данных из ячейки B1 (если из нее удалить все лишние пробелы – СЖПРОБЕЛЫ (B1) ), надо извлечь подстроку.
- Подстрока начинается на первом символе – это единичка (1) после первой точки с запятой.
- Подстрока заканчивается на символе «пробел» (“ “) за минусом одной позиции (-1).
Итог работы такого теперь уже достаточно сложного и неочевидного расчета – это фамилия Завгороднев в ячейке B6, куда мы поместили наш расчет (рис. 6). Ура, сработало!
Рис. 6. Пример совместного использования функций Excel ПСТР и НАЙТИ для извлечения фамилии произвольной длины из строки ФИО.
Также, как и в прошлые разы, слева записываем цифру 3 (номер очередной попытки извлечения фамилии), а справа повторяем ввод функции для наглядности.
Извлечение фамилии из ФИО в Excel: пример мини программы
Все ли под силу программистам
Вот так, с третьей попытки нам (программистам) удалось извлечь ЛЮБУЮ фамилию из ЛЮБОЙ ФИО. Тогда как вручную любой человек эту работу сделал бы без таких проблем, связанных с оцифровкой задачи для компьютера. Правда, если бы таких ФИО было, скажем, миллион, то человек уже не справится. А компьютер с его сумасшедшей производительностью не то что миллион, и миллиард, пожалуй, сможет потянуть.
В этом-то и заключается суть. Чтобы сделать что-то один раз, для этого компьютер не нужен. А вот выполнять повторяющиеся действия человеку сложно или невозможно. Требуется компьютер для повторяющихся множественных действий.
Но все ли возможные проблемы решены выше? Конечно, нет. В реальной жизни может быть такое, что сложно даже представить, не то, чтобы перечислить. Например, вместо буквы «З» в начале фамилии будет поставлена цифра 3. Внешне символы выглядят одинаково.
Наша только что написанная программа спокойно сработает с «тройкой». Правда, если потом придется фамилии расположить в алфавитном порядке, «тройка» встанет перед «А», а не после «Ж». Непорядок! Значит, надо научить компьютер распознавать «тройки» и автоматически заменять их на буквы «З». Можно также перепутать и другие пары цифры-буквы, например, 0 и «О», а также 4 и «Ч».
При ручном вводе можно спутать верхний и нижний регистры. Исходную фамилию можно ввести с маленькой буквы. Вместо маленьких букв не исключено ошибочное использование больших заглавных букв.
Снова встает задача для программиста: предусмотреть автоматическое распознавание неправильно выбранного регистра и замену больших букв на маленькие, либо маленьких на большие там, где нужно. Оцифровка такой задачи потребует определенных сил и времени, не так это просто, как кажется на первый взгляд.
Возьмем еще более сложную задачу. Что делать компьютеру, если ФИО напечатана наоборот: имя, отчество, фамилия? Придется искать фамилию не с первой позиции до первого пробела (как мы это сделали выше), а со второго пробела и до конца строки. Это намного сложнее сформулировать программисту.
Какие задачи могут быть неразрешимыми для программистов
И совсем неразрешимая задача, если отдельные ФИО будут введены «правильно» (фамилия, имя, отчество), а другие «неправильно» (имя, отчество, фамилия). Как обучить компьютер находить фамилию в тексте, если фамилия не будет стоять на своем строго закрепленном месте?
Фамилия – первая или третья в строке? Глазами-то мы увидим фамилию сразу. Но как оцифровать данную задачу?! Ведь для компьютера, как мы немного убедились выше, нужно однозначно указать, с какого символа по какой символ располагается фамилия в тексте ФИО. Однозначно указать не получится – это может быть с первого символа до первого пробела, либо со второго пробела и до конца. Что значит «либо»? С первого символа или со второго пробела?!
Вообще, самое сложное в программировании, когда исходные данные совсем не структурированы. Если программистам ставят задачи, в которых нет хотя бы элементарного порядка, оцифровать алгоритмы обработки таких текстов крайне сложно. В нашем случае с перепутанным расположением фамилий могут потребоваться огромные дополнительные таблицы, содержащие все известные фамилии. Программист должен будет написать код, который сравнивает строку ФИО с таблицей фамилий с целью поиска в ней подходящей фамилии.
Либо придется писать так называемые эвристические алгоритмы и программы, наделяющие компьютер определенными элементами искусственного интеллекта. Так работают, например, системы распознавания визуальных образов для поиска людей по их изображению с камер видеонаблюдений.
В общем, иногда проще предварительно навести некоторый порядок в работе, подлежащей автоматизации. И только после этого приступать к программированию рабочих действий. Так проще, чем (часто безуспешно) создавать эвристические алгоритмы и программы.
Об искусстве программирования
Список возможных задач для программирования или возникающих проблем при кодировании можно продолжать до бесконечности. С большинством известных задач программисты справляются, иначе бы у нас не было любимых и полезных компьютеров и иных устройств.
Вручную человек легко решает задачи подобного рода, например, извлечение фамилии из ФИО. Для этого у человека есть разум, который помогает ему в поиске и выборе. У компьютера и его основного «работника» процессора разума НЕТ! Процессор умеет только считать. Цифры для компьютера должны быть окончательными и однозначными.
Программистам приходится превращать в цифры, в манипуляции с цифрами всё, что мы обычно делаем силой своего разума. Например, мы видим ФИО не как набор символов, а как четкий образ. Мы можем любой образ преобразовать во что угодно, хоть нарисовать от руки предполагаемый портрет персонажа, скрывающегося под ФИО.
Компьютер же может только считать, повторю это в который раз! Складывать, умножать, сравнивать… Он даже вычитает путем сложения с отрицательными числами, и делит не совсем так, как мы могли бы сделать, скажем, «столбиком».
Программисты – это те, кто умеет, практически, любые человеческие действия превратить в движение чисел. Тем самым они создают для нас незаменимых помощников: компьютеры, ноутбуки, планшеты, смартфоны, телефоны и многое другое, где применяют процессоры для вычислений.
Программирование (иными словами кодирование) – это искусство перевода человеческого мышления на язык цифр! Это именно искусство, поскольку невозможно заранее и для всех мыслимых и немыслимых случаев сделать четкие инструкции. Были бы инструкции – работа программистов напоминала бы ремесло. Сам же процесс составления инструкций выглядел бы в некотором смысле прототипом науки. Но тут не ремесло и не наука, а именно искусство!
В каждом конкретном случае программист должен искать и находить пути оцифровки задачи, действуя творчески. Мы хотим многого, а программисту требуется придумать цифровые алгоритмы исполнения наших желаний. Оцифровка потребовалась даже в простеньком продемонстрированном примере: выделение фамилии из более длинной строки с текстом фамилии, имени и отчества произвольного человека.
Другие материалы:
1. Основные понятия Excel и поиск данных в таблице с использованием функции ВПР
2. Деление в Excel двух чисел и работа над ошибками при вводе данных
3. Как определить срок покупки железнодорожных билетов с помощью Microsoft Excel
4. Что такое переменная с индексами, массив, комментарий, цикл и счетчик в программировании на конкретном примере
Получайте новые статьи по компьютерной грамотности на ваш почтовый ящик:
Необходимо подтвердить подписку в своей почте. Спасибо!
Превращаем полное ФИО в Фамилию + Инициалы.
Если Фамилия, Имя и Отчество (ФИО) хранятся в одной ячейке (например, в
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 и присоединение их к фамилии. Разделение фамилий, имен и отчеств, расположенных в одной ячейке, по разным ячейкам и наоборот.
Фамилия, имя и отчество в разных ячейках
Если фамилия, имя и отчество записываются в разные ячейки, работать с ними легче, чем при размещении их в одной ячейке. Перечислим преимущества:
- при заполнении таких ячеек в таблицах срабатывает автоподбор значений, так как имена и отчества часто повторяются;
- для заполнения таких ячеек можно использовать раскрывающиеся списки наиболее распространенных имен и отчеств;
- фамилия, имя и отчество, записанные в разные ячейки, легко объединить в одну, а имя и отчество заменить инициалами.
Итак, объединяем фамилию, имя и отчество полностью из разных ячеек в одну:
Здесь можно использовать формулу «СЦЕПИТЬ», как в примере =СЦЕПИТЬ(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)&"."
Вы можете копировать эти формулы в свои файлы, не забывая изменять адреса ячеек на фактические. Часто инициалы пишут перед фамилией, изменить формулы для такого отображения ФИО несложно.
Извлечь имя и фамилию из полного имени
На чтение 4 мин. Просмотров 13 Опубликовано 28.05.2021
Предположим, у вас есть список полных имен, теперь вы хотите извлечь первое имя и фамилию или имя и первый символ фамилии из полного имени, как показано ниже. . Как бы вы могли справиться с этими задачами в книге Excel?
- Извлечь первые инициалы и фамилию из полного имени в Excel
- Извлечь имя и первый символ фамилии из полного имени в Excel
Содержание
- Извлечь первые инициалы и фамилию из полного имени в Excel
- Извлечь имя и первый символ фамилии из полного имени в Excel
- Используемые относительные функции:
- Другие статьи:
- – -> Лучшие инструменты для повышения производительности в офисе
Извлечь первые инициалы и фамилию из полного имени в Excel
Для извлечения первого инициала и фамилии комбинация функций LEFT, RIGHT, LEN и FIND может оказать вам услугу, общий синтаксис:
- : полное имя или значение ячейки, которое вы хотите использовать.
Введите или скопируйте следующую формулу в пустую ячейку:
Затем, перетащите дескриптор заполнения вниз к ячейкам, к которым вы хотите применить эту формулу, и вы получите все сначала инициалы и фамилия были извлечены, см. снимок экрана:
Пояснение к формуле:
1. LEFT (A2,1) : эта функция LEFT используется для извлечения первого символа ячейки A2. Это будет первая буква «J».
2. ВПРАВО (A2; LEN (A2) -НАЙТИ (“”, A2) +1) :
- НАЙТИ (“”, A2) : эта функция НАЙТИ возвращает позицию символа пробела. Он получит число 6.
- LEN (A2) -FIND (“”, A2) +1 : длина A2 вычитает номер позиции. пробел, добавление 1 означает включение пробела при извлечении данных. Это будет количество символов, которые будут возвращены с конца ячейки A2. Это распознается как num_chars функции RIGHT.
3. LEFT (A2,1) & RIGHT (A2, LEN (A2) -FIND (“”, A2) +1) : наконец, использование символа & для соединения первого инициала, возвращаемого функцией LEFT, и фамилии. который возвращается функцией RIGHT.
Извлечь имя и первый символ фамилии из полного имени в Excel
Если вам нужно извлечь имя и первый символ фамилии, вам могут помочь функции LEFT и FIND. Общий синтаксис:
- : полное имя или значение ячейки, которое вы хотите использовать.
Скопируйте или введите следующую формулу в пустую ячейку:
Затем перетащите дескриптор заполнения вниз к ячейкам, к которым вы хотите применить эту формулу, и все имена и первый символ фамилий были извлечены, см. снимок экрана:
Пояснение к формуле:
FIND (“”, A2) +1 : функция FIND используется для возврата позиции символа пробела в ячейке A2, добавление 1 означает получение позиции первого символа после пробела. Это будет распознано как аргумент num_chars в функции LEFT.
LEFT (A2, FIND (“”, A2) +1) : эта функция LEFT является используется для извлечения количества символов, возвращаемых функцией НАЙТИ из левой части ячейки A2.
Используемые относительные функции:
- :
- Функция LEFT извлекает заданное количество символов из левой части предоставленная строка.
- :
- Функция ВПРАВО используется для извлечения определенного количества символов из правой части текстовой строки
- :
- Функция LEN возвращает количество символов в текстовой строке.
- :
- Функция НАЙТИ используется для поиска строки в другой строке и возвращает начальную позицию строки внутри другой
Другие статьи:
- Перевернуть или перевернуть имя и фамилию в списке Excel
- Если у вас есть список полных имен, которые отформатированы как фамилия и имя, теперь вы хотите перевернуть фамилию и имя на имя и фамилию, как показано на скриншоте ниже. В этой статье я представлю некоторые формулы для решения этой задачи в Excel.
- Извлечь несколько Строки из ячейки
- Если у вас есть список текстовых строк, разделенных разрывами строки (это происходит при нажатии клавиш Alt + Enter при вводе текста), и теперь вы хотите извлечь эти строки текста в несколько ячеек, как показано на скриншоте ниже. Как вы могли бы решить эту проблему с помощью формулы в Excel?
- Извлечь N-е слово из текстовой строки в Excel
- Если у вас есть список текстовых строк или предложений, теперь вы хотите извлечь конкретное n-е слово из списка, как показано на скриншоте ниже. В этой статье я расскажу о некоторых методах решения этой задачи в Excel..
- Извлечь текст между скобками из текстовой строки
- Если в текстовой строке есть часть текста, заключенная в круглые скобки, теперь вам нужно извлечь все текстовые строки между скобками, как показано на следующем снимке экрана. Как можно быстро и легко решить эту задачу в Excel?
– -> Лучшие инструменты для повышения производительности в офисе
В столбце Эксель (Excel) записано ФИО человека в формате «Фамилия Имя Отчество».
Например, Иванов Андрей Иванович.
Требуется с помощью формул сделать, чтобы:
1) Фамилия, имя и отчество располагались в отдельных ячейках.
2) Выделить инициалы — преобразовать «Иванов Андрей Иванович» в «Иванов А. И.».
Как в (Эксель) Excel выполнить подобную задачу?
Как в (Эксель) Excel разделить ФИО на отдельные ячейки и на инициалы?
Пусть в ячейке A1 содержится ФИО в виде «Фамилия Имя Отчество».
Тогда, для получения результата :
- В ячейку B1 пишем формулу : =ПСТР(A1;1;НАЙТИ(» «;A1;1))
- В ячейку С1 пишем формулу : =ПСТР(A1;НАЙТИ(» «;A1;1)+1;НАЙТИ(» «;A1;НАЙТИ(» «;A1;1)+1)-НАЙТИ(» «;A1;1))
- В ячейку D1 пишем формулу : =ПСТР(A1;НАЙТИ(» «;A1;НАЙТИ(» «;A1;1)+1)+1;ДЛСТР(A1)-НАЙТИ(» «;A1;НАЙТИ(» «;A1;1)+1)+1)
- В ячейку E1 пишем формулу : =B1&» «&ЛЕВСИМВ(C1;1)&». «&ЛЕВСИМВ(D1;1)&».»
Ставите курсор на ячейку с фамилией, именем и отчеством. Идём на вкладку данные, нажимаем кнопку «текст по столбцам», формат данных ставим с разделителями, нажимаем кнопку далее, ставим галочку напротив символа-разделителя в виде пробела, нажимаем кнопку «далее». Формат данных столбца ставим «текстовой». Выбираем ячейку куда хотим переместить наши ФИО (ячейка должна быть абсолютной). Нажимаем кнопку «готово». При переносе только инициалов пишем формулу =(ячейка с фамилией)&» «&ЛЕВСИМВ((ячейка с именем);1)&». «&ЛЕВСИМВ((ячейка с отчеством);1)&».» Вместо (ячейка) ставится относительный адрес ячейки.
Разделяем пробелами Фамилию, Имя и Отчество
Пусть имеется текстовая строка с именем, записанным слитно: ПетровИванИванович. Причем, фамилия, имя и отчество написаны с заглавных букв. Используем это для разделения частей имени пробелами (см. файл примера ).
C помощью функции КОДСИМВ() определим является ли буква заглавной (прописной) , если да, то выведем перед ней пробел.
Сначала создадим вспомогательную таблицу для вывода позиций ПРОПИСНЫХ символов (в столбцах F:ВВ , всего 50 символов).
Формула =ЕСЛИ(ЕСЛИОШИБКА(КОДСИМВ(ПСТР($A5;F$4;1));999)<224;F$4;0) будет возвращать позицию ПРОПИСНЫХ символов, начиная со второго.
В столбцах C:E с помощью формул
выведем фамилию, имя и отчество.
И, наконец, в столбце В получим требуемый результат с помощью формулы =C5&» «&D5&» «&E5
Разделить ФИО в Excel на ячейки
Часто при составлении договора, счета, коммерческого предложения или другого документа, мы работаем с фамилией именем и отчеством клиента. Бывает необходимо разделить ФИО и получить только имя, что обратиться к клиенту в тексте документа, бывает нужно сделать графу с подписантом, оставив фамилию и инициалы.
Конечно можно написать формулу, которая извлечет нужные данные из ФИО, но даже опытному человеку потребуется 5-7 минут на это. А что если заранее не знаешь расположение фамилии в тексте? Гораздо проще и удобнее пользоваться готовыми функциями из надстройки VBA-Excel.
Найти фамилию в ФИО
После установки надстройки будет доступна новая функция =ФАМИЛИЯ(ФИО), где
- ФИО — текст или ссылка на ячейку, которая содержит ФИО полностью
Задача данной функции — извлечь из ячейки только фамилию. Причем ей не важен порядок. Фамилия может идти как в начале, так в конце или середине текста.
Найти имя в ФИО
Аналогичная функция для имени =ИМЯ(ФИО). Она извлекает имя из текста с ФИО, также не важно где расположенное.
Найти отчество в ФИО
Последняя функция из этой серии — функция =ОТЧЕСТВО(ФИО) для извлечения отчества.
Примеры работы с ФИО в Excel
Вывести фамилию и инициалы
Одной из распространенных задач при составлении документов — это преобразование текста вида Фамилия Имя Отчество к тексту Фамилия И. О. Решить ее можно используя следующую формулу:
Она несколько длинная, но поверьте намного короче и понятнее, если бы мы не использовали функции из надстройки.
Также плюс в том, что нам не важно расположение фамилии в тексте, функция определит все автоматически.