Извлечение имени, отчества и Фамилия из Полное имя в Excel

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

• Извлечение или получение Имя из Полное имя в Excel
• Извлечение или получение Фамилия из Полное имя в Excel
• Извлечение или получение Отчество из Полное имя в Excel
• Извлечение или получение имени, отчества и Фамилия из Полное имя с помощью удивительной функции

Извлечение или получение Имя из Полное имя в Excel

Чтобы извлечь имя из полного имени, воспользуйтесь функциями LEFT и SEARCH. Общий синтаксис:

• nameПолное имя или ссылка на ячейку, из которой нужно извлечь первое значение.

Скопируйте или введите следующую формулу в пустую ячейку, куда вы хотите поместить результат:

Затем перетащите маркер заполнения вниз до нужных ячеек, и все Имя будут извлечены, как показано на снимке экрана ниже:

Пояснение формулы:

SEARCH(« »,A2)-1: Эта функция SEARCH определяет позицию первого пробела, а вычитание единицы означает, что нужное количество символов на один меньше этой позиции. Результат: 5.

LEFT(A2,SEARCH(« »,A2)-1)= LEFT(«Jerry T. Stone»,5): Эта функция LEFTизвлекает из полного имени «Jerry T. Stone» первые 5 символов, начиная с левой стороны, как возвращает функция SEARCH.

Извлечение или получение Фамилия из Полное имя в Excel

Чтобы извлечь фамилию из полного имени, используйте комбинацию функций RIGHT, LEN, FIND и SUBSTITUTE. Общий синтаксис:

• name: Полное имя или ссылка на ячейку, из которой необходимо извлечь Фамилия.

Скопируйте или введите приведённую ниже формулу в пустую ячейку для получения Фамилия:

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

Пояснение формулы:

1. LEN(A2)-LEN(SUBSTITUTE(A2,« »,«»)): Эта часть формулы определяет, сколько пробелов содержится в ячейке A2.

• LEN(A2): Эта функция LEN возвращает общее количество символов в ячейке A2. Результат: 14.
• SUBSTITUTE(A2,« »,«»):Эта функция SUBSTITUTE заменяет все пробелы на пустую строку, и в результате вы получите: «JerryT.Stone».
• LEN(SUBSTITUTE(A2,« »,«»)): Получение общей длины полного имени в ячейке A2 без пробелов.
• LEN(A2)-LEN(SUBSTITUTE(A2,"-",«»)):Вычитание длины полного имени без дефисов из общей длины полного имени позволяет определить количество дефисов; результат: 2.

2. SUBSTITUTE(A2,« »,«*»,LEN(A2)-LEN(SUBSTITUTE(A2,« »,«»)))= SUBSTITUTE(A2,« »,«*»,2): Эта функция SUBSTITUTE заменяет последнее вхождение пробела — определённое первой частью формулы — на символ *. В результате вы получите: «Jerry T.*Stone».

3. FIND(«*»,SUBSTITUTE(A2,« »,«*»,LEN(A2)-LEN(SUBSTITUTE(A2,« »,«»))))= FIND(«*»,«Jerry T.*Stone» ):Функция FIND возвращает позицию символа «*» в текстовой строке, полученной с помощью функции SUBSTITUTE; результат — 9.

4. LEN(A2)-FIND(«*»,SUBSTITUTE(A2,« »,«*»,LEN(A2)-LEN(SUBSTITUTE(A2,« »,«»))))= LEN(A2)-9:Эта часть формулы определяет количество символов после последнего пробела. Результат — 5. Это значение используется в качестве аргумента num_chars функции RIGHT.

5. RIGHT(A2,LEN(A2)-FIND(«*»,SUBSTITUTE(A2,« »,«*»,LEN(A2)-LEN(SUBSTITUTE(A2,« »,«»)))))=RIGHT(A2, 5):Наконец, функция RIGHTизвлекает последние 5 символов с правого конца текстовой строки в ячейке A2 — именно столько указывает формула на шаге 4.

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

Извлечение или получение Отчество из Полное имя в Excel

Если вам нужно извлечь Отчество из Полное имя, используйте формулу, созданную с помощью функций MID и SEARCH. Общий синтаксис:

• name: Полное имя или ссылка на ячейку, из которой необходимо извлечь Отчество.

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

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

Пояснение формулы:

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

1. SEARCH(« », A2) + 1: Эта функция ПОИСК определяет позицию первого пробела, а прибавление единицы указывает, что извлечение начнётся со следующего символа. В результате получится число 7 — именно оно используется в качестве аргумента start_num функции ПСТР.

2. SEARCH(« », A2, SEARCH(« », A2)+1) - SEARCH(« », A2)-1: Эта часть интерпретируется как аргумент num_chars функции ПСТР.

• SEARCH(« », A2, SEARCH(« », A2)+1): Эта часть формулы определяет позицию второго пробела. Результат: 9.
• SEARCH(« », A2): Эта функция SEARCH определяет позицию первого пробела в ячейке A2. Результат — 6.
• SEARCH(« », A2, SEARCH(« », A2)+1) - SEARCH(« », A2)-1=9-6-1: Вычитание позиции первого пробела из позиции второго пробела и последующее вычитание 1 позволяет убрать начальный пробел. Итоговый результат: 2.

3. MID(A2, SEARCH(« », A2) + 1, SEARCH(« », A2, SEARCH(« », A2)+1) - SEARCH(« », A2)-1)=MID(A2,7,2): В результате функция ПСТР извлечёт два символа из середины ячейки A2, начиная с седьмого символа.

Примечание: Если в Полное имя присутствуют только имя и Фамилия, приведённая выше формула вернёт ошибку, как показано на следующем снимке экрана:

Чтобы устранить эту проблему, следует заключить указанную выше формулу в функцию ЕСЛИОШИБКА. Используйте следующую формулу:

Извлекайте или получайте имя, отчество и Фамилия из Полное имя с помощью удивительной функции

Используемые связанные функции:

Другие статьи: