Общая формула
=RIGHT(name,LEN(name)-FIND("*",SUBSTITUTE(name," ","*",LEN(name)-LEN(SUBSTITUTE(name," ","")))))
Резюме
Если вам нужно извлечь фамилию из полного имени, вы можете сделать это с помощью этой довольно сложной формулы, которая использует несколько функций. В общей форме формулы (см. Выше) имя - это полное имя с пробелом, отделяющим имя от других частей имени.
В примере активная ячейка содержит эту формулу:
=RIGHT(B4,LEN(B4)-FIND("*",SUBSTITUTE(B4," ","*",LEN(B4)-LEN(SUBSTITUTE(B4," ","")))))
Объяснение
По сути, эта формула использует функцию ВПРАВО для извлечения символов, начиная с правого. Другие функции, составляющие сложную часть этой формулы, делают только одно: они вычисляют, сколько символов необходимо извлечь.
На высоком уровне формула заменяет последний пробел в имени звездочкой «*», а затем использует FIND для определения положения звездочки в имени. Позиция используется для определения количества символов, которые нужно извлечь с помощью ВПРАВО.
Как функция заменяет только последний пробел? Это умная часть.
Пристегнитесь, объяснение становится немного техническим.
Ключом к этой формуле является этот бит:
SUBSTITUTE(B4," ","*",LEN(B4)-LEN(SUBSTITUTE(B4," ","")))
Что делает фактическую замену последнего пробела на "*".
SUBSTITUTE имеет четвертый (необязательный) аргумент, который указывает, какой «экземпляр» найденного текста следует заменить. Если для этого аргумента ничего не указано, заменяются все экземпляры. Однако, если, скажем, предоставляется номер 2, заменяется только второй экземпляр. В приведенном выше фрагменте экземпляр вычисляется с использованием второй ЗАМЕНА:
LEN(B4)-LEN(SUBSTITUTE(B4," ",""))
Здесь длина имени без пробелов вычитается из фактической длины имени. Если в имени только один пробел, получается 1. Если есть два пробела, получается 2 и т. Д.
В примере имени в B4 в имени есть два пробела, поэтому мы получаем:
15–13 = 2
И два используется, как в номере экземпляра:
SUBSTITUTE(B4," ","*",2)
который заменяет второй пробел на "*". Тогда имя будет выглядеть так:
"Сьюзан Энн * Чанг"
Затем функция НАЙТИ берет на себя задачу выяснить, где находится "*" в имени:
FIND("*", "Susan Ann*Chang")
Результатом будет 10 (* находится на 10-й позиции), которое вычитается из общей длины имени:
LEN(B4)-10
Поскольку имя состоит из 15 символов, мы имеем:
15-10 = 5
Число 5 используется ВПРАВО так:
=RIGHT(B4,5)
В результате получается "Чанг"
Как видите, чтобы вычислить эти простые 5, нужно проделать большую работу!
Обработка несовместимых пространств
Лишние пробелы вызовут проблемы с этой формулой. Одно из решений - сначала использовать функцию TRIM для очистки, а затем использовать формулу синтаксического анализа.
