Ich habe Adressen in einer Zelle und möchte sie in verschiedene Zellen in derselben Zeile extrahieren. Einige Zellen haben vier Adresszeilen und andere drei. Ich kann die Zellen mit drei Adresszeilen problemlos aufteilen, indem ich Text in Spalten und verschiedene Trennzeichen verwende, aber nicht die mit vier.
Bildbeschreibung hier eingeben
Im ersten Beispiel habe ich vier Zeilen und das zweite drei
Anchorage Oncology Centre
3801 University Lake Drive
Suite 300-B2
Anchorage, AK 99508 US
Ich möchte das Obige in 5 Zellen aufteilen. Je eine Zelle für Adresse, Stadt, Bundesland, Postleitzahl und Land
Anchorage Oncology Centre
3801 University Lake Drive
Suite 300-B2
Anchorage
AK
99508
US
im zweiten Beispiel unten
Providence Alaska Medical Center
3200 Providence Drive
Anchorage, AK 99508 US
Ich möchte
Providence Alaska Medical Center
3200 Providence Drive
Anchorage
AK
99508
US
Könnte dies mithilfe einer Formel erfolgen?
Danke
Antwort1
Angenommen, der Text „Anchorage, AK 99508 US“ befindet sich bei A1, geben Sie diese Formeln ein.
A4 --> =MID(A1,1,(FIND(",",A1,1))-1)
B4 --> =MID($A$1,(FIND(" ",$A$1,A3))+1,C3-(FIND(" ",$A$1,A3))-1)
C4 --> =MID($A$1,(FIND(" ",$A$1,(FIND(" ",$A$1,FIND(",",A1,1)))+1))+1,(FIND(" ",$A$1,(FIND(" ",$A$1,(FIND(" ",$A$1,FIND(",",A1,1)))+1))+1))-(FIND(" ",$A$1,(FIND(" ",$A$1,FIND(",",A1,1)))+1))-1)
D4 --> =MID($A$1,(FIND(" ",$A$1,(FIND(" ",$A$1,(FIND(" ",$A$1,FIND(",",A1,1)))+1))+1))+1,LEN(A1))
Schlüssel: find() wurde verwendet, um den Start-/Endwert für mid() zu ermitteln. Hier finden wir jedes "," und " " im A1-Text.
Antwort2
Oder... Sie könnten einen ganz anderen Ansatz wählen. Genauer gesagt zwei.
Eine Möglichkeit besteht darin, die Technik zu verwenden FILTERXML(). Sie macht sich daran, Ihre Daten in zwei Teile aufzuteilen, den „Rest“ und die letzte Zeile. Der „Rest“ kommt in die erste Ausgabezelle. (Ich zeige weiter unten eine recht einfache Möglichkeit, die Teile aufzuteilen.) Die letzte Zeile wird als Zeichenfolge bearbeitet, um das Komma-Leerzeichen und die Leerzeichen durch HTML-Tags ( </Something><Something>) zu ersetzen, wodurch die Zeichenfolge halb in eine HTML-Zeichenfolge umgewandelt wird. Anschließend wird das öffnende Tag vorangestellt und das schließende Tag angehängt, und dann werden die äußeren HTML-Tags hinzugefügt, die das Ganze umschließen. Dies ist die eigentliche Eingabe für die FILTERXML()Funktion und ihre Ausgabe sind die vier Zellen, die die Informationen der letzten Zeile enthalten.
Das ist einfacher, als es klingt, und wird nach ein paar Malen der Verwendung zur Routine, wenn es zeitlich eng gefasst ist. Der größte Aufwand besteht darin, die Daten in Teile aufzuteilen und vorzubereiten. Das liegt daran, dass die Daten bei jeder neuen Verwendung anders verpackt zu sein scheinen.
Ein Vorteil, abgesehen von der standardisierten Natur nach der Übung, ist, dass es „natürlich“ eine Array-Ausgabe erzeugt, anstatt {CSE}einen Eintrag ( Ctrl-Shift-Enter) zu benötigen. Ein weiterer Vorteil ist, dass Sie die Ausgabeelemente nach Nummer oder mit auswählen können [Last](und dieses Element der Adressierung kann mit String-Techniken erstellt werden), sodass Sie genaue Positionsdaten extrahieren können, wenn Sie diese angeben können.
Ein anderer Ansatz ist die Verwendung eines Excel 4 MacroBefehls („E4M“) namens EVALUATE(). Sie können ihn nicht direkt in einer Zellformel verwenden, sondern müssen einen benannten Bereich erstellen, um ihn aufzunehmen. Sie können das, was Sie ihm präsentieren, in Zellen erstellen oder alles in einer einzigen Formel ausarbeiten und diese in die Funktion im benannten Bereich einfügen. Was auch immer für Sie am besten funktioniert oder Ihren Neigungen am besten entspricht.
In jedem Fall FILTERXML()müssen Sie, wie bei , die Eingabe vorbereiten. Der Unterschied besteht darin, dass die Dinge, die Sie auf diese Weise tun, sehr vertraut sind und kleine Dinge, die schiefgehen, wie ein ausgelassenes Komma oder ein Tippfehler, „natürlich“ aussehen und relativ leicht zu finden sind. Bei FILTERXML()sieht alles „falsch“ aus und Sie sind eine Zeit lang nicht vertraut damit, wie die Ausgabe aussehen sollte, sodass Fehler leichter passieren und schwerer zu finden sind. Vorteil, EVALUATE().
Wie bereiten Sie also die Daten vor? Sie benötigen zwei Teile: den ersten Teil (den ich oben den „Rest“ genannt habe), den Sie einfach extrahieren und präsentieren, und den Teil, den Sie auf besondere Weise bearbeiten möchten. Im letzten Teil erstellen Sie dann eine Zeichenfolge, die wie die Art von Array aussieht, die Excel intern in seinen Formeln erstellt. Eine Zeichenfolge, die über eine Zeile angezeigt wird, könnte so aussehen, {"a","b",1,"K"}und dieselben Daten, die für die Anzeige in einer Spalte erstellt werden, würden so aussehen, {"a";"b";1;"K"}und natürlich können sie gemischt werden, um über einen Bereich von Spalten und Zeilen angezeigt zu werden. Sie möchten eine Zeichenfolge wie die letztere erstellen, mit Semikolons als Trennzeichen.
Aber Sie haben in der letzten Zeile kein einziges Trennzeichen. Sie haben ", " und " ". Also nehmen Sie zuerst diesen Teil und setzen ihn in ein, SUBSTITUTE()um das ", " ("Komma-Leerzeichen") in nur das Leerzeichen zu ändern. Jetzt haben Sie nur noch ein Trennzeichen und können es mit SUBSTITUTE()in das benötigte ";" ändern. Sie können auch die meisten der benötigten Anführungszeichen um alle Elemente herum anwenden, indem Sie ";"statt nur das verwenden ;. Tatsächlich ist die Verwendung von Anführungszeichen als Formelelemente in Excel ziemlich lästig, daher CHAR(34)ist die Verwendung für sie viel einfacher. "Die meisten", weil Sie sie nur zwischen Elementen hinzufügen können, wobei SUBSTIITUTE()die Anführungszeichen am Anfang und Ende weiterhin benötigt werden. Sie fügen sie also zusammen mit den entsprechenden geschweiften Klammern in die öffnenden und schließenden Zeichenfolgen ein. Sie haben jetzt eine geeignete Zeichenfolge, die Sie EVALUATE()auseinandernehmen können.
Bauen Sie das in eine Zelle ein und wenn es funktioniert, erstellen Sie den benannten Bereich und setzen Sie alles in EVALUATE()'s-Klammern. Sie werden in der Formel unten sehen, wie das aussieht. Es ist nicht klar, wie Ihre Ausgabe im Verhältnis zu den Eingabedaten aussehen wird, also habe ich einfach die beiden Zeilen unter einer Eingabe verwendet, also Eingabe in A1, Ausgabe in A2 und A3. Sie können nach Wunsch anpassen. Also, innerhalb des benannten Bereichs:
=EVALUATE("{"&CHAR(34)&SUBSTITUTE(SUBSTITUTE(RIGHT(A1,LEN(A1)-MAX(IFERROR(FIND(CHAR(10),A1,SEQUENCE(1,LEN(A1))),""))),", "," ")," ",CHAR(34)&";"&CHAR(34))&CHAR(34)&"}")
SEQUENCE()könnte verfügbar gewesen sein, als die Frage geschrieben wurde. Könnte aber auch nicht sein. Ich habe es der Einfachheit halber verwendet, da ich schließlich für Leute schreibe, die nach dem Problem suchen, aber eine aktuelle Lösung wollen. Allerdings gab es damals Ersatz dafür, wobei der übliche ROW(1:xxx)entweder einfach oder auf komplexe Weise verwendet wurde.
„Am Tag“, es hätte möglicherweise mit {CSE}dem Eintrag eingegeben werden müssen.
Es macht all die Dinge, die bereits besprochen wurden.
Das kleine Element hier ist, wie man die Eingabe aufteilt. Jedes Problem ist anders, aber Ihr Problem ist nicht zu schwer. Jede Zeile in der Eingabezelle endet mit Alt-Enteroder CHAR(10). Sie können sie also mit der Technik „Länge des Inhalts minus Länge des Inhalts nach SUBSTITUTE()Entfernung“ zählen. Dann umschließen Sie das mit einem , FIND()das etwas tut, was normalerweise nicht getan wird: Es verwendet den Parameter „Positionsnummer“ (dritter), um ein Array von Werten von 1 bis zur Länge der Eingabe zu nehmen, sodass Sie ein Array von FIND()'s haben, die jeweils eine Stelle weiter in die Eingabe hineinschauen. Das ergibt ein Array von Werten des nächsten Vorkommens, wenn es die Eingabe mit Fehlern am Ende durchgeht, da es nach dem letzten noch etwas gibt. Diese Fehler können nicht verwendet werden, also werden IFERROR()sie ausgeblendet. Die höchste gefundene Zahl ist der Bruchpunkt für die Eingabe, also verwenden Sie , MAX()um das auszugeben. Dann führen Sie zwei Formeln aus, ein LEFT()mit so vielen Zeichen, um alle zu erhalten, einfach, werfen Sie einfach die ersten paar Zeilen an Ort und Stelle ab (wie viele es auch sein mögen, 3, 4, 5, was auch immer). Dann RIGHT()sammelt ein der Eingabe der Länge der Eingabe minus dieses Bruchpunkts den Rest. Dies ist die Eingabe für die EVALUATE()Funktion.
Wie bei FILTERXML()ist das eigentlich alles ziemlich einfach, da das Material in jeder beliebigen Weise bearbeitet werden muss. Anschließend fügt es sich einfach in ein EVALUATE().