Bestimmte Zahl aus "Chaos-Zelle" isolieren

[julian1xls]

Hallo zusammen, 

ich versuche aktuell eine Excel Datei zum Abgleich von Daten zu erstellen. (Bankdaten)
Hierfür benötige ich die Rechnungsnummer isoliert. Problem dabei: die Daten der Verwendungszwecke in denen die Rechnungsnummern auftauchen ist total chaotisch. 

Ich habe eine Beispieldatei angehängt. Hier sieht man wie die Daten aussehen.
Theoretisch bräuchte ich eine Funktion die in jeder Zeile die 5stelligen Zahlen (Rechnungsnummern) isoliert. 

Ich denke, dass Excel bzw. ich hier an Grenzen stößt. 

Vielleicht hat jemand eine Idee 

Vielen Dank schonmal für euren Input

[Fennek]

Hallo,

mit "RegEx" sollte dies recht einfach gehen, dies würde zumindest Grundkenntnisse in VBA erfordern.


Mfg

Code:

Sub F_en()
Dim RegEx As Object, RR As Object

Set RegEx = CreateObject("vbscript.regexp")
RegEx.Pattern = "\d{5}"

For i = 2 To Cells(Rows.Count, 1).End(xlUp).Row
    Set RR = RegEx.Execute(Cells(i, 1))
    Cells(i, 3) = RR(0)
Next i
End Sub

[BoskoBiati]

Hi,

wenn die Rechnungsnummer immer mit 2 anfängt geht auch das:

Code:

=AGGREGAT(15;6;TEIL(WECHSELN(A2&"a";" ";"");SPALTE(INDEX(1:1;SUCHEN("2";WECHSELN(A2&"a";" ";""))):INDEX(1:1;LÄNGE(WECHSELN(A2&"a";" ";""))-4));5)/ISTZAHL(--TEIL(WECHSELN(A2&"a";" ";"");SPALTE(INDEX(1:1;SUCHEN("2";WECHSELN(A2&"a";" ";""))):INDEX(1:1;LÄNGE(WECHSELN(A2&"a";" ";""))-4));5))/NICHT(ISTZAHL(--TEIL(WECHSELN(A2&"a";" ";"");SPALTE(INDEX(1:1;SUCHEN("2";WECHSELN(A2&"a";" ";""))):INDEX(1:1;LÄNGE(WECHSELN(A2&"a";" ";""))-4));6)));1)

Es gibt wahrscheinlich auch einfachere Lösungen.

[RPP63]

Off topic!
Goggle Sheets und LibreOffice Calc kennen die Regenechsen als normale Zellformeln:
Google: =REGEXEXTRACT(A1;"\d{5}")
Libre: =REGAUS(A1;"\d{5}")

Ich schaue immer bei jedem 365er Update, wann dies endlich auch in Excel implementiert wird.

Gruß Ralf

[00202]

Hallo Ralf,  

gibt es in Google oder Libre "Negative oder Positive LookBehind"? Diese sind in VBA REGEX nicht implementiert. Kann man aber sehr häufig brauchen. Ist z. B. noch eine 6stellige (oder mehrstellige) Zahl vor der Rechnungsnummer versagt "\d{5}". Hier muss man dann tricksen bzw. die Matches durchloopen, oder auch Gruppen bilden.

Also sowas funktioniert in VBA REGEX nicht:

Code:

(?<!\d)\d{5}(?!\d)

Sowas würde gehen:

Code:

\b\d{5}\b

Dann darf aber VOR der Rechnungsnummer ohne Leerzeichen kein Buchstabe kommen. Ist in den Beispielen aber mit drin.

Wenn REGEX in Excel implementiert wird, bin ich für eine Erweiterung der Funktionen.

In https://regex101.com lässt sich das alles testen.

[schauan]

Hallöchen,

ein Problem bei den Überweisungen ist eben der "Wildwuchs" beim Ausfüllen. Man kann viel per Formel abdecken, aber eben nicht alles. Man kann nicht sicher sein, dass das Auffinden einer Nummer auch die Rechnungsnummer ist. Könnte ja auch eine 5stellige Kundennummer sein oder die Postleitzahl. Man muss also in jedem Fall das Ergebnis 100% prüfen.

Eventuell macht man es anders rum und es reicht ein Abgleich mit einer Rechnungsübersicht. Man weiß ja, was man für Rechnungen gestellt hat, sucht in den Strings nach selbiger und markiert die Datensätze. Bei Bedarf schreibt man noch die Nummer daneben. Muss man anschließend aber auch zu 100% prüfen.

Problematisch wird dann auch ein Wechsel zu 6stelligen Rechnungsnummern. Ist dann wohl zum Glück nur temporär, bis alle 5stelligen bezahlt sind.
Man könnte dem aus dem Weg gehen, indem man das Jahr davor schreibt, also dann 2 Stellen für das Jahr und dann eine ausreichende Anzahl, z.B. 4 Stellen für die Nummern 1 bis 9999. als0 230001 bis 239999. 2024 ging es dann mit 240001 weiter. Rechnungsnummern sollten zwar fortlaufend sein, eine "Lücke" am Jahresende ist aber begründbar.

[Gast 123]

Hallo

mit VBA kann man meines Erachtens das Chaos am einfachsten in den Griff bekommen.
Die Stellenzahl kann in Zelle C2 auf 5-7 Stellen festgelegt werden. Sonst kommt MsgBox Meldung.
Leider eine alte Excel 2003 Datei. Das Makro kannst du ja in die Originaldatei kopieren.

mfg Gast 123

[{Boris}]

Hi,

per Formel ein Alternativvorschlag:

=WENNFEHLER(--TEIL(A2;MIN(WENN(ISTZAHL(--TEIL(WECHSELN(WECHSELN(A2;" ";"#");".";"#");SEQUENZ(300);5));SEQUENZ(300)));5);"nix gefunden")

Sucht immer die erste vorkommende 5-stellige Zahl. Falls die erste Zahl aber z.B. 6-stellig sein sollte, wird daraus die 5-stellige Zahl ermittelt.
Datumsangaben (sind ja auch Zahlen) sind jedoch berücksichtigt - die werden mit WECHSELN "entdatiert"

[julian1xls]

Danke an alle für eure Unterstützung und die Mühe. Die Formeln, funktionieren größtenteils sehr gut. Am höchsten war die Trefferquote aber bei der VBA Datei von Gast 123. 

---

Hallo

mit VBA kann man meines Erachtens das Chaos am einfachsten in den Griff bekommen.
Die Stellenzahl kann in Zelle C2 auf 5-7 Stellen festgelegt werden. Sonst kommt MsgBox Meldung.
Leider eine alte Excel 2003 Datei. Das Makro kannst du ja in die Originaldatei kopieren.

mfg Gast 123

Vielen Dank für deine Mühe. Die Lösung finde ich von allen Vorschlägen am zuverlässigsten. Ich habe keine VBA Kenntnisse und werde deshalb deine Datei verwenden. 

[ws-53]

Jetzt auch eine mit Power Query erstellte Lösung, die auch mehrere 5-stellige Nummern erkennt und extrahiert.