Anzeige

Wie funktionieren die Funktionen Länge, Teil, Rechts und Links - Excel Anleitung

1 1 1 1 1 Bewertung 4.20 (5 Stimmen)

Die Funktionen Länge, Teil, Rechts und Links können hervorragend dafür verwendet werden, Inhalte aus Zellen zu trennen. Wenn Beispielsweise in einer einzigen Zelle die Postleitzahl und der Ort steht, kann dies suboptimal für die Weiterverarbeitung der Daten sein, erst recht wenn die Tabelle mehrere hundert Zeilen lang ist oder größer.

Wie man genau diesem Problem mit den genannten Formeln entgegnen kann und wie die Formeln im einzelnem aufgebaut sind, wird in dieser Anleitung erläutert.

Anzeige

Formelaufbau der Rechts- und Links- Funktion

Der Aufbau der Formeln für die Rechts- und Links- Funktion ist absolut identisch und denkbar einfach. Mit diesen Funktionen wird ein beliebig langer Teil des Inhalts einer beliebigen Zelle ausgegeben. Dabei ist es der Funktion egal, ob in der Zelle Zahlen, Leerzeichen oder Buchstaben stehen.

=Links(C5;5)

=Rechts(C5;5)

Der erste Wert in der Klammer ist die Zelle, aus der die Inhalte bezogen werden sollen.Der zweite Wert (durch Semikolon (;) getrennt) gibt die Anzahl der zu beziehenden Zeichen an.

Im Prinzip sagt die Formel folgendes:

Links-Funktion: Gebe von links gezählt die ersten 5 Zeichen der Zelle „C5“ aus.

Rechts-Funktion: Gebe von rechts gezählt die ersten 5 Zeichen der Zelle „C5“ aus.

Formelaufbau der Teil-Funktion

Der Aufbau der Teil-Funktion ist ein wenig komplizierter als bei den zuvor genannten Funktionen. Mit dieser Funktion kann von Links ein genau bestimmter Teil des Inhalts einer beliebigen Zelle ausgegeben werden.

=TEIL(C5;7;1000)

Der erste Wert in der Klammer ist die Zelle, aus der die Inhalte bezogen werden sollen.Der zweite Wert (durch Semikolon (;) getrennt) gibt die Stelle an, ab der die Inhalte ausgegeben werden sollen.Der dritte Wert gibt die Stelle an, bis zu der die Inhalte ausgegeben werden sollen, überschreitet der Wert die in der gewählten Zelle verfügbaren Zeichen, endet die Ausgabe mit dem letzten Zeichen in der Zelle.

Im Prinzip sagt die Teil-Formel folgendes:

Gebe von Links gezählt vom 7. Zeichen bis zum 1000. Zeichen alle Inhalte der Zelle „C5“ zurück.

Formelaufbau der Längefunktion

Den Aufbau der Länge-Funktion könnte man als primitiv bezeichnen, so kurz ist er, die Funktion ist jedoch trotzdem mächtig. Die Funktion gibt die Anzahl der Zeichen einer beliebigen Zeile aus, in Kombination mit anderen Funktionen ist das sehr nützlich, dazu später mehr.

=Länge(C5)

Hier wird lediglich die Angabe der Zelle benötigt, in der die Zeichen (Buchstaben, Zahlen, Leerzeichen und sonstige Zeichen) gezählt werden sollen.

Beispiel 1: Postleitzahl von Ort trennen und umgekehrt

Hier im Beispiel liegen uns die Daten wie folgt vor: In der Spalte „C“ stehen die Werte im folgenden Format „21129 Hamburg“ nun möchten wir die Inhalte sinnvoll aufteilen. In der Spalte „E“ soll nur die Postleitzahl stehen und in der Spalte „F“ soll ausschließlich der Ort stehen. Also in „E5“ 21129 und in „F5“ Hamburg.

Postleitzahl mit Linksfunktion

Um die Postleitzahl abzutrennen, nutzen wir die Linksfunktion, genau wie oben beschrieben.

Links-Funktion Excel

Ort mit Teilfunktion

Um den Ort abzutrennen, nutzen wir an dieser Stelle die Teilfunktion, ebenfalls genau wie oben beschrieben.

Teil Funktion Excel

Beispiel 2: Postleitzahl von Ort trennen und umgekehrt (Reihenfolge verdreht)

Hier im Beispiel liegen uns die Daten wie folgt vor: In der Spalte „C“ stehen die Werte im folgenden Format „Hamburg 21129“ nun möchten wir auch hier die Inhalte sinnvoll aufteilen. In der Spalte „E“ soll wieder nur die Postleitzahl stehen und in der Spalte „F“ ausschließlich der Ort. Also in „E18“ 21129 und in „F18“ Hamburg.

Postleitzahl mit Rechtsfunktion

Die Postleitzahl abzutrennen ist wieder ziemlich einfach mit der „Rechts-Funktion“ zu realisieren, nochmals wie oben beschrieben.

Rechts Funktion Excel

Ort mit Links- und Längefunktion

Um in diesem Beispiel den Ort abzutrennen, müssen 2 Formeln verschachtelt werden. Im Prinzip wird hier die Links-Funktion angewendet, wobei mit Hilfe der Längefunktion die Anzahl der Auszugebenen Zeichen bestimmt wird.

Das Problem ist, dass die Ortsnamen keine einheitliche Länge haben, darum muss diese an dieser Stelle automatisiert herausgefunden werden. Dies übernimmt die Länge-Funktion, diese bestimmt allerdings nur die Gesamtanzahl der Zeichen in der Zeile. Das ist aber kein Problem, da die Postleitzahl eine einheitliche Länge hat (5 Zeichen) + 1 Leerzeichen ergibt 6 Zeichen, die von den Gesamtzeichen der Zelle abgezogen werden muss, um die exakte Zeichenlänge des Ortsnamens herauszufinden.

Würde man allein die Längefunktion in der Zelle „F18“ verwenden, würde als Ergebnis eine 13 erscheinen, wenn man dort die eben definierten 6 Zeichen abzieht, würde das Ergebnis „7“ lauten. Bei den anderen Orten verändert sich das Ergebnis natürlich je nach Länge des Ortsnamens.

Links- mit Längefunktion

Wenn alle Ortsnamen 7 Zeichen hätten, würde die Formel wie folgt aussehen: „=Links(C18;7)“, dies ist aber nicht der Fall, darum wird nun sozusagen die „Längeformel“ (zusammen mit der „-6“ wegen der PLZ) an die Stelle der „7“ eingefügt, diese generiert so also an der richtigen Stelle die richtige Zahl. Das Ergebnis wird jedoch für uns unsichtbar direkt in die Linksformel kopiert.

Die Formel sieht nun wie folgt aus: „=Links(C18;Länge(C18)-6)

Anzeige

Noch auf der Suche nach einer Lösung? Jetzt Hilfe anfordern!

Kommentare: (5)

Jörg Eggen
04.08.2015, 16:35 Uhr
Hallo,
und wie funktioniert das ganze wenn sowohl die Zahlen als auch die Namen verschieden lang sind (z.B. bei Strasse und Hausnummer, wenn in eine Spalte der Strassenname (z.B. Rundgasse, Hermann-von-Rund Strasse, Runder Weg) und in die andere Spalte die Hausnummer geschrieben werden soll (z.B. 1, 122, 37b).
Vielen Dank
05.08.2015, 11:35 Uhr
Hallo Jörg,

die Sache ist ein bisschen komplexer.

Das an dieser Stelle bis ins Detail zu erläutern, würde wahrscheinlich den Rahmen sprengen. Ich versuche mal bis zum Wochenende eine Anleitung dazu zu verfassen.

Gerne gebe ich aber hier schonmal meine bisherigen Formeln weiter.

Vielleicht noch nicht perfekt, aber funktionieren sollte es.

[b]Ausganstext:[/b] (Zelle B4)

98765, Wunschort, Runder Weg 37b

[b]Erster Wert vor dem Komma:[/b] (beliebige Länge, hier "PLZ")

=TEIL(B4;1;SUCHEN(",";B4;1)-1)

[b]Wert nach dem ersten Komma:[/b] (beliebige Länge, hier "Wunschort")

=TEIL(B4;SUCHEN(",";B4;1)+2;SUCHEN(",";B4;SUCHEN(",";B4;1)+1)-SUCHEN(",";B4;1)-2)

[b]Werte hinter dem zweiten Komma:[/b] (beliebige Länge, hier "Runder Weg 37b")

=RECHTS(B4;LÄNGE(B4)-1-SUCHEN(",";B4;SUCHEN(",";B4;1)+1))

Da es bei den Werten hinter dem letzten Komma keine Trennungsmerkmale wie ein Komma gibt, ist das Ergebnis zwar nicht perfekt, sollte aber immer noch eine Hilfe sein. Das Leerzeichen wurde zumindest in meinem Test nicht als mögliches Trennzeichen angenommen.

[b][i]Abspaltung der Hausnummer:[/i][/b] (fixe Länge, hier "37b", Formel steht in Zelle G4)

=GLÄTTEN(RECHTS(B4;3))

Die Länge wurde auf 3 festgesetzt, je nachdem,bei welcher Zeichenanzahl weniger falsche Werte ausgegeben werden, kann hier variiert werden.

[i][b]Abspaltung des Straßennamens:[/b][/i] (beliebige Länge, hier "Runder Weg")

=GLÄTTEN(ERSETZEN(RECHTS(B4;LÄNGE(B4)-1-SUCHEN(",";B4;SUCHEN(",";B4;1)+1));SUCHEN(G4;RECHTS(B4;LÄNGE(B4)-1-SUCHEN(",";B4;SUCHEN(",";B4;1)+1));1);100;""))

Hier wird Zelle G4 (Hausnummer) als Hilfe genommen.

Achtung: Wenn an dieser Stelle die Hausnummer falsch ausgegeben wird, wird auch der Straßenname falsch ausgegeben!

Ich hoffe, das ist auch ohne große Erläuterung hilfreich. Im Grunde sollte es funktionieren, wenn man die Formel kopiert und die Zellbezüge anpasst.

Gruß

Sven
https://www.anleitung24.com
07.08.2015, 15:29 Uhr
Hallo Jörg,

ich habe nun eine Anleitung zu dem Thema verfasst. Allerdings einen gänzlich anderen Lösungsweg eingeschlagen. Ich denke der ist für die Meisten einfacher und erfüllt auch grundlegend noch die Bedingung der perfekten Hausnummernabspaltung ;)

Aber beide Vorgehensweisen haben Ihre Vorteile.

Hier der Link:

https://www.anleitung24.com/text-in-spalten-inhalte-von-einer-spalte-in-mehrere-aufteilen-excel-anleitung.html
https://www.anleitung24.com
denilo
19.04.2018, 11:34 Uhr
Vielen vielen Dank für diese Erklärung und vor allem für die Lösungswege in deinem Nachtrag! Hat mir uuuunglaublich weitergeholfen!!

Die Separierung via "Text zu Spalten" ist zwar nützlich, hilft aber nichts bei automatisierten Reports, bei dem keine manuellen Eingriffe mehr vorgenommen werden sollen.
19.04.2018, 20:58 Uhr
Hi Denilo,

danke für deinen Kommentar, freut mich, dass dir mein Beitrag geholfen hat.

Klar, welche Herangehensweise besser ist, hängt immer davon ab, was man genau erreichen möchte.

Gruß

Sven
https://www.anleitung24.com

Kommentar schreiben:

Noch 5000 Zeichen übrig.
Hinweise zur Verarbeitung Ihrer Angaben und Widerspruchsrechte finden Sie in unserer Datenschutzerklärung.
Anzeige
Cookies helfen uns bei der Bereitstellung unserer Dienste. Mit der Nutzung dieser Website erklären Sie sich damit einverstanden, dass wir Cookies einsetzen. Weitere Informationen