☰

Объединение первых двух букв имени и первых двух букв фамилии

tag-icon • tag-icon microsoft-excel worksheet-function microsoft-excel-2013
Объединение первых двух букв имени и первых двух букв фамилии

У меня есть таблица имен пользователей.

Имя и фамилия находятся в одной ячейке столбца A.

Существует ли формула, которая объединит первые две буквы имени (первое слово) и первые две буквы фамилии (второе слово)?

Например John Doe, должно стать JoDo.

Я пытался

=LEFT(A1)&MID(A1,IFERROR(FIND(" ",A1),LEN(A1))+1,IFERROR(FIND(" ",SUBSTITUTE(A1," ","",1)),LEN(A1))-IFERROR(FIND(" ",A1),LEN(A1)))

но это дает мне JoDoeрезультат.

решение1

Да; если у каждого человека есть только имя и фамилия, и они всегда разделены пробелом, вы можете использовать следующее:

=LEFT(A1,2)&MID(A1,SEARCH(" ",A1)+1,2)

Я мог основывать свой ответ только на этих предположениях, поскольку это все, что вы предоставили.

Или, если вы хотите, чтобы пространство все равно было включено:

=LEFT(A1,2)&" "&MID(A1,SEARCH(" ",A1)+1,2)

решение2

И в завершение вот решение, которое вернет первые два символа имени и первые два символа фамилии,нотакже учитывает вторые имена.

=LEFT(A1,2)&LEFT(MID(A1,FIND("~~~~~",SUBSTITUTE(A1," ","~~~~~",LEN(A1)-LEN(SUBSTITUTE(A1," ",""))))+1,LEN(A1)),2)

введите описание изображения здесь

Благодаря@Kyle за основную часть формулы

решение3

Это еще один способ...

Скриншот рабочего листа

  • Имя
  • Б -=CONCATENATE(LEFT(A1,2),LEFT(RIGHT(A1,(LEN(A1)-FIND(" ",A1))),2))

решение4

На основеэтот ответ, вот элегантное решение, которое работает с любым количеством отчеств:

=LEFT(A1,2)&LEFT(TRIM(RIGHT(SUBSTITUTE(A1," ",REPT(" ",LEN(A1))),LEN(A1))),2)

Объяснение:

SUBSTITUTE(A1, " ", REPT(" ",LEN(A1)))заменяет пробелы между словами на пробелы, количество которых равно длине всей строки. Использование длины строки вместо произвольно большого числа гарантирует, что формула будет работать для строки любой длины, и означает, что она делает это эффективно.

RIGHT(space_expanded_string, LEN(A1))извлекает самое правое слово, к которому добавлено несколько пробелов. *

TRIM(space_prepended_rightmost_word)извлекает самое правое слово.

LEFT(rightmost_word, 2)извлекает первые два символа самого правого слова (фамилии).


*Предостережение: если имя пользователя может содержать конечные пробелы, вам необходимо заменить первый аргумент SUBSTITUTE(), т. е. A1, на TRIM(A1). Начальные пробелы и несколько последовательных пробелов между словами обрабатываются правильно только с A1.

Исправление вашей попытки

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

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

Кроме того, если все имена пользователей состоят только из имени или имени и фамилии, то формула излишне усложняется и ее можно упростить.


Чтобы увидеть, как работает формула, и исправить ее, проще ее упорядочить, например так:

=
LEFT(A1,2) &
MID(
  A1,
  IFERROR(FIND(" ",A1), LEN(A1)) + 1,
  IFERROR(
    FIND(" ", SUBSTITUTE(A1," ","",1)),
    LEN(A1)
  )
  - IFERROR(FIND(" ",A1), LEN(A1))
)


Чтобы понять, как это работает, сначала посмотрим, что происходит, когда A1не содержит пробелов (т.е. содержит только одно имя). Все функции IFERROR()оценивают свои вторые аргументы, поскольку FIND()возвращает #VALUE!ошибку, если искомая строка не найдена в целевой строке:

=
LEFT(A1,2) &
MID(
  A1,
  LEN(A1) + 1,
  LEN(A1)
  -LEN(A1)
)

Третий аргумент MID()вычисляется как ноль, поэтому функция ""и результат формулы представляют собой первые два символа одного имени.


Теперь посмотрим, когда есть ровно два имени (т.е. ровно один пробел). Первая и третья IFERROR()функции вычисляются по своим первым аргументам, но вторая вычисляется по своему второму аргументу, поскольку FIND(" ", SUBSTITUTE(A1," ","",1))пытается найти другой пробел после удаления первого и единственного:

=
LEFT(A1,2) &
MID(
  A1,
  FIND(" ",A1) + 1,
  LEN(A1)
  - FIND(" ",A1)
)

Очевидно, MID()возвращает второе слово (т.е. фамилию) целиком, а результат формулы — первые два символа имени, за которыми следуетвсесимволы фамилии.


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

=
LEFT(A1,2) &
MID(
  A1,
  FIND(" ",A1) + 1,
  FIND(" ", SUBSTITUTE(A1," ","",1))
  - FIND(" ",A1)
)

Это немного менее понятно, чем в предыдущем случае, но MID()возвращает точно всю информацию.второйслово (т.е. первое отчество). Таким образом, результат формулы — это первые два символа имени, за которыми следуют все символы первого отчества.


Очевидно, что исправление заключается в использовании LEFT()для получения первых двух символов вывода MID():

=
LEFT(A1,2) &
LEFT(
  MID(
    A1,
    IFERROR(FIND(" ",A1), LEN(A1)) + 1,
    IFERROR(
      FIND(" ", SUBSTITUTE(A1," ","",1)),
      LEN(A1)
    )
    - IFERROR(FIND(" ",A1), LEN(A1))
  ),
  2
)


Упрощение, о котором я упоминал выше, заключается в замене LEFT(MID(…,…,…), 2)на MID(…,…,2):

=
LEFT(A1,2) &
MID(
  A1,
  IFERROR(FIND(" ",A1), LEN(A1)) + 1,
  2
)

или в одну строку:

=LEFT(A1,2)&MID(A1,IFERROR(FIND(" ",A1),LEN(A1))+1,2)

Это по сутиРешение PeterHизменено для работы с отдельными именами (в этом случае результатом будут только первые два символа имени).


Примечание:Упрощенные формулы действительно работают, если их ввести.

Связанный контент