Bestimmten Text (Referenznummer) aus Tabellenzellen extrahieren

[hylli]

Aus einer Umsatztabelle verschiedener Konten, möchte ich jeweils die Referenznummer aus einem Verwendungszweck extrahieren und in eine separate Zelle schreiben.

Bei einem Konto sieht das mehrzeilig ungefähr so aus:

Einr. SCT Sammel
KREF: KUNDEN-XXX-REFERENZ:
ABCDEF61XXXCGF7KBRIVUAAAAFUOYXBYWC3
SVWZ: SEPA VERWENDUNGSZWECK:
NUMBER OF TRX 1

Beim anderen Konto hingegen in einer Zeile...

Weiterleitungsauftrag ABCDEF61XXXCGF7KBRIVUAAAAFUOYXBYWC3 an irgendwas..

...oder auch mal so ein Konstrukt...

Erhaltene Zahlungen vom XYZ, MessageIdentificatioABCDEF61XXXCGF7KBRIVUAAAAFUOYXBYWC3

...bzw. ähnlich so:

Erhaltene Gutschriften vom Clearer, MessageIdentification=ABCDEF61XXXCGF7KBRIVUAAAAFUOYXBYWC3

Extrahieren möchte ich jeweils die hier fett gedruckte Referenznummer, die von der Länge her leider auch unterschiedlich ausfallen kann.

Die Referenznummer beginnt grundsätzlich z.B. wie hier mit "ABCDEF61XXX".

Bei den einzeiligen Verwendungszwecken kommt entweder mindestens ein Leerzeichen hinter der Referenznummer, manchmal endet die Zelle aber auch mit der Referenznummer.

Gibt es Funktionen, mit denen man die Referenznummer jeweils sauber extrahieren kann?

Beispielsweise: Suche nach "ABCDEF61XXX" bis zum nächsten Leerzeichen/Ende des Strings und schneide diesen Teil aus?

Ziel der Aktion:
Neben dem gleichen B etrag möchte ich noch die Referenznummer der Umsätze vergleichen, um die Konten besser abstimmen zu können.

Hylli

[F3K Total]

Moin,
ja geht, angenommen dein Verwendungszweck steht in A2, dann errechnest du die Position des Anfangs der Referenznummer in B2 mit:

Code: Alles auswählen

=FINDEN("ABCDEF61XXX";A2;1)

Die Position des Endes dann in C2 mit

Code: Alles auswählen

=FINDEN(" ";A2;B2)

->Finde das nächste Leerzeichen, dies gibt einen Fehler wenn keines gefunden wird, den du in D2 mit

Code: Alles auswählen

=WENN(ISTFEHLER(C2);999;C2)

abfängst. D.h. diese Formel funktioniert nur bis zu einer Länge des Verwendungszwecks von 999 Zeichen. In E2 kannst du nun die Referenznummer ermitteln:

Code: Alles auswählen

=TEIL(A2;B2;D2-B2)

Setzt du nun die Formeln ineinander ein, kannst du alles mit dieser einen Formel in F2 erledigen:

Code: Alles auswählen

=TEIL(A2;FINDEN("ABCDEF61XXX";A2;1);WENN(ISTFEHLER(FINDEN(" ";A2;FINDEN("ABCDEF61XXX";A2;1)));999;FINDEN(" ";A2;FINDEN("ABCDEF61XXX";A2;1)))-FINDEN("ABCDEF61XXX";A2;1))

Beispiel anbei.
Gruß R

[hylli]

Hi,

arbeitet für die einzeiligen Verwendungszwecke schon einmal perfekt, vielen Dank!

Wie muss ich die Formel anpassen für den mehrzeiligen Verwendungszweck.

Da erhalte ich als Ergebnis aktuell:

ABCDEF61XXXCGF7KBRIVUAAAAFUOYXBYWC3
SVWZ:

Das dürfte daran liegen, dass erst nach SVWZ: ein Leerzeichen kommt, aber zwischen der Referenz und SVWZ: eben noch ein Zeilensprung ist, welcher in dem Fall das "Trennzeichen" wäre.

Edit:
Vergiss es! Für den Zeilenumbruch, muss Deine Formel wie folgt angepasst werden:

Code: Alles auswählen

=TEIL(A2;FINDEN("ABCDEF61XXX";A2;1);WENN(ISTFEHLER(FINDEN(ZEICHEN(10);A2;FINDEN("ABCDEF61XXX";A2;1)));999;FINDEN(ZEICHEN(10);A2;FINDEN("ABCDEF61XXX";A2;1)))-FINDEN("ABCDEF61XXX";A2;1))

Works like a charm!

Hylli

[F3K Total]

Tja,
darum ist es besser ein Beispieldokument hochzuladen, die Zeilenumbrüche sind beim Einfügen in Calc nicht mitgekommen ...

[hylli]

Ich hätte noch eine ergänzende Frage. Im Anhang sieht man, dass hier die Referenzen aus dem Buchstaben B + derzeit einer 7stelligen Zahl besteht. Später dürfte die 7stellige Zahl wahrscheinlich mal 8, 9 oder auch 10+-stellig werden.

Wie kann ich hier die Referenz aus dem Verwendungszweck sinnvoll auslesen?

Hylli

[F3K Total]

Bitteschön.
Die Formeln einzusetzen schaffst du alleine?
Der Trick liegt in dieser Formel mit regulärem Ausdruck, dazu muss in Extras/Optionen.../LibreOfficeCalc/Berechnen "reguläre Ausdrücke in Formeln zulassen" angehakt sein:

Code: Alles auswählen

=SUCHEN("B"&"[:digit:]{7,}";A2;1)

D.h.

• Suche in Zelle A2, ab Stelle 1
• "B"& -> ein B verkettet mit ...
• "[:digit:]{7,}" -> ... Zahlen, sieben oder mehr hintereinander (Komma hinter der 7)

Gruß R

[hylli]

Perfekt, vielen Dank!

Ich wusste, dass da wohl SUCHEN() die richtige Variante ist, nur kann ich mit den regulären Ausdrücken noch nicht so wirklich umgehen.

Hylli