6 stellige Zahlen aus Zelle extrahieren

Forumthread: 6 stellige Zahlen aus Zelle extrahieren

22.06.2021 15:40:08

Karsten

Hallo Leute,
die Damen und Herren unserer Buchhaltung sind bei ihren Buchungstexten leider etwas inkonsequent.
Ich möchte aus einem Buchungstext eine 6 stellige Zahl extrahieren.
Da Problem ist, dass diese Zahl nicht immer an der gleichen Stelle im Text steht und nicht die Einzige Zahl in der Zelle ist.
Wie gehe ich das Problem an?
590 Schulz+Sohn - Störung v Aufzg 364046 am 26.08
590 Schulz+SOHN - Störung v Afzg 364051 am 03.09.
590 Schulz+SOHN - Störung v Aufzg 364051 am 05.10
590 Schulz+Sohn - Störung v Aufzg 364046 am 16.10
590 Schulz+SOHN - Störng v Aufzg 364049, 06.11.18
590 Schulz+SOHN - Störung v Afzg 364051, 06.11.18

Beiträge zum Forumthread

Beiträge zu diesem Forumthread

Betreff

Datum

Anwender

AW: 6 stellige Zahlen aus Zelle extrahieren

22.06.2021 16:20:31

Karsten

Hallo steve1da,
Danke für den Link. Ich hatte vorher nach 7 stelligen zahlen gesucht und nichts gefunden.
Kannst Du mir bei der Formel selbst auch helfen?
Die von neopa C (paneo) präsentierte Lösung


=VERWEIS(9^9;--TEIL(WECHSELN(A1;".";"#")&"!";SPALTE(1:1);6))

zeigt mit in Zeile 1 nur die letzten 5 Stellen der 6 stelligen Zahl an.
Bei Änderung der Formel auf


=VERWEIS(9^9;--TEIL(WECHSELN(A1;".";"#")&"!";SPALTE(1:1);7))

wird dann die 6stellige Zahl angezeigt.
Bei den letzten beiden Zeilen meines o.a. Bespieles werden allerdings wiederum nur die letzten 5 Stellen angezeigt. bei Änderung auf


=VERWEIS(9^9;--TEIL(WECHSELN(A1;".";"#")&"!";SPALTE(1:1);8))

stehen dann die 6 Stellen wieder da. Ich vermute, dass das mit dem nachstehenden Komma zu tun hat?
Wenn ich die Formel in allen Zeilen auf 8 lasse, bekomme ich zumindestens in allen Zeilen meine 6 stellige Zahl

AW: 6 stellige Zahlen aus Zelle extrahieren

22.06.2021 16:22:06


=TEIL(A1;VERWEIS(1;1/ISTFEHL(--TEIL("!"&WECHSELN(A1;".";"#")&"!";SPALTE(1:1);1)) /ISTZAHL(--TEIL("!"&WECHSELN(A1;".";"#")&"!";SPALTE(1:1)+1;6))/ISTFEHL(--TEIL("!"&WECHSELN(A1;"."; "#")&"!";SPALTE(1:1)+7;1));SPALTE(1:1));6)

Die Formel aus dem Link hat bei mir gut funktioniert.

AW: 6 stellige Zahlen aus Zelle extrahieren

22.06.2021 16:07:33

Daniel

Hi
kommt darauf an, was sonst noch so in der Zelle steht.
für die gezeigten Beispiele reicht:


=AGGREGAT(14;6;WERT(TEIL(A1;ZEILE($A$1:$A$100);6));1)

Zeile($A$1:$A$100) erzeugt hier nur die Zählnummer, mit der du durch den Text gehst und die Zahl suchst.
der Zellbereich muss immer mit 1 beginnen und die Größe richtet sich nach der maximal möglichen Länge des Textes (dh die letzte Zeile darf größer sein, aber möglichst nicht geringer.
die Aufzugnummer muss die größte Zahl im Text sein.
Das Datum sollte kein Problem sein, da dies maximal 5stellig sein kann.
solltest du aber Aufzugnummern haben, die eine oder mehrere führende Nullen haben, könnte das relevant sein, dann muss man das Datum als mögliche Zahl ausschließen:


=AGGREGAT(14;6;WERT(TEIL(Wechseln(A1;".";"x");ZEILE($A$1:$A$100);6));1)

gruß Daniel

AW: 6 stellige Zahlen aus Zelle extrahieren

22.06.2021 16:33:09

Karsten

Danke Dir das funktioniert super.
Der Tip mit dem Datum ist auch sehr gut.
Danke auch an steve1da. Aber hier ist die Aggregatfunktion doch einfacher.
karsten

AW: für Deine Beispieldatenwerte ...

22.06.2021 17:46:39

neopa

Hallo Karsten,
... würde einfach einfach nur =TEIL(A2;FINDEN("zg ";A2)+3;7)+0 ausreichen, welches auch kein Problem mit führenden 0en in der sechsstelligen Zahl hätte. Allgemeiner und damit sicherer ist natürlich der Formelvorschlag von Daniel.
Gruß Werner
.. , - ...

AW: für Deine Beispieldatenwerte ...

22.06.2021 20:54:28

Karsten

Hallo Werner,
danke für die Rückmeldung.
nein das würde nicht zum Erfolg führen, da die Buchhalter wirklich willkürlich abkürzen.

=TEIL(A1;FINDEN("zg";A1)+3;6)

22.06.2021 17:47:29

Infobox / Tutorial

Schritt-für-Schritt-Anleitung

Öffne deine Excel-Datei und gehe zu der Zelle, die den Text mit der 6-stelligen Zahl enthält.
Verwende die folgende Formel, um die 6-stellige Zahl aus der Zelle zu extrahieren:
```
=AGGREGAT(14;6;WERT(TEIL(A1;ZEILE($A$1:$A$100);6));1)
```
Hierbei wird die Zelle A1 als Beispiel genutzt. Ersetze A1 durch die tatsächliche Zelle, die die Zahl enthält.
Drücke Enter, um die Formel anzuwenden. Du solltest nun die erste 6-stellige Zahl aus der Zelle extrahiert sehen.
Falls erforderlich, passe die Formel an, um führende Nullen auszuschließen oder um sicherzustellen, dass nur die relevanten Zahlen extrahiert werden.

Häufige Fehler und Lösungen

Fehler: Formel funktioniert nicht wie erwartet
- Lösung: Überprüfe, ob die Zelle tatsächlich eine 6-stellige Zahl enthält und dass die Formel korrekt auf die richtige Zelle verweist.
Fehler: Ungewollte Werte werden extrahiert
- Lösung: Stelle sicher, dass du die Formel so anpasst, dass sie nur auf die Zahlen in dem gewünschten Format (z.B. 6-stellig) angewendet wird.

Alternative Methoden

Eine weitere Möglichkeit, um 6-stellige Zahlen aus einer Zelle zu extrahieren, ist die Verwendung der VERWEIS-Funktion in Kombination mit TEIL:

=VERWEIS(9^9;--TEIL(WECHSELN(A1;".";"#")&"!";SPALTE(1:1);6))

Diese Methode kann hilfreich sein, wenn die 6-stellige Zahl nicht immer an derselben Stelle im Text steht.

Praktische Beispiele

Beispiel 1: Angenommen, in Zelle A1 steht folgender Text: 590 Schulz+Sohn - Störung v Aufzg 364046 am 26.08. Mit der oben genannten Formel erhältst du 364046.
Beispiel 2: Wenn in Zelle A2 der Text 590 Schulz+SOHN - Störung v Afzg 364051 am 03.09. steht, kannst du ebenfalls die gleiche Formel verwenden, um die 6-stellige Zahl 364051 zu extrahieren.

Tipps für Profis

Nutze die TEXT-Funktion: Wenn du sicherstellen möchtest, dass die extrahierte Zahl immer in einem bestimmten Format angezeigt wird, kannst du die TEXT-Funktion verwenden, um die Formatierung anzupassen.
Überprüfe die Eingabedaten: Achte darauf, dass die Daten in den Zellen gut formatiert sind. Wenn du häufig Daten importierst, stelle sicher, dass die Zahlformate konsistent sind.

FAQ: Häufige Fragen

1. Wie kann ich sicherstellen, dass nur 6-stellige Zahlen extrahiert werden?
Du kannst die Formel anpassen, um nur Zahlen mit genau 6 Stellen zu betrachten, indem du die LÄNGE-Funktion in Kombination mit anderen Bedingungen verwendest.

2. Funktioniert das auch bei 7-stelligen oder 8-stelligen Zahlen?
Ja, du kannst die Formeln entsprechend anpassen, indem du die Anzahl der Stellen in der TEIL-Funktion änderst, um auch 7-stellige oder 8-stellige Zahlen zu extrahieren.

Beliebteste Forumthreads (12 Monate)

6 stellige Zahlen aus Zelle extrahieren

Forumthread: 6 stellige Zahlen aus Zelle extrahieren

8 Beiträge zum Forumthread Beiträge zu diesem Forumthread

Forumthreads zu verwandten Themen

VBA Beispiele zu diesem und ähnlichen Themen

Infobox / Tutorial

6-stellige Zahlen aus Zelle extrahieren

Schritt-für-Schritt-Anleitung

Häufige Fehler und Lösungen

Alternative Methoden

Praktische Beispiele

Tipps für Profis

FAQ: Häufige Fragen

Beliebteste Forumthreads (12 Monate)

8
Beiträge zum Forumthread

Beiträge zu diesem Forumthread