У меня есть таблица с именами пользователей.
Имя и фамилия находятся в одной ячейке столбца 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 в результате.
Да; при условии, что у каждого человека есть только имя и фамилия, и это всегда разделяется пробелом, вы можете использовать ниже:
=LEFT(A1,2)&MID(A1,SEARCH(" ",A1)+1,2)
Я мог бы основать этот ответ только на этих предположениях, так как это все, что вы предоставили.
Или, если вы хотите, чтобы место все еще было включено:
=LEFT(A1,2)&" "&MID(A1,SEARCH(" ",A1)+1,2)
И в заключение, вот решение, которое возвращает первые два символа имени и первые два символа фамилии, но также учитывает отчество.
=LEFT(A1,2)&LEFT(MID(A1,FIND("~~~~~",SUBSTITUTE(A1," ","~~~~~",LEN(A1)-LEN(SUBSTITUTE(A1," ",""))))+1,LEN(A1)),2)
Спасибо @Kyle за основную часть формулы
Это еще один способ ...
=CONCATENATE(LEFT(A1,2),LEFT(RIGHT(A1,(LEN(A1)-FIND(" ",A1))),2))Прежде всего, я хотел бы сказать, что ответ PeterH является самым простым и легким для понимания. (Хотя я предпочитаю использовать FIND() вместо SEARCH() - ввод менее двух символов помогает избежать RSI ;-))
Альтернативный ответ, который не использует ни MID() , ни LEFT() ни RIGHT() , но вместо этого использует REPLACE() для удаления ненужных частей имени, выглядит следующим образом:
=REPLACE(REPLACE(A1,FIND(" ",A1)+3,LEN(A1),""),3,FIND(" ",A1)-2,"")
Объяснение:
Внутренний REPLACE(A1, FIND(" ",A1)+3, LEN(A1), "") удаляет символы из третьего символа фамилии и далее, тогда как внешний REPLACE(inner_replace, 3, FIND(" ",A1)-2, "") удаляет символы из третьего символа имени до и включая пробел.
Приложение 1:
Приведенная выше формула также может быть адаптирована для учета одного отчества:
=REPLACE(REPLACE(A1,IFERROR(FIND(" ",A1,FIND(" ",A1)+1),FIND(" ",A1))+3,LEN(A1),""),3,IFERROR(FIND(" ",A1,FIND(" ",A1)+1),FIND(" ",A1))-2,"")
заменив FIND(" ",A1) на IFERROR(FIND(" ",A1,FIND(" ",A1)+1), FIND(" ",A1)) .
FIND(" ", A1, FIND(" ",A1)+1) находит второй пробел (запуская поиск пробела после первого пробела) или ошибки в противном случае.
IFERROR(find_second_space, FIND(" ",A1)) находит первый пробел, если второго пробела нет.
Эта (длинная) версия допускает любое количество отчеств:
=REPLACE(REPLACE(A1,FIND("§",SUBSTITUTE(A1," ","§",LEN(A1)-LEN(SUBSTITUTE(A1," ",""))))+3,LEN(A1),""),3,FIND("§",SUBSTITUTE(A1," ","§",LEN(A1)-LEN(SUBSTITUTE(A1," ",""))))-2,"")
В этом случае FIND(" ",A1) заменяется на FIND("§", SUBSTITUTE(A1," ","§",LEN(A1)-LEN(SUBSTITUTE(A1," ","")))) ,
LEN(A1)-LEN(SUBSTITUTE(A1," ","")) подсчитывает количество пробелов.
SUBSTITUTE(A1, " ", "§", count_of_spaces) заменяет последний пробел на § .
FIND("§", last_space_replaced_string) находит первый § который совпадает с поиском последнего пробела.
(Конечно, § можно заменить любым символом, который гарантированно не существует в строке полного имени. Более общей, более безопасной альтернативой было бы использование CHAR(1) . )
Обратите внимание, что, конечно, ответ BruceWayne - самое простое и легкое для понимания решение, которое допускает любое количество отчеств . Ну, это было. Пока я не отправил другой мой ответ, то есть ;-)
Приложение 2:
Все решения могут быть адаптированы для обработки только одного имени (если требуется результат из четырех символов), заключая их в функцию IFERROR() следующим образом:
=IFERROR(solution, alternate_formula)
Обратите внимание, что приведенное выше является формулой общего случая, и может оказаться возможным сделать более эффективную модификацию конкретного решения. Например, если в случае одного имени требуется объединить первые две буквы с последними двумя буквами, ответ PeterH может быть более эффективно адаптирован следующим образом:
=LEFT(A1,2)&MID(A1,IFERROR(SEARCH(" ",A1)+1,LEN(A1)-1),2)
Чтобы учесть случай, когда имя или буква состоят из одной буквы (предполагается, что в качестве второго символа пробел или точка недопустимы), для любого решения можно использовать следующее:
=SUBSTITUTE(SUBSTITUTE(solution, " ", single_char), ".", single_char))
Обратите внимание, что один символ может быть жестко запрограммирован или рассчитан по имени. (Или используйте "" чтобы удалить пробел или точку.)
Наконец, если вам действительно нужно учитывать случай, когда полное имя состоит только из одного символа (!), Просто оберните формулу только для одного имени другим IFERROR() . (Предполагая, конечно, что альтернативная формула не учитывает этот особый случай.)
Приложение 3:
Наконец, наконец (нет, действительно * ;-)) для обслуживания нескольких последовательных и / или начальных / конечных пробелов, используйте TRIM(A1) вместо A1 .
* Я оставлю дело для одной буквы фамилии, как г-н Т., в качестве упражнения для читателя.
Подсказка:
=solution &IF(MID(A1,LEN(A1)-1,1)=" ", single_char, "")
Основываясь на этом ответе, вот элегантное решение, которое работает с любым количеством отчеств:
=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, измененное для работы с отдельными именами (в этом случае результатом являются только первые два символа имени).
Примечание: предварительно введенные формулы действительно работают, если введены.