Hallo,
ich muß in einer Calc- Tabelle Teile aus Zeichenketten ausfiltern. Die Zeichenketten habe ich mir nicht selbst ausgedacht. Sie haben folggende Beispielstruktur:
1.1.12
6.3.4a
5.7.2
20.4.12
1.1.3.4a
Davon brauche ich jeweils die Zahlen zwischen dem 2. und 3. Punkt bzw, zwischen 2. Punkt und dem Ende, aber ohne Buchstaben. Die Zahlen werden dann in Dezimalzahlen umgewandelt.
Mit Suchen, Links, Rechts, Länge bin ich leider zu keinem brauchbaren Ergebnis für alle Eventualitäten gekommen. Vor allem weiß ich nicht, wie man Buchstaben ausfiltern kann, wenn es auch Wörter ohne Buchstaben gibt (Fehlermeldung).
Moritz
Formel
Moderator: Moderatoren
Wahrscheinlich wirst Du das in mehreren Schritten machen müssen.
Du könntest mal so anfangen:
- Spalte markieren, suchen ersetzen, regulärer Ausdruck, nur in Selektion
- suchen nach: [a-z], ersetzen durch: leer lassen
Jetzt hast Du mal die Buchstaben weg (evtl. wiederholen mit [A-Z].
Dann in einer zweiten Spalte:
=TEIL(A1;FINDEN(".";A1;FINDEN(".";A1)+1)+1;LÄNGE(A1))
A ist dabei die Spalte, welche Deine Werte enthält. Mit dieser Formel bekommst Du alles nach dem zweiten Punkt bis zum Ende.
Jetzt wird es immer noch Werte geben, die so aussehen:
3.4. Daraus filterst Du wieder nur die 3 raus: =LINKS(B5;FINDEN(".";B1)-1)
Man kann das natürlich auch in eine einzige Formel mit einer WENN-Abfrage packen, aber die wird leicht unübersichtlich und wahrscheinlich bist Du schneller, wenn Du es in zwei Schritten machst.
Aus dem Ergebnis machst Du dann mit der Funktion WERT() eine Zahl
Schöne Grüsse
Du könntest mal so anfangen:
- Spalte markieren, suchen ersetzen, regulärer Ausdruck, nur in Selektion
- suchen nach: [a-z], ersetzen durch: leer lassen
Jetzt hast Du mal die Buchstaben weg (evtl. wiederholen mit [A-Z].
Dann in einer zweiten Spalte:
=TEIL(A1;FINDEN(".";A1;FINDEN(".";A1)+1)+1;LÄNGE(A1))
A ist dabei die Spalte, welche Deine Werte enthält. Mit dieser Formel bekommst Du alles nach dem zweiten Punkt bis zum Ende.
Jetzt wird es immer noch Werte geben, die so aussehen:
3.4. Daraus filterst Du wieder nur die 3 raus: =LINKS(B5;FINDEN(".";B1)-1)
Man kann das natürlich auch in eine einzige Formel mit einer WENN-Abfrage packen, aber die wird leicht unübersichtlich und wahrscheinlich bist Du schneller, wenn Du es in zwei Schritten machst.
Aus dem Ergebnis machst Du dann mit der Funktion WERT() eine Zahl
Schöne Grüsse
Formel
Danke Lenu,
mein Problem ist es nicht, daß ich einmalig in einer Spalte die Buchstaben lösche. Die Zeichenketten sind als Projektnummern in einer Tabelle vorgeben. Die Ziffern hinter dem 2. Punkt (bis zum 3. Punkt bzw. zum Ende der Kette, wenn es keinen 3. Punkt gibt) bezeichnen eine Zeile in einer anderen Tabelle (die auch nicht von mir ist). Wenn dort also eine 12 steht, wird über Adresse und Indirekt in dieser Zeile ein Wert ausgelesen.
Das Ganze war ursprünglich eine Excel- Tabelle, der frühere Bearbeiter ist aber mit Excel an dieser Stelle anscheinend auch nicht weitergekommen.
Mit einem Makro wäre die Zeichenkettenbearbeitung grundsätzlich kein Problem, mit Formel aber leichter zu handhaben. Es wäre aber kein Problem, eine Formel in mehrere übersichtlichere Teilformeln und Zellen aufzulösen.
Mein Problem ist z.B.: Suchen sucht immer von links nach rechts. Gibts es auch eine Möglichkeit, um von rechts nach links zu suchen? Kann man einen String mit Formel spiegeln? Kann man mit einer Formel die Anzahl der "." ermitteln? Wie kann man prüfen, ob ein Buchstabe enthalten ist? Kann man Fehler abfangen? Ich suche über eine Suchenschachtelung nach dem 3. Punkt, in dieser Zeichenkette gibt es aber nur 2 Punkte. Das ergibt eine Fehlermeldung, in Basic würde 0 ausgegeben, womit ich weiterarbeiten könnte.
Eine ganze Menge Fragen. Oder kann man das doch nur mit einem makro lösen?
Moritz
mein Problem ist es nicht, daß ich einmalig in einer Spalte die Buchstaben lösche. Die Zeichenketten sind als Projektnummern in einer Tabelle vorgeben. Die Ziffern hinter dem 2. Punkt (bis zum 3. Punkt bzw. zum Ende der Kette, wenn es keinen 3. Punkt gibt) bezeichnen eine Zeile in einer anderen Tabelle (die auch nicht von mir ist). Wenn dort also eine 12 steht, wird über Adresse und Indirekt in dieser Zeile ein Wert ausgelesen.
Das Ganze war ursprünglich eine Excel- Tabelle, der frühere Bearbeiter ist aber mit Excel an dieser Stelle anscheinend auch nicht weitergekommen.
Mit einem Makro wäre die Zeichenkettenbearbeitung grundsätzlich kein Problem, mit Formel aber leichter zu handhaben. Es wäre aber kein Problem, eine Formel in mehrere übersichtlichere Teilformeln und Zellen aufzulösen.
Mein Problem ist z.B.: Suchen sucht immer von links nach rechts. Gibts es auch eine Möglichkeit, um von rechts nach links zu suchen? Kann man einen String mit Formel spiegeln? Kann man mit einer Formel die Anzahl der "." ermitteln? Wie kann man prüfen, ob ein Buchstabe enthalten ist? Kann man Fehler abfangen? Ich suche über eine Suchenschachtelung nach dem 3. Punkt, in dieser Zeichenkette gibt es aber nur 2 Punkte. Das ergibt eine Fehlermeldung, in Basic würde 0 ausgegeben, womit ich weiterarbeiten könnte.
Eine ganze Menge Fragen. Oder kann man das doch nur mit einem makro lösen?
Moritz
Es gibt noch eine Chance, das mit einer Formel zu lösen. Aber die Struktur muss irgendwie begrenzbar sein:
nehmen wir an, die Zeichenkette zwischen dem 2. und 3. Punkt enthält sowohl Zahlen wie auch Buchstaben:
nehmen wir an, die Zeichenkette zwischen dem 2. und 3. Punkt enthält sowohl Zahlen wie auch Buchstaben:
- steht der Buchstabe immer nur am Ende?
also nur so: 4a, 34H, oder welche Buchstaben-Einschlüsse sind möglich? - wenn Buchstaben vorkommen, ist das immer nur einer oder vieviele können es maximal sein?
Die gute Nachricht: es geht. Kleines Aber:
es geht nicht ohne Zwischenspalte. Jedenfalls nicht bei meinem Formel-Vorschlag. Nicht wegen der Formel-Logik, sondern weil die Formel länger wird, als OO bereit ist, zuzulassen.
Die Formel-Logik:
Aber man kann die einzelnen Formelteile als Aliase speichern und erhält so wieder einen übersichtlichen Aufbau.
Eine Formel speichert man via: Einfügen/Namen/Festlegen. Die Namen sind frei wählbar, ausser, dass man keine OO-Funktionsnamen nehmen kann. Die Formel schreibt oder kopiert man dann in das Feld "zugeordnet zu".
Was man beachten muss: man kann keine Zellbezüge im gewohnten Stil verwenden, also sowas wie =A2 bzw. WENN(A2=....
Man muss eine Schreibweise finden, die absolut allgemeingültig ist.
Im vorliegenden Beispiel steht die Formel-Spalte rechts neben der Spalte mit dem Ausgangswert. Die Formel bezieht sich also auf "die Zelle links von mir". Auf den Inhalt der Zelle, die um 1 Spalte nach links zur Formel-Spalte verschoben ist, greift man so zu:
=INDIREKT(ADRESSE(ZEILE();SPALTE()-1))
Steht die Ausgangszelle weiter links, muss man halt mehr als 1 Spalte abziehen:
=INDIREKT(ADRESSE(ZEILE();SPALTE()-3))
Das Ganze gibts hier zum Download. Bitte Link mit rechter Maustaste anklicken und "Ziel speichern unter"
Schöne Grüsse
es geht nicht ohne Zwischenspalte. Jedenfalls nicht bei meinem Formel-Vorschlag. Nicht wegen der Formel-Logik, sondern weil die Formel länger wird, als OO bereit ist, zuzulassen.
Die Formel-Logik:
- Man braucht zunächst einen verschachtelten FINDEN-Befehl, um den zweiten und dritten Punkt zu finden:
Punkt 2: FINDEN(".";A1;FINDEN(".";A1)+1)
Punkt 3: FINDEN(".";A1;FINDEN(".";A1;FINDEN(".";A1)+1)+1) - Wenn die Suche nach dem dritten Punkt erfolglos bleibt, liefert OO einen Fehlerwert, den kann man in eine WENN-Abfrage einbauen:
WENN(ISTFEHLER(FINDEN.... - Wenn es keinen dritten Punkt gibt, befindet sich der zu bearbeitende Teil-String zwischen dem zweiten Punkt und dem Ende, andernfalls zwischen dem zweiten und dritten Punkt.
- Mit WERT() wird aus dem Teilstring eine Zahl
- Wenn der Teilstring Buchstaben enthält, ergibt WERT einen Fehler. Man muss also zunächst prüfen, ob die WERT-Funktion überhaupt eine Zahl liefern kann:
WENN(ISTZAHL(WERT(B1));WERT(B1)) - Wenn das Ergebnis keine Zahl ist, schneidet man mit TEIL einfach die letzte Stelle ab
Aber man kann die einzelnen Formelteile als Aliase speichern und erhält so wieder einen übersichtlichen Aufbau.
Eine Formel speichert man via: Einfügen/Namen/Festlegen. Die Namen sind frei wählbar, ausser, dass man keine OO-Funktionsnamen nehmen kann. Die Formel schreibt oder kopiert man dann in das Feld "zugeordnet zu".
Was man beachten muss: man kann keine Zellbezüge im gewohnten Stil verwenden, also sowas wie =A2 bzw. WENN(A2=....
Man muss eine Schreibweise finden, die absolut allgemeingültig ist.
Im vorliegenden Beispiel steht die Formel-Spalte rechts neben der Spalte mit dem Ausgangswert. Die Formel bezieht sich also auf "die Zelle links von mir". Auf den Inhalt der Zelle, die um 1 Spalte nach links zur Formel-Spalte verschoben ist, greift man so zu:
=INDIREKT(ADRESSE(ZEILE();SPALTE()-1))
Steht die Ausgangszelle weiter links, muss man halt mehr als 1 Spalte abziehen:
=INDIREKT(ADRESSE(ZEILE();SPALTE()-3))
Das Ganze gibts hier zum Download. Bitte Link mit rechter Maustaste anklicken und "Ziel speichern unter"
Schöne Grüsse
Danke, Danke
Das ist genau das, was ich gesucht habe. Vielen Dank dafür. Und wieviel Stunden hast Du dafür geopfert? Sicherlich ist Deine Lösung aber auch für andere eine große Hilfe. Nochmals vielen Dank. Moritz
Ich habe jetzt mal noch getestet, wie es aussieht, wenn man keine Alias-Namen verwendet, sondern die Formel direkt eingibt. Da man dabei mit einer einfachen Zelladresse (A11) arbeiten kann, statt mit dem INDIREKT-String, wird die Formel (intern) um soviel Kürzer als die Alias-Version, dass man alles in eine Formel packen kann, ohne Zwischenspalten:
Beispiel 1
Beispiel 2
Aber ich denke es ist eindeutig: solche Formeln sind der absolute Horror
PS: die Beispiele enthalten blanks, damit die Zeilen hier umgebrochen werden können, die müsste man rausnehmen, wenn man die Formel kopieren wollte.
Beispiel 1
Code: Alles auswählen
=WENN(ISTZAHL(WERT(WENN(ISTFEHLER(FINDEN(".";A11;FINDEN(".";A11;FINDEN(".";A11)+1)+1)); TEIL(A11;FINDEN(".";A11;FINDEN(".";A11)+1)+1;LÄNGE(A11));TEIL(A11;FINDEN(".";A11;FINDEN(".";A11)+1)+1; LÄNGE(A11)-FINDEN(".";A11;FINDEN(".";A11;FINDEN(".";A11)+1)+1)))));WERT(WENN(ISTFEHLER(FINDEN(".";A11; FINDEN(".";A11;FINDEN(".";A11)+1)+1));TEIL(A11;FINDEN(".";A11;FINDEN(".";A11)+1)+1;LÄNGE(A11));TEIL(A11; FINDEN(".";A11;FINDEN(".";A11)+1)+1;LÄNGE(A11)-FINDEN(".";A11;FINDEN(".";A11;FINDEN(".";A11)+1)+1)))); WERT(TEIL(WENN(ISTFEHLER(FINDEN(".";A11;FINDEN(".";A11;FINDEN(".";A11)+1)+1));TEIL(A11;FINDEN(".";A11; FINDEN(".";A11)+1)+1;LÄNGE(A11));TEIL(A11;FINDEN(".";A11;FINDEN(".";A11)+1)+1;LÄNGE(A11)-FINDEN(".";A11; FINDEN(".";A11;FINDEN(".";A11)+1)+1)));1;LÄNGE(WENN(ISTFEHLER(FINDEN(".";A11;FINDEN(".";A11; FINDEN(".";A11)+1)+1));TEIL(A11;FINDEN(".";A11;FINDEN(".";A11)+1)+1;LÄNGE(A11));TEIL(A11;FINDEN(".";A11; FINDEN(".";A11)+1)+1;LÄNGE(A11)-FINDEN(".";A11;FINDEN(".";A11;FINDEN(".";A11)+1)+1))))-1)))Code: Alles auswählen
=WAHL(WENN(ISTZAHL(WERT(WENN(ISTFEHLER(FINDEN(".";A11;FINDEN(".";A11;FINDEN(".";A11)+1)+1)); TEIL(A11;FINDEN(".";A11;FINDEN(".";A11)+1)+1;LÄNGE(A11));TEIL(A11;FINDEN(".";A11;FINDEN(".";A11)+1)+1; LÄNGE(A11)-FINDEN(".";A11;FINDEN(".";A11;FINDEN(".";A11)+1)+1)))));1;2);WERT(WENN(ISTFEHLER(FINDEN(".";A11; FINDEN(".";A11;FINDEN(".";A11)+1)+1));TEIL(A11;FINDEN(".";A11;FINDEN(".";A11)+1)+1;LÄNGE(A11));TEIL(A11; FINDEN(".";A11;FINDEN(".";A11)+1)+1;LÄNGE(A11)-FINDEN(".";A11;FINDEN(".";A11;FINDEN(".";A11)+1)+1)))); WERT(TEIL(WENN(ISTFEHLER(FINDEN(".";A11;FINDEN(".";A11;FINDEN(".";A11)+1)+1));TEIL(A11;FINDEN(".";A11; FINDEN(".";A11)+1)+1;LÄNGE(A11));TEIL(A11;FINDEN(".";A11;FINDEN(".";A11)+1)+1;LÄNGE(A11)-FINDEN(".";A11; FINDEN(".";A11;FINDEN(".";A11)+1)+1)));1;LÄNGE(WENN(ISTFEHLER(FINDEN(".";A11;FINDEN(".";A11; FINDEN(".";A11)+1)+1));TEIL(A11;FINDEN(".";A11;FINDEN(".";A11)+1)+1;LÄNGE(A11));TEIL(A11;FINDEN(".";A11; FINDEN(".";A11)+1)+1;LÄNGE(A11)-FINDEN(".";A11;FINDEN(".";A11;FINDEN(".";A11)+1)+1))))-1)))PS: die Beispiele enthalten blanks, damit die Zeilen hier umgebrochen werden können, die müsste man rausnehmen, wenn man die Formel kopieren wollte.
kleine Korrektur
Laß mal, die Variante mit den benannten Formeln ist wesentlich übersichtlicher. Vor allem kannte ich diese Möglichkeit noch gar nicht. Wieder was gelernt.
In "bisPunkt3" ist aber ein kleiner Fehler. Richtig muß diese Formel lauten: TEIL(Importnummer;Punkt2+1;Punkt3-Punkt2-1).
Nochmals vielen Dank
Moritz
In "bisPunkt3" ist aber ein kleiner Fehler. Richtig muß diese Formel lauten: TEIL(Importnummer;Punkt2+1;Punkt3-Punkt2-1).
Nochmals vielen Dank
Moritz
Re: kleine Korrektur
Stimmt. Und da ist man dann dankbar für kleine Teilformeln.moritz hat geschrieben:TEIL(Importnummer;Punkt2+1;Punkt3-Punkt2-1).
In BisEnde ist übrigens auch noch ein Fehler, der sich aber nicht auswirkt. Die Teil-String-Länge wäre ja richtig nicht die gesamte LÄNGE
Schönen Sonntag!
PS: hab die Download-Datei korrigiert.