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