Note: The other languages of the website are Google-translated. Back to English
English English

Извлечь первое отчество и фамилию из полного имени в Excel

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


Извлечь или получить имена из полных имен в Excel

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

=LEFT(name,FIND(" ",name)-1)
  • name: Полное имя или ссылка на ячейку, из которой вы хотите извлечь в первую очередь.

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

=LEFT(A2,SEARCH(" ",A2)-1)

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


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

ПОИСК (""; A2) -1: Эта функция ПОИСК используется для получения позиции первого символа пробела, вычитание 1 означает, что количество символов, которое вы хотите, на единицу меньше, чем позиция пробела. Получим результат: 5.

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


Извлечь или получить фамилии из полных имен в Excel

Чтобы извлечь фамилии из полных имен, комбинация функций ВПРАВО, LEN, НАЙТИ, LEN и ПОДСТАВИТЬ может оказать вам услугу. Общий синтаксис:

=RIGHT(name,LEN(name)-FIND("*",SUBSTITUTE(name," ","*",LEN(name)-LEN(SUBSTITUTE(name," ","")))))
  • name: Полное имя или ссылка на ячейку, из которой вы хотите извлечь фамилию.

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

=RIGHT(A2,LEN(A2)-FIND("*",SUBSTITUTE(A2," ","*",LEN(A2)-LEN(SUBSTITUTE(A2," ","")))))

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


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

1. LEN (A2) -LEN (ЗАМЕНА (A2, "", "")): Эта часть используется для получения количества пробелов в ячейке A2.

  • LEN (A2): Эта функция LEN возвращает общее количество символов в ячейке A2. Он вернет: 14.
  • ЗАМЕНА (A2, "", ""): Эта функция ЗАМЕНА используется для замены всех пробелов ничем. И вы получите такой результат: «Джерри Т. Стоун».
  • LEN (ПОДСТАВИТЬ (A2; ""; ""): Получите общую длину полного имени в ячейке A2 без пробелов.
  • LEN (A2) -LEN (ПОДСТАВИТЬ (A2; "-", "")): Вычтите длину полного имени без пробелов из общей длины полного имени, чтобы получить номер пробела, и получится 2.

2. ПОДСТАВИТЬ (A2, «», «*», LEN (A2) -LEN (ПОДСТАВИТЬ (A2, «», «»))) = ПОДСТАВИТЬ (A2, «», «*», 2): Эта функция SUBSTITUTE используется для замены последнего вхождения пробела, возвращаемого формулой первой части, символом *. И вы получите такой результат: «Джерри Т. * Стоун».

3. НАЙТИ ("*", ПОДСТАВИТЬ (A2, "", "*", LEN (A2) -LEN (SUBSTITUTE (A2, "", "")))) = НАЙТИ ("*", "Джерри Т. *Камень" ): Функция НАЙТИ вернет позицию символа * в текстовой строке, которую возвращает функция ПОДСТАВИТЬ, и вы получите число 9.

4. LEN (A2) -FIND ("*", ПОДСТАВИТЬ (A2, "", "*", LEN (A2) -LEN (ПОДСТАВИТЬ (A2, "", "")))) = LEN (A2) - 9: Эта часть формулы получит, сколько символов стоит после последнего пробела. Это получит число 5. Это будет распознано как аргумент num_chars в функции RIGHT.

5. ВПРАВО (A2; LEN (A2) -НАЙТИ ("*", ПОДСТАВИТЬ (A2, "", "*", LEN (A2) -LEN (ПОДСТАВИТЬ (A2, "", ""))))) = ВПРАВО (A2; 5): Наконец, функция RIGHT используется для извлечения 5 символов, которые возвращаются формулой на шаге 4 из правой части текстовой строки в ячейке A2.


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

=RIGHT(A2,LEN(A2)-SEARCH(" ",A2,1))


Извлечь или получить отчество из полных имен в Excel

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

=MID(name, SEARCH(" ", name) + 1, SEARCH(" ", name, SEARCH(" ", name)+1) - SEARCH(" ", name)-1)
  • name: Полное имя или ссылка на ячейку, из которой вы хотите извлечь отчество.

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

=MID(A2, SEARCH(" ", A2) + 1, SEARCH(" ", A2, SEARCH(" ", A2)+1) - SEARCH(" ", A2)-1)

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


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

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

1. ПОИСК ("", A2) + 1: Эта функция ПОИСК получит позицию первого пробела, добавление 1 означает начало извлечения со следующего символа. Это получит номер 7. Эта часть распознается как аргумент start_num в функции MID.

2. ПОИСК ("", A2, ПОИСК ("", A2) +1) - ПОИСК ("", A2) -1: Эта часть распознается как аргумент num_chars в функции MID.

  • ПОИСК (""; A2; ПОИСК (""; A2) +1): Эта часть формулы используется для получения позиции второго пробела, ей будет присвоено число 9.
  • ПОИСК (""; A2): Эта функция ПОИСК получит позицию первого пробела в ячейке A2. Он получит номер 6.
  • ПОИСК (""; A2; ПОИСК (""; A2) +1) - ПОИСК (""; 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 , XNUMX): Наконец, функция MID извлечет 2 символа в середине ячейки A2, которая начинается с седьмого символа.


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

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

=IFERROR(MID(A2, SEARCH(" ", A2) + 1, SEARCH(" ", A2, SEARCH(" ", A2)+1) - SEARCH(" ", A2)-1),"")


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

Если вам неприятны приведенные выше формулы, я порекомендую простой инструмент - Kutools for Excel, С его Разделить имена функция, вы можете извлечь все имя, среднее имя и фамилию в отдельные ячейки сразу.  Нажмите, чтобы загрузить Kutools for Excel для бесплатной пробной версии!


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

  • LEFT:
  • Функция LEFT извлекает заданное количество символов из левой части предоставленной строки.
  • RIGHT:
  • Функция ВПРАВО используется для извлечения определенного количества символов из правой части текстовой строки.
  • MID:
  • Функция MID используется для поиска и возврата определенного количества символов из середины данной текстовой строки.
  • LEN:
  • Функция LEN возвращает количество символов в текстовой строке.
  • FIND:
  • Функция НАЙТИ используется для поиска строки в другой строке и возвращает начальную позицию строки внутри другой.
  • SEARCH:
  • Функция ПОИСК может помочь вам найти позицию определенного символа или подстроки в заданной текстовой строке.
  • SUBSTITUTE:
  • Функция ЗАМЕНА в Excel заменяет текст или символы в текстовой строке другим текстом или символами.

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

  • Извлечь имя и фамилию из полного имени
  • Предположим, у вас есть список полных имен, теперь вы хотите извлечь первое имя и фамилию или имя и первый символ фамилии из полного имени, как показано ниже. Как бы вы могли справиться с этими задачами в книге Excel?
  • Перевернуть или перевернуть имя и фамилию в списке Excel
  • Если у вас есть список полных имен, которые отформатированы как фамилия и имя, теперь вы хотите перевернуть фамилию и имя на имя и фамилию, как показано ниже. В этой статье я представлю некоторые формулы для работы с этой работой в Excel.
  • Извлечь несколько строк из ячейки
  • Если у вас есть список текстовых строк, разделенных разрывами строк (это происходит при нажатии клавиш Alt + Enter при вводе текста), и теперь вы хотите извлечь эти строки текста в несколько ячеек, как показано ниже. Как бы вы могли решить это с помощью формулы в Excel?
  • Извлечь N-е слово из текстовой строки в Excel
  • Если у вас есть список текстовых строк или предложений, теперь вы хотите извлечь конкретное n-е слово из списка, как показано ниже. В этой статье я расскажу о некоторых методах решения этой задачи в Excel.

Лучшие инструменты для работы в офисе

Kutools for Excel - поможет вам выделиться из толпы

Хотите быстро и безупречно выполнять свою повседневную работу? Kutools for Excel предлагает мощные расширенные функции 300 (объединение книг, сумма по цвету, разделение содержимого ячеек, дата преобразования и т. Д.) И экономия 80% времени для вас.

  • Рассчитан на 1500 сценариев работы, помогает решить 80% задач Excel.
  • Уменьшите количество нажатий на клавиатуру и мышь каждый день, избавьтесь от усталости глаз и рук.
  • Станьте экспертом по Excel за 3 минуты. Больше не нужно запоминать какие-либо болезненные формулы и коды VBA.
  • 30-дневная неограниченная бесплатная пробная версия. 60-дневная гарантия возврата денег. Бесплатное обновление и поддержка 2 года.
Лента Excel (с установленным Kutools for Excel)

Вкладка Office - включение чтения и редактирования с вкладками в Microsoft Office (включая Excel)

  • Одна секунда для переключения между десятками открытых документов!
  • Уменьшите количество щелчков мышью на сотни каждый день, попрощайтесь с рукой мыши.
  • Повышает вашу продуктивность на 50% при просмотре и редактировании нескольких документов.
  • Добавляет эффективные вкладки в Office (включая Excel), точно так же, как Chrome, Firefox и новый Internet Explorer.
Снимок экрана Excel (с установленной вкладкой Office)
Сортировать комментарии по
Комментарии (3)
Оценок пока нет. Оцените первым!
Этот комментарий был сведен к минимуму модератором на сайте
ты чемпион! очень полезно!
Этот комментарий был сведен к минимуму модератором на сайте
Eu gostaria де тер ума lista де Todos Nomes за исключением о ultimo. Portanto, uma lista de nome completo sem o sobrenome.
Этот комментарий был сведен к минимуму модератором на сайте
Eu gostaria де экстраир о Primeiro е делать Meio Nomes juntos.
Здесь еще нет комментариев
Оставляйте свои комментарии
Публикация как гость
×
Оценить этот пост:
0   Характеристики
Предлагаемые места