Ich habe eine URL der folgenden Form:
http://www.example.com/page.html?param1=asdf¶m2=asdfg¶m3=asdfgh
Ich möchte den Wert von Param2 oder 3 aus der URL extrahieren und diesen bestimmten Param aus der URL entfernen. Irgendwelche Ideen, wie man das mit Excel macht?
Antwort1
Ich glaube nicht, dass Excel über integrierte Funktionen zum Umgang mit URLs verfügt. Sie müssen daher auf eine kreative Kombination herkömmlicher Funktionen zur Zeichenfolgenbearbeitung wie LEN(), MID()und zurückgreifen SEARCH().
Angenommen, Ihre URL befindet sich in der Zelle A1und der Parameter, dessen Wert Sie extrahieren möchten, befindet sich in der Zelle B1, versuchen Sie es mit den folgenden Formeln.
So erhalten Sie den Wert des angegebenen Parameters:
=IFERROR(MID(A1; SEARCH(B1 & "="; A1) + LEN(B1) + 1; IFERROR(SEARCH("&"; A1; SEARCH(B1 & "="; A1)) - SEARCH(B1 & "="; A1) - LEN(B1) - 1; LEN(A1))); "")
So entfernen Sie den Parameter und seinen Wert aus der URL:
=IFERROR(REPLACE(A1; SEARCH(B1 & "="; A1); IFERROR(SEARCH("&"; A1; SEARCH(B1 & "="; A1)) - SEARCH(B1 & "="; A1) + 1; LEN(A1)); ""); A1)
Hinweis: Abhängig von Ihren regionalen Einstellungen müssen Sie möglicherweise alle Semikolons in den Formeln durch Kommas ersetzen.
Ausführliche Erklärung
Um den Wert des angegebenen Parameters zu erhalten, müssen wir ihn zunächst innerhalb der URL lokalisieren:
=SEARCH(B1 & "="; A1)
DerSEARCH()Die Funktion lokalisiert den ersten Parameter (den angegebenen Parameter) innerhalb des zweiten Parameters (der URL) und gibt die Nummer der Startposition zurück, an der er vorkommt. Beachten Sie, dass wir dem Namen des Parameters das Gleichheitszeichen anhängen, um sicherzugehen, dass wir nach dem richtigen Objekt suchen. Andernfalls param1könnte die Suche nach stattdessen die Position von zurückgeben, beispielsweise param10wenn es früher in der URL vorkommt.
Wenn der Parameter gefunden wurde, müssen wir den Teil (oder Teilstring) der URL zurückgeben, der dort beginnt, wo der Wert des Parameters beginnt und direkt vor dem nächsten Et-Zeichen endet. Dazu verwenden wir dasMID()Funktion, die drei Parameter annimmt: die Zeichenfolge, aus der die Teilzeichenfolge zurückgegeben werden soll, die Startposition und die Anzahl der zurückzugebenden Zeichen.
=MID(A1; SEARCH(B1 & "="; A1); LEN(A1))
Wir nutzen auch dieLEN()Funktion, die einfach die Länge der angegebenen Zeichenfolge (in diesem Fall die URL) zurückgibt. Dies ist vorerst nur ein Platzhalter (der dritte Parameter wurde erst in Excel 2010 optional), wird aber später nützlich sein, wenn wir den Wert des letzten Parameters benötigen.
Zuerst müssen wir die Startposition vom Anfang des Parameters selbst dorthin verschieben, wo sein Wert beginnt. Dazu addieren wir zur Position des Parameters innerhalb der Zeichenfolge seine Länge sowie 1 (für das =Vorzeichen).
=MID(A1; SEARCH(B1 & "="; A1) + LEN(B1) + 1; LEN(A1))
Das ist besser, der zurückgegebene Wert beginnt an der richtigen Stelle. Damit er an der richtigen Stelle endet, müssen wir das nächste Et-Zeichen finden, das angibt, wo der nächste Parameter beginnt.
=SEARCH("&"; A1; SEARCH(B1 & "="; A1))
Wir verwenden die SEARCH()Funktion erneut und fügen dieses Mal einen dritten Parameter hinzu, der die Position angibt, an der die Suche beginnen soll – uns interessieren nicht die Et-Zeichen vor dem angegebenen Parameter, sondern nur die danach.
Um die Länge des Parameterwerts aus dem Obigen zu erhalten, müssen wir die Position, an der der Parameter beginnt, die Länge des Parameters und erneut 1 für das =Vorzeichen abziehen.
=SEARCH("&"; A1; SEARCH(B1 & "="; A1)) - SEARCH(B1 & "="; A1) - LEN(B1) - 1
Dies funktioniert für alle Parameter außer dem letzten, da am Ende der URL kein Et-Zeichen steht. In diesem Fall können wir denIFERROR()Funktion zum Erkennen des von Excel ausgegebenen Fehlers und zum Zurückgeben einer festen Zahl. Eine gute Wahl ist LEN(A1)- die Länge der Zeichenfolge ist garantiert größer als die Länge aller ihrer Teilzeichenfolgen, und wenn wir dies als drittes Argument an übergeben MID(), werden alle Zeichen von der angegebenen Position bis zum Ende der Zeichenfolge zurückgegeben, was genau das ist, was wir brauchen.
=IFERROR(SEARCH("&"; A1; SEARCH(B1 & "="; A1)) - SEARCH(B1 & "="; A1) - LEN(B1) - 1; LEN(A1))
Kombinieren wir dies mit dem obigen Ergebnis, erhalten wir die folgende Formel:
=MID(A1; SEARCH(B1 & "="; A1) + LEN(B1) + 1; IFERROR(SEARCH("&"; A1; SEARCH(B1 & "="; A1)) - SEARCH(B1 & "="; A1) - LEN(B1) - 1; LEN(A1)))
Eine letzte Sache: Wenn B1ein Parameter enthalten ist, der in der URL nicht vorhanden ist, gibt das obige einen #VALUEFehler zurück. Um in einem solchen Fall eine leere Zeichenfolge (oder einen anderen geeigneten Wert) zurückzugeben, packen Sie das Ganze in ein weiteres IFERROR():
=IFERROR(MID(A1; SEARCH(B1 & "="; A1) + LEN(B1) + 1; IFERROR(SEARCH("&"; A1; SEARCH(B1 & "="; A1)) - SEARCH(B1 & "="; A1) - LEN(B1) - 1; LEN(A1))); "")
Die Formel zum Entfernen des Parameters und seines Wertes aus der URL ist ziemlich ähnlich. Sie verwendet denREPLACE()Funktion zum Ersetzen einer gegebenen Teilzeichenfolge der URL (die mehr oder weniger auf die gleiche Weise wie oben definiert ist) durch eine leere Zeichenfolge.
Sie können die Formeln wahrscheinlich etwas rationalisieren, indem Sie häufig verwendete Blöcke (wie SEARCH(B1 & "="; A1)) in eigene Spalten verschieben und diese Zellen referenzieren, anstatt die Formel mehrmals einzugeben. Diese zusätzlichen Spalten können dann ausgeblendet werden.