Удалить параметр URL в Excel

Я не верю, что в Excel есть встроенные функции для обработки URL, поэтому вам придется творчески комбинировать обычные функции работы со строками, такие как LEN() , MID() а также SEARCH() ,

Предполагая, что ваш URL находится в ячейке A1 и параметр, значение которого вы хотите извлечь, находится в ячейке B1 , попробуйте следующие формулы.

Чтобы получить значение данного параметра:

=IFERROR(MID(A1; SEARCH(B1 & "="; A1) + LEN(B1) + 1; IFERROR(SEARCH("&"; A1; SEARCH(B1 & "="; A1)) - SEARCH(B1 & "="; A1) - LEN(B1) - 1; LEN(A1))); "")

Чтобы удалить параметр и его значение из URL:

=IFERROR(REPLACE(A1; SEARCH(B1 & "="; A1); IFERROR(SEARCH("&"; A1; SEARCH(B1 & "="; A1)) - SEARCH(B1 & "="; A1) + 1; LEN(A1)); ""); A1)

Примечание. В зависимости от региональных настроек вам может потребоваться заменить все точки с запятой в формулах запятыми.

Детальное объяснение

Чтобы получить значение данного параметра, нам сначала нужно найти его в URL:

=SEARCH(B1 & "="; A1)

SEARCH() Функция находит первый параметр (данный параметр) во втором параметре (URL) и возвращает номер начальной позиции, где он встречается. Обратите внимание, что мы добавляем знак равенства к имени параметра, чтобы убедиться, что мы ищем правильную вещь. В противном случае поиск param1 вместо этого может вернуть местоположение, скажем, param10 если это происходит раньше в URL.

С найденным параметром нам нужно вернуть часть (или подстроку) URL-адреса, начиная с того, где начинается значение параметра и заканчивая непосредственно перед следующим амперсандом. Для этого мы используем MID() функция, которая принимает три параметра: строку, из которой нужно вернуть подстроку, позицию, с которой нужно начинать, и количество возвращаемых символов.

=MID(A1; SEARCH(B1 & "="; A1); LEN(A1))

Мы также используем LEN() функция, которая просто возвращает длину заданной строки (в данном случае URL). Пока это просто заполнитель (третий параметр не стал необязательным до Excel 2010), но он пригодится позже, когда нам понадобится значение последнего параметра.

Сначала нам нужно переместить начальную позицию от начала самого параметра к тому месту, где начинается его значение. Для этого мы добавляем к расположению параметра в строке его длину, а также 1 (для = знак).

=MID(A1; SEARCH(B1 & "="; A1) + LEN(B1) + 1; LEN(A1))

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

=SEARCH("&"; A1; SEARCH(B1 & "="; A1))

Мы используем SEARCH() снова, добавьте третий параметр, определяющий позицию, с которой начинается поиск - нас не интересуют амперсанды, предшествующие данному параметру, а только те, которые следуют за ним.

Чтобы получить длину значения параметра из вышеприведенного, нам нужно вычесть позицию, в которой начинается параметр, длину параметра и снова 1 для = знак.

=SEARCH("&"; A1; SEARCH(B1 & "="; A1)) - SEARCH(B1 & "="; A1) - LEN(B1) - 1

Это работает для всех параметров, кроме последнего, потому что в конце URL нет конечного амперсанда. Для этого случая мы можем использовать IFERROR()функция, чтобы обнаружить ошибку, которую дает Excel, и вместо этого вернуть некоторое фиксированное число. Хороший выбор LEN(A1) - длина строки гарантированно будет больше, чем длина любой из ее подстрок, и если мы передадим это в качестве третьего аргумента MID() , он вернет все символы из заданной позиции в конец строки, что как раз то, что нам нужно.

=IFERROR(SEARCH("&"; A1; SEARCH(B1 & "="; A1)) - SEARCH(B1 & "="; A1) - LEN(B1) - 1; LEN(A1))

Комбинируя это с вышесказанным, мы получаем следующую формулу:

=MID(A1; SEARCH(B1 & "="; A1) + LEN(B1) + 1; IFERROR(SEARCH("&"; A1; SEARCH(B1 & "="; A1)) - SEARCH(B1 & "="; A1) - LEN(B1) - 1; LEN(A1)))

И последнее: если B1 содержит параметр, который не существует в URL, выше возвращает #VALUE ошибка. Чтобы вернуть пустую строку (или любое другое подходящее значение) в таком случае, оберните все это в другой IFERROR() :

=IFERROR(MID(A1; SEARCH(B1 & "="; A1) + LEN(B1) + 1; IFERROR(SEARCH("&"; A1; SEARCH(B1 & "="; A1)) - SEARCH(B1 & "="; A1) - LEN(B1) - 1; LEN(A1))); "")

Формула для удаления параметра и его значения из URL очень похожа, используя REPLACE() функция для замены заданной подстроки URL (которая определена более или менее так же, как и выше) пустой строкой.

Возможно, вы можете немного упростить формулы, перемещая часто используемые блоки (например, SEARCH(B1 & "="; A1) ) к своим собственным столбцам и ссылкам на эти ячейки вместо того, чтобы вводить формулу несколько раз. Эти дополнительные столбцы могут быть скрыты от просмотра.