Загальна формула
=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 бере на себе функцію, щоб з’ясувати, де в назві знаходиться "*":
FIND("*", "Susan Ann*Chang")
Результат - 10 (* знаходиться на 10-й позиції), що віднімається від загальної довжини імені:
LEN(B4)-10
Оскільки назва складається з 15 символів, ми маємо:
15-10 = 5
Цифра 5 використовується ПРАВО так:
=RIGHT(B4,5)
Що призводить до "Чанг"
Як ви бачите, для того, щоб обчислити цей простий 5, потрібно виконати багато роботи!
Обробка непослідовних пробілів
Зайві пробіли спричинять проблеми з цією формулою. Одним із рішень є використання функції TRIM спочатку для очищення речей, а потім використання формули аналізу.
