Profi-Tipp: Text aus Zellen ausschneiden mit LÄNGE / LINKS / RECHTS
Folgendes Problem ist vielen Excel Anwendern bekannt: Sie haben eine große Datenmenge in Spalten, aber leider ist der Text nicht optimal angeordnet. Zum Beispiel befinden sich Städtenamen mit PLZ angeheftet in Spalte B. Sie wollen aber die PLZ separat in einer Spalte isolieren (Siehe Beispiel).
1. Beispiel: Berlin_10119 –> 10119
=RECHTS(„Berlin_10119“;5) oder =RECHTS(B3;5) im Beispiel oben
Das erste Beispiel, die PLZ vom Original Text zu trennen ist sehr einfach. Eine PLZ hat immer 5 Nummern, das bedeutet wir können einfach die 5 Zeichen von Rechts wiedergeben. Die Funktion =RECHTS besteht aus zwei Teilen: (1) Der Text von dem Zeichen ausgeschnitten werden sollen und (2) die Anzahl der Zeichen.
Ähnlich geht das Beispiel Hamburg_20097, 5 Zeichen von Rechts ergeben die gewünschte PLZ
2. Beispiel: München_80997 –> München
=LINKS(„München_80997“;7) oder =LINKS(B5;7) im Beispiel oben
Die =LINKS Funktion ist genau wie die =RECHTS Funktion anzuwenden. Einfach den Text wählen und die Anzahl der Zeichen angeben. Im Beispiel oben benötigen wir 7 Zeichen um München auszuschneiden.
Wenn wir aber jetzt diese Funktion auf den Rest der Daten anwenden wollen um sämtliche Städtenamen vom Original Text auszuschneiden, haben wir ein Problem: Nicht alle Städte bestehen aus 7 Zeichen. Excel bietet uns aber die Möglichkeit die genaue Anzahl der Zeichen zu ermitteln.
Wie ermitteln wir die Anzahl der Zeichen der einzelnen Städte?
=LÄNGE ist eine Funktion die die Anzahl der Zeichen in einer Zelle findet. Im Beispiel oben schaut das so aus:
=LÄNGE(B5) –> 13
Wir wissen jetzt also dass die Zelle „München_80997“ aus 13 Zeichen besteht. Was wir auch wissen ist dass nach dem Städtenamen 6 weitere Zeichen folgen (5 für die PLZ und ein weiteres für _). Um die gewünschten 7 Zeichen zu erhalten müssen wir also nur 6 von der Gesamtanzahl abziehen.
=LINKS(B5;LÄNGE(B5)-6) –> München
Die Kombination von =LINKS mit =LÄNGE kann somit jeden Städtenamen isolieren, so lange die Anzahl der Zeichen nach dem Städtenamen immer genau 6 ist.
Haben Sie Fragen oder Probleme bestimmte Zeichen aus einer Zelle auszuschneiden?
Bei Problemen wenden Sie sich an unser Excel Forum, wir beantworten Ihre Anfragen gerne!
4 Comments
Frage oder Kommentar zu diesem Artikel?
Populäre Einträge
Tipps & Artikel auf Facebook
Neueste Einträge
- Zellen über Schaltflächen einfärben, wenn die bedingte Formatierung nicht möglich ist
- KFZ-Modell durch Listenfeldern auswählen (Excel Übung mit Download)
- Pivot-Tabelle mit mehreren Konsolidierungsbereichen erstellen [mit Download]
- Excel-Shortcuts: Die 5 besten Tastenkombinationen die Sie noch nicht kennen
- Rechnen mit Bezügen – Ergebnisse gleichzeitig berechnen [mit Download]
- Excel für Berechnungen einstellen – Rundungsfehler vermeiden [Schnelltipp]
- SVERWEIS, WENN, VERKETTEN – Adressen in einer Rechnung auslesen [mit Download]
- Digitale Würfel durch bedingte Formatierung in Excel erstellen
- Excel Kalender: Wochenenden einfärben
- SUMMEWENN Funktion – Beispiel mit Dropdown-Liste
- Wie kann ich den Durchschnitt der Top 5 Werte einer Liste berechnen?
- Zahlen besser formatieren mit diesen 4 Profi Tipps
aber wie sieht es mit einer liste aus mit unterschiedlichen Städten?
München 23455
Bad Kleinberg 34567
ILL 67890
usw
wie bekomme ich die Stadt in eine Spalte und die PLZ in eine andere?
LG
Hallo Tim,
in Deutschland hat die Postleitzahl immer 5 Stellen. Deshalb kannst du ganz bequem die Postleitzahl mit der Formel:
=RECHTS(A2;5) auslesen
Bei Ort ist es etwas komplizierter, aber nicht viel. Du nutzt wieder die 5 Stellen der Postleitzahl plus der 1 Stelle des Leerzeichens. Das macht zusammen eine Länge von 6 Stellen, die von Links abgezogen werden muss, damit der Ort übrig bleibt. Was leicht nachvollziehbar ist, weil Orte immer unterschiedliche Anzahlen von Buchstaben haben, die im Vorfeld nicht vordefiniert werden können. Aus Links und Länge machst du eine zusammengesetzte Formel:
=LINKS(A2;LÄNGE(A2)-6)
Du ziehst von der Gesamtlänge des Zellinhaltes 6 Stellen von rechts ab, deshalb -6.
Frohe Ostern und beste Grüße
Andreas
Mit welcher Formel macht man das, wenn die Länge unterschiedlich ist? Hab da schon mal irgendwo ein Formel mit „Finden“ gesehen, weiß aber nicht mehr, wie die ging.
In diesem Fall müsste man das Zeichen „_“ finden und das als Trennungsstelle in der Formel festlegen.
Und wie kombiniere ich es?
Z.B. wenn ich die Telefonvorwahl habe, die Stadt und die PLZ?
Also: 07353_Schwendi_88477 und ich nur die Stadt erhalten möchte?