У меня есть URL-адрес следующей формы:
http://www.example.com/page.html?param1=asdf¶m2=asdfg¶m3=asdfgh
Я хочу извлечь значение param2 или 3 из URL, а также удалить этот конкретный параметр из 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)) в свои собственные столбцы и ссылаясь на эти ячейки, вместо того, чтобы вводить формулу несколько раз. Эти дополнительные столбцы могут быть скрыты от просмотра.