Excel трюк: Достаем фамилию из ФИО

Excel трюк: Достаем фамилию из ФИО

У меня было сотня случаев, когда из списка сотрудников необходимо было достать фамилию или имя. При этом список насчитывал минимум пару сотен человек и делать это в ручную было очень грустно. Хорошо, что в Excel есть все необходимые инструменты, позволяющие оптимизировать данный процесс и решить поставленную задачу за несколько минут. Текстовые формулы Excel, позволяют решать подобные задачи. Сейчас я вам все подробно объясню на примере нескольких актеров любезно предоставленных первой страницей Google.

Пример (Текстовые формулы Excel)

Текстовые формулы Excel, которые нам понадобятся

Для решения поставленной задачи нам понадобится собственно Excel и следующие стандартные текстовые функции:

  1. ПОИСК - позволяет найти текст или значение в тексте
  2. ДЛСТР - подсчитывает количество символов в ячейке с текстом
  3. ПРАВСИМВ - выдает заданной количество символов с конца текста

Наш особенно сообразительный читатель возможно уже догадался как решить нашу задачу, но я всё же объясню для всех по шагам как это нужно было бы мне 🙂

Для решения нашей задачи нам понадобиться:

  1. Посчитать длину текста в ФИО с помощью функции ДЛСТР
  2. Найти номер знака с которого начинается фамилия с помощью функции ПОИСК
  3. Посчитать длинну фамилии как разницу между результатами пунктов 1 и 2
  4. Вывести фамилию с помощью функции ПРАВСИМВ и длинны фамилии из третьего пункта. 

Звучит как план к действию. Приступим!

Фукнция ДЛСТР

Данная текстовая функция Excel - одна из простейших. Ей просто нужно указать ячейку и она выдает количество символов в ней. Вы и сами можете убедиться на примере:

 

Функция ДЛСТР (Текстовые формулы Excel)

Функция ПОИСК

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

Важно отметить, что пробел или искомый текст в данной функции записывается в ковычках. В частности это будет примерно так =ПОИСК(" "; 'ячейка с текстом').

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

Функция ПОИСК (Текстовые формулы Excel)

Функция ПРАВСИМВ

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

Функция ПРАВСИМВ (Текстовые формулы Excel)

Мы с вами разобрали как работают лишь несколько из  текстовых функций Excel. Подписывайтесь и следите за публикациями. Есть ещё много чего рассказать.

4 комментария

  • Ruslan

    А проще было бы использовать функцию text to column и в качестве разделителя использовать пробел

    • Согласен, Руслан! Этот способ также решит данную задачу. Однако целью Заметки было показать использование текстовых функций. К тому же предложенный метод работает в автоматическом режиме, то есть при обновлении данных не потребует ручной обработки. Повторюсь один из способов

  • Руслан

    добрый день.
    у меня есть 1000 Фамилий в одном столбце, в другом листе ФИО.
    нужно заменить Фамилию на ФИО из другого листа.
    Это возможно?

    • Руслан, конечно можно. Для этого вы как описывается в статье выделяете текст, который нужно сохранить, а потом объединяете получившийся текст с новыми фамилиями используя знак &. Формула будет выглядеть примерно так ‘=’Фамилия’&» «&’Имя Отчетство’. Пробел в кавычках нужнен будет лоя того, чтобы между соединяемыми кусками текста появился разделитель.

Post a Reply

Этот сайт использует Akismet для борьбы со спамом. Узнайте как обрабатываются ваши данные комментариев.

s2Member®