Удалить текст до (включительно) от второго до последнего периода (или `://`, если только один период)

Question

У меня есть следующие данные:

http://123.456.789.10.com
http://789.12340.com
http://456.789.1123123rs.com
http://12321.com

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

10.com
12340.com
1123123rs.com
12321.com

Примечание: если есть только один период (как в последнем примере), я хочу избавиться от всего, вплоть до (включительно) :// .

Я пытался: (значение хранится в ячейке D2)

=RIGHT(D2,FIND(".",D2,FIND(".",D2)+1)-1) (что не работает хорошо при использовании RIGHT, но работает отлично при использовании LEFT)

=RIGHT(D2,FIND(".",D2)+1)

Я не знаю, чего не хватает ...

Может кто-нибудь помочь?

Answer 1

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

Краткое описание алгоритма:

используйте инструмент оценки формулы, чтобы разделить его и посмотреть, как он работает в деталях. Как отмечено в комментариях, вы увидите, что 99 устанавливает ограничение на количество символов, которые вы можете вернуть. Это, возможно, придется изменить, если ваши возвращенные данные могут быть длиннее

• удалить все вплоть до ://
• заменить 2-ую из последней точки на редко используемый символ
• используйте функцию FIND чтобы найти эту замену, чтобы сгенерировать начальный номер для функции MID

---

=MID(A1,FIND("://",A1)+2+FIND(CHAR(1),SUBSTITUTE("." &MID(A1,FIND("://",A1)+3,99),".",CHAR(1),LEN("." &A1)-LEN(SUBSTITUTE("." &A1,".",""))-1)),99)

Answer 2

=RIGHT(D2, 
 LEN(D2) - FIND("\", SUBSTITUTE(D2, ".", "\", LEN(D2) - LEN(SUBSTITUTE(D2, ".", "")) - 1))

---

Объяснение:

Во-первых, мы получаем количество периодов с помощью этого простого трюка:

1. Определяем длину полного текста:
   =LEN(D2) (см. Столбец E на рисунке)
2. Мы определяем длину того же текста, но со всеми удаленными периодами:
   =LEN(SUBSTITUTE(D2, ".", "")) (См. Столбец F на рисунке)
3. Разница будет в количестве периодов:
   =LEN(D2) - LEN(SUBSTITUTE(D2, ".", "")) (См. Столбец G на рисунке)

Во-вторых, поскольку мы знаем число периодов, мы можем определить число появлений последнего, кроме одного периода. Например, если число периодов равно 5 , последний, но один период имеет номер вхождения 4:

• =LEN(D2) - LEN(SUBSTITUTE(D2, ".", "")) - 1 (см. Столбец H на рисунке)

В-третьих, мы используем это как 4-й параметр функции SUBSTITUTE(), чтобы заменить это вхождение точки символом \ :

• =SUBSTITUTE(D2, ".", "\", LEN(D2) - LEN(SUBSTITUTE(D2, ".", "")) - 1) (см. Столбец I на рисунке)

В-четвертых, мы определяем положение этого символа (\) с помощью функции FIND ():

• =FIND("\", SUBSTITUTE(D2, ".", "\", LEN(D2) - LEN(SUBSTITUTE(D2, ".", "")) - 1)) (см. Столбец J на рисунке)

В-пятых, поскольку мы знаем положение этого символа (\) и длину полного текста, мы можем определить количество оставшихся символов, т. Е. Символов после \ . Например, если длина полного текста равна 5, а позиция \ равна 3, остаются 2 оставшихся символа:

• =LEN(D2) - FIND("\", SUBSTITUTE(D2, ".", "\", LEN(D2) - LEN(SUBSTITUTE(D2, ".", "")) - 1)) (см. Столбец K на картинке)

Наконец, мы используем его как второй параметр функции RIGHT() :

• =RIGHT(D2, LEN(D2) - FIND("\", SUBSTITUTE(D2, ".", "\", LEN(D2) - LEN(SUBSTITUTE(D2, ".", "")) - 1)) (см. столбец L на рисунке)

и это окончательная формула.

---

Замечания:

Последний пример в вопросе имеет не второй, а один период (в отличие от названия вопроса). Чтобы включить такую возможность, добавьте . после http:// , т.е. заменить все D2 в формуле на

=SUBSTITUTE(D2, "//", "//.")

Answer 3

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

Например, если самый длинный набор символов между периодами может быть, о, 95 в ваших ожидаемых данных, то строка замены, скажем, 250 "пробелов", заменяющих каждый период, будет работать хорошо.

Затем поместите функцию SUBSTITUTE() функцию RIGHT() . Если ваш результат должен быть длиной всего 3 символа, вам нужно 253 символа справа. У вас есть 502, прежде чем вы берете что-нибудь нежелательное. Если в результате получится целых 95 ожидаемых плюс 250 пробелов плюс еще 95, вам нужно 420 символов, чтобы покрыть себя. Но все равно есть 502, которые вы можете взять, не забирая нежелательных персонажей. Поэтому выберите число между максимальным ожидаемым значением 420 и маской, которую вы можете использовать, 502: возможно, 490, и возьмите самые правые 490 символов с помощью функции RIGHT() .

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

Если пробелы могут быть в желаемом результате, используйте другой символ, который не будет. Что-то странное, глядя в список шрифтов возможных символов. Возможно ¬ (Alt-01452). Или не рискуйте и используйте это вместо того, чтобы когда-либо использовать пробелы.

Не вводите 250 пробелов. Используйте функцию REPT() в первой (внутренней) функции SUBSTITUTE() чтобы один раз набрать выбранного персонажа, но получите 250 из них!

Существуют и другие подходы, но обычно они предполагают, сколько персонажей может быть. Например, ваши данные могут быть достаточно корректными, чтобы знать, что всегда есть 4 периода. Тогда они работают хорошо. Но если это могло измениться, 3 здесь, 7 там, хорошо ...

Выше сказанное скорее "брутально", чем "элегантно", но кого это волнует? Простой в концепции, так прост в использовании, и он делает свое дело.

Answer 4

=IF(LEN(A1)-LEN(SUBSTITUTE(A1, ".", "", 2)), SUBSTITUTE(TRIM(RIGHT(SUBSTITUTE(A1, ".", REPT(" ", LEN(A1))), LEN(A1)*2)), " ", "."), IFERROR(RIGHT(A1, LEN(A1)-FIND("://", A1)-2), A1))

Как это работает?

Сначала у нас есть оператор IF который позволяет нам по-разному относиться к вещам, если есть один (или меньше) периодов, или если есть хотя бы 2 периода.

Наш логический тест - проверить, существует ли второй период: LEN(A1)-LEN(SUBSTITUTE(A1, ".", "", 2))
Это вернет 0 если он не может найти 2 периода в строке, или 1), если есть 2 или более.

Затем мы сначала помещаем формулу «если есть 2 или более периодов», а формулу «если есть менее 2 периодов» - вторые, но, поскольку последние намного проще, я расскажу о них в первую очередь.

Если есть менее 2 периодов:

Если есть один или ноль периодов, то нам нужно удалить текст до и включая :// в начале, поэтому мы найдем позицию :// и возьмем только текст после него: RIGHT(A1, LEN(A1)-FIND("://", A1)-2)
Только в случае , если есть данные , которые не начинаются с протоколом , заканчивающимся :// мы должны обернуть это с IFERROR и получить всю исходную строку вместо ошибки #VALUE это заканчивается существо: IFERROR(RIGHT(A1, LEN(A1)-FIND("://", A1)-2), A1)

Если есть (по крайней мере) 2 периода

Теперь, когда у нас есть более простые случаи, давайте посмотрим, что происходит, когда существует 2 (или более) периода: SUBSTITUTE(TRIM(RIGHT(SUBSTITUTE(A1, ".", REPT(" ", LEN(A1))), LEN(A1)*2)), " ", ".")

Давайте разберемся с этим:

1. Мы заменяем все периоды большим количеством пробелов (столько, сколько имеется символов во всей исходной строке): SUBSTITUTE(A1, ".", REPT(" ", LEN(A1)))
2. Мы берем только конец этого, а именно удвоенную длину исходной строки. Это дает нам последние две части и кучу пробелов перед и между двумя частями: RIGHT(<step 1>, LEN(A1)*2)
   Примечание. Если вы хотите включить следующий раздел, измените значение *2 на *3 .
3. Мы удаляем все посторонние пространства, используя TRIM , оставляя нам только один пробел, где оставшийся период должен быть: TRIM(<step 2>)
4. Заменим это оставшееся пространство на .: SUBSTITUTE(<step 3>, " ", ".")

Таким образом, вся наша формула выглядит следующим образом:=IF(LEN(A1)-LEN(SUBSTITUTE(A1, ".", "", 2)), SUBSTITUTE(TRIM(RIGHT(SUBSTITUTE(A1, ".", REPT(" ", LEN(A1))), LEN(A1)*2)), " ", "."), IFERROR(RIGHT(A1, LEN(A1)-FIND("://", A1)-2), A1))