Общая формула
=RIGHT(domain,LEN(domain)-FIND("*",SUBSTITUTE(domain,".","*",LEN(domain)-LEN(SUBSTITUTE(domain,".","")))))
Резюме
Чтобы извлечь домен верхнего уровня (TLD) (например, «com», «net», «org») из имени домена или адреса электронной почты, вы можете использовать формулу, основанную на нескольких текстовых функциях: MID, RIGHT, FIND, LEN, и ЗАМЕНА. В показанном примере формула в ячейке C5:
=RIGHT(B5,LEN(B5)-FIND("*",SUBSTITUTE(B5,".","*",LEN(B5)-LEN(SUBSTITUTE(B5,".","")))))
Примечание. В общей форме формулы «домен» представляет собой домен или адрес электронной почты, например www.domain.com.
Объяснение
В этом примере ячейка C5 содержит эту формулу:
=RIGHT(B5,LEN(B5)-FIND("*",SUBSTITUTE(B5,".","*",LEN(B5)-LEN(SUBSTITUTE(B5,".","")))))
По сути, эта формула использует функцию ВПРАВО для извлечения символов, начиная с правого. Другие функции в этой формуле просто делают одно: они вычисляют, сколько символов нужно извлечь, n:
=RIGHT(B5,n) // n = ??
На высоком уровне формула заменяет последнюю точку «». в домене со звездочкой (*), а затем использует функцию НАЙТИ, чтобы определить положение звездочки. Как только позиция известна, функция RIGHT может извлечь TLD.
Как формула знает, что нужно заменить только последнюю точку? Это умная часть. Ключ здесь:
SUBSTITUTE(B5,".","*",LEN(B5)-LEN(SUBSTITUTE(B5,".","")))
В этом фрагменте кода последняя точка заменяется звездочкой (*).
Уловка состоит в том, что функция ЗАМЕНА имеет необязательный четвертый аргумент, который указывает, какой «экземпляр» найденного текста следует заменить. Если ничего не указано, заменяются все экземпляры. Однако, если, скажем, предоставляется номер 2, заменяется только второй экземпляр.
Чтобы выяснить, какой экземпляр заменить, используется функция LEN:
LEN(B5)-LEN(SUBSTITUTE(B5,".",""))
Длина домена без точек вычитается из полной длины домена. Результат - количество точек в домене.
В примере имени в B5 есть две точки в домене, поэтому число 2 используется как в номере экземпляра:
SUBSTITUTE(B5," ","*",2)
Это заменяет только вторую точку на «*». Тогда имя будет выглядеть так:
"www.domain * com"
Затем функция НАЙТИ берет на себя задачу выяснить, где именно находится звездочка в тексте:
FIND("*", "www.domain*com")
Результатом будет 11 (* находится на 11-й позиции), которое вычитается из общей длины домена:
LEN(B5)-11
Поскольку имя состоит из 15 символов, мы имеем:
14-11 = 3
Наконец, число 3 используется ВПРАВО так:
=RIGHT(B5,3)
В результате получается "com"
