BITTE helfen Sie uns HEUTE mit einer SPENDE
Helfen Sie das LibreOffice Forum zu erhalten!

❤️ DANKE >><< DANKE ❤️

> KEINE WERBUNG FÜR REGISTRIERTE BENUTZER!<
Ihre Spende wird für die Deckung der laufenden Kosten sowie den Erhalt und Ausbau 🌱 des LibreOffice Forums verwendet.
🤗 Als Dankeschön werden Sie im Forum als LO-SUPPORTER gekennzeichnet. 🤗

Bestimmten Text (Referenznummer) aus Tabellenzellen extrahieren

CALC ist die Tabellenkalkulation, die Sie immer wollten.
Antworten
hylli
Beiträge: 290
Registriert: Sa 19. Mär 2011, 19:00

Bestimmten Text (Referenznummer) aus Tabellenzellen extrahieren

Beitrag von hylli » Do 28. Nov 2019, 16:18

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
Geschäftlich: LibreOffice 7.2.x(als Citrix-Anwendung)
Privat: LibreOffice 7.3.x unter Linux Mint Cinnamon 64bit (jeweils aktuelle Version)

F3K Total
Beiträge: 2412
Registriert: So 10. Apr 2011, 10:10

Re: Bestimmten Text (Referenznummer) aus Tabellenzellen extrahieren

Beitrag von F3K Total » Do 28. Nov 2019, 16:57

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
Dateianhänge
Extract_Refnumber.ods
(17.18 KiB) 158-mal heruntergeladen
Windows 10: AOO, LO Linux Mint: AOO, LO

hylli
Beiträge: 290
Registriert: Sa 19. Mär 2011, 19:00

Re: Bestimmten Text (Referenznummer) aus Tabellenzellen extrahieren

Beitrag von hylli » Fr 29. Nov 2019, 08:25

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
Geschäftlich: LibreOffice 7.2.x(als Citrix-Anwendung)
Privat: LibreOffice 7.3.x unter Linux Mint Cinnamon 64bit (jeweils aktuelle Version)

F3K Total
Beiträge: 2412
Registriert: So 10. Apr 2011, 10:10

Re: Bestimmten Text (Referenznummer) aus Tabellenzellen extrahieren

Beitrag von F3K Total » Fr 29. Nov 2019, 13:59

Tja,
darum ist es besser ein Beispieldokument hochzuladen, die Zeilenumbrüche sind beim Einfügen in Calc nicht mitgekommen ...
Windows 10: AOO, LO Linux Mint: AOO, LO

hylli
Beiträge: 290
Registriert: Sa 19. Mär 2011, 19:00

Re: Bestimmten Text (Referenznummer) aus Tabellenzellen extrahieren

Beitrag von hylli » Mo 9. Dez 2019, 15:38

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
Dateianhänge
Referenz_ermitteln.ods
(12.79 KiB) 134-mal heruntergeladen
Geschäftlich: LibreOffice 7.2.x(als Citrix-Anwendung)
Privat: LibreOffice 7.3.x unter Linux Mint Cinnamon 64bit (jeweils aktuelle Version)

F3K Total
Beiträge: 2412
Registriert: So 10. Apr 2011, 10:10

Re: Bestimmten Text (Referenznummer) aus Tabellenzellen extrahieren

Beitrag von F3K Total » Mo 9. Dez 2019, 18:10

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
Dateianhänge
Referenz_ermitteln.ods
(12.7 KiB) 131-mal heruntergeladen
Windows 10: AOO, LO Linux Mint: AOO, LO

hylli
Beiträge: 290
Registriert: Sa 19. Mär 2011, 19:00

Re: Bestimmten Text (Referenznummer) aus Tabellenzellen extrahieren

Beitrag von hylli » Mo 9. Dez 2019, 20:04

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
Geschäftlich: LibreOffice 7.2.x(als Citrix-Anwendung)
Privat: LibreOffice 7.3.x unter Linux Mint Cinnamon 64bit (jeweils aktuelle Version)

An alle, die das LibreOffice-Forum nutzen:


Bitte beteiligen Sie sich mit 7 Euro pro Monat und helfen uns bei unserem Budget für das Jahr 2024.
Einfach per Kreditkarte oder PayPal.
Als Dankeschön werden Sie im Forum als LO-SUPPORTER gekennzeichnet.

❤️ Vielen lieben Dank für Ihre Unterstützung ❤️

Antworten