Du bist hier::--Excel Formeln – Alle vorkommenden Zeichen in einem Bereich auflisten

Excel Formeln – Alle vorkommenden Zeichen in einem Bereich auflisten

In Excel alle verwendeten Zeichen aus einem Bereich auszulesen geht mit den nicht verschachtelten Standardformeln nicht. Dafür benötigt man spezielle Formeln. Möchten Sie aber wissen, wie man so etwas macht, dann lesen Sie diesen Artikel.

Wie man die verwendeten Zeichen aus einer Zelle auslesen kann, habe ich hier schon erklärt. Nun möchte ich ihnen aber zeigen, dass man nicht nur die Zeichen aus einer Zelle, sondern auch aus einem Zellbereich auslesen kann. Wer jetzt denkt, das wäre viel komplizierter, der irrt.

Hilfszellen (B1):

Zu Beginn schreiben Sie bitte in die Zellen A1 und abwärts den Text, der auf die vorkommenden Zeichen untersucht werden soll. In meinem Fall habe ich dazu die Zellen A1-A6 genommen, Sie dürfen natürlich auch mehr oder weniger Zellen dafür verwenden. Um aber am Ende alle Zeichen aufgelistet zu bekommen, benötigen Sie noch Hilfszellen. Diese müssen zuerst erstellt werden. Schreiben Sie deshalb nun in Zelle B1 folgende Formel:

=WECHSELN(A1&A2&A3&A4&A5&A6;" ";)

Diese Formel ist darauf angepasst, dass Sie sechs Zellen mit Text verwendet haben, also von A1-A6. Sollten Sie mehr oder weniger Zellen dafür benutzt haben, so ändern Sie die Formel einfach ab. Haben Sie mehr Zellen verwendet, so schreiben Sie hinter „A1&A2&A3&A4&A5&A6“ einfach noch „&A7“. Dies wiederholen Sie sooft, bis alle Zellen in der Formel markiert wurden.

(Zur Darstellung in Originalgröße Abbildungen anklicken)
01-excel-formeln-alle-vorkommenden-zeichen-in-einem-bereich-auflisten-b1-470.png?nocache=1308577895268

Auflistung (nach vorkommen im Text):

In Zelle C1 und abwärts werden nun die Zeichen aufgelistet in der Reihenfolge, in der sie auch im Text in den Zellen A1-A6 vorgekommen sind. Schreiben Sie deshalb nun in die Zelle C1 diese Formel:

=LINKS(B1)

Diese Formel muss auch in der anderen Zellen der Spalte C stehen. Kopieren Sie diese Formel nun also nach unten. Fahren Sie dazu mit der Maus über die rechte untere Ecke der Zelle C1. Die Maus sollte sich nun in ein Kreuz verwandelt haben. Jetzt klicken sie bitte mit der linken Maustaste und halten sie gedrückt. Währenddessen fahren Sie mit der Maus einige Zellen nach unten. In meinem Fall waren es 20, bei ihnen kann es durchaus mehr oder weniger sein. Sind Sie bei der gewünschten Zelle angekommen, so lassen Sie den Mauszeiger nun wieder los.

02-excel-formeln-alle-vorkommenden-zeichen-in-einem-bereich-auflisten-c1-470.png?nocache=1308577912320

Hilfszellen (B2 bis Ende):

Die Spalte B ist, wie oben schon beschrieben für die Hilfszellen da. Da die Formel für die erste Zelle der Hilfszellen eine andere ist, als die für den Rest, muss hier nun natürlich eine andere Formel eingegeben werden, als noch im ersten Schritt. Nun lautet die Formel:

=WECHSELN(B1;C1;"")

Da diese Formel auch nun für alle anderen Hilfszellen gebraucht wird, können Sie diese einfach in die anderen B Zellen kopieren. Fahren Sie dazu mit der Maus wieder an die rechte untere Ecke der Zelle B2. Nun ist die Maus zu einem Kreuz geworden. Klicken Sie nun mit der linken Maustaste und halten Sie sie gedrückt, während Sie mit der Maus einige Zellen nach unten gehen. In meinem Fall hat es bis Zelle 20 ausgereicht. Lassen Sie dann ihre Maus wieder los. Jetzt sehen Sie in der Spalte B jeweils den Standardtext, der pro Zelle nach unten immer um ein Zeichen kürzer wird. In Spalte C sind die Buchstaben aufgelistet, die aus der Zelle links davon (also die Zelle B) eliminiert werden. Dies sind auch schon die geordneten Zeichen.

03-excel-formeln-alle-vorkommenden-zeichen-in-einem-bereich-auflisten-b2-470.png?nocache=1308577935928

Zeichen in einer Zelle:

Nun stehen in Spalte C alle Zeichen, die im Bereich A1-A6 auftreten. Möchten Sie diese Zeichen nun alle wieder vereint in einer Zelle haben (aber eben nur einzeln auftretend), so geht auch das mit einer Formel. Diese dafür lautet (am Besten in E1 schreiben):

=C1&C2&C3&C4&C5&C6&C7&C8&C9&C10&C11&C12&C13&C14&C15&C16&C17&C18&C19&C20

Auch hier ist es wie bei der ersten Hilfszellenformel so, dass die Anzahl an Zellen verschieden sein kann. Haben Sie in der Spalte C mehr oder weniger Zellen gebraucht, so machen Sie dies bitte auch in der Formel kenntlich, indem Sie die weiteren Zellen noch anhängen (&C21).

04-excel-formeln-alle-vorkommenden-zeichen-in-einem-bereich-auflisten-nach-eingabe-470.png?nocache=1308577978160

Auflistung (nach Zeichencode):

In Spalte C sind die Zeichen nun nach vorkommen im Text geordnet. Möchten Sie aber, dass sie nach ihrem Zeichencode geordnet sind, so ist das auch möglich. Geben Sie dazu in der ersten Zelle einer weiteren Spalte, wie zum Beispiel D1 die folgende Formel ein:

=WENN(B1="";"";ZEICHEN(KKLEINSTE(CODE(WENN(C$1:C$20="";ZEICHEN(255);C$1:C$20));ZEILE(A1))))

Dies ist aber eine Matrixformel. Das bedeutet, dass die Formel nicht einfach mit einem Druck auf die Enter Taste bestätigt werden kann, sondern dafür STRG+SHIFT+Enter gedrückt werden müssen. Bis Sie diese drei Tasten drücken können, müssen Sie aber zuerst noch einmal oben in die Bearbeitungszeile der Formel klicken. Anschließend sollte nun in der Zelle D1 das erste Zeichen erscheinen. Da die Formel auch für die anderen Zellen der Spalte D identisch ist, können Sie die Formel kopieren. Fahren Sie dazu über das rechte untere Eck der Zelle D1, woraufhin sich der Mauszeiger in ein Kreuz verwandelt. Nun klicken Sie mit der linken Maustaste und halten diese gedrückt, während Sie nach unten fahren. Nach ca. 20 Zellen – es können bei ihnen durchaus mehr sein – lassen Sie den Mauszeiger wieder los. Jetzt sehen Sie alle verwendeten Zeichen in nach Zeichencode geordneter Reihenfolge.

05-excel-formeln-alle-vorkommenden-zeichen-in-einem-bereich-auflisten-d1-470.png?nocache=1308578003929

Zeichen in einer Zelle:

Möchten Sie nun, dass die Zeichen wie Sie in der Spalte geordnet sind ein einer Zelle stehen, so müssen Sie die Zellen verknüpfen. Dazu nehmen Sie folgende Formel:

=D1&D2&D3&D4&D5&D6&D7&D8&D9&D10&D11&D12&D13&D14&D15&D16&D17&D18&D19&D20

Je nachdem, wie viele Zellen Sie in Spalte D benutzt haben, kann die Formel anders aussehen. Haben Sie nämlich mehr als 20 Zellen benutzt, so erweitern Sie bitte die Formel (&D21), bis Sie alle Zellen einschließt.

06-excel-formeln-alle-vorkommenden-zeichen-in-einem-bereich-auflisten-nach-code-470.png?nocache=1308578034490

Jetzt wissen Sie, wie man auch die Zeichen in einem Zellbereich und nicht nur in einer einzelnen Zelle anzeigen kann. Auch hier kann man wieder ordnen nach dem Zeichencode oder dem Vorkommen im Ursprungstext.

Von |2018-07-25T14:24:20+00:00Juni 21st, 2011|Kategorien: Tabellenkalkulation|0 Kommentare

Über den Autor:

Hinterlassen Sie einen Kommentar