Frage von Ioulios, 129

Zahl zwischen zwei Buchstaben in Excel Finden?

Hallo zusammen,

ich verzweifel langsam, ich hab schon das ganze WWW durchsucht und rumprobiert ohne Ende, jedoch ohne Erfolg - ich hoffe Ihr könnt mir helfen.

Folgendes: Ich habe in der Spalte A1 einen eingescannten Wert beispielsweise 2389089JKLQ344Q30PC2936MNW . Mit welcher Funktion schaffe ich es die Zahl 30 zwischen dem Q und dem PC in die spalte A2 zu bekommen?

Ich danke euch für die Antworten.

Hilfreichste Antwort - ausgezeichnet vom Fragesteller
von dkilli, 65
=TEIL(A1;FINDEN("#";WECHSELN(A1;"Q";"#";LÄNGE(A1)-LÄNGE(WECHSELN(A1;"Q";""))))+1;FINDEN("PC";A1)-FINDEN("#";WECHSELN(A1;"Q";"#";LÄNGE(A1)-LÄNGE(WECHSELN(A1;"Q";""))))-1)
Kommentar von dkilli ,

Es geht noch kürzer:

=TEIL(H3;VERWEIS(9^9;FINDEN("Q";H3;SPALTE(3:3)))+1;FINDEN("PC";H3)-VERWEIS(9^9;FINDEN("Q";H3;SPALTE(3:3)))-1)
Kommentar von dkilli ,

Oh Fehlerteufel:

=TEIL(A1;VERWEIS(9^9;FINDEN("Q";A1;SPALTE(1:1)))+1;FINDEN("PC";A1)-VERWEIS(9^9;FINDEN("Q";A1;SPALTE(1:1)))-1)
Kommentar von Ioulios ,

Optimal! Vielen Dank!!!:)

Wenn hinter dem Q234PC jetzt noch ein Q stehen würde.. Dann funktioniert es nicht mehr - seh ich das richtig?

Kommentar von dkilli ,

Ja das stimmt. Dann wird die Formel komplizierter

Kommentar von Ioulios ,

aber möglich wäre das?

Kommentar von dkilli ,

Grundsätzlich ja

Kommentar von Iamiam ,

ich hab mal noch eine Erklärung der Formel ausgearbeitet, denn so ein Monstrum ist doch nicht ohne Weiteres zu durchschschauen:

  • =TEIL(A1;FINDEN("#";WECHSELN(A1;"Q";"#";LÄNGE(A1)-LÄNGE(WECHSELN(A1;"Q";""))))+1;FINDEN("PC";A1)-FINDEN("#";WECHSELN(A1;"Q";"#";LÄNGE(A1)-LÄNGE(WECHSELN(A1;"Q";""))))-1)
  • Man muss erst mal ermitteln, wieviele Q es sind, indem man Q gegen "" (nichts) ersetzt und die Längen vergleicht:
  • LÄNGE(A1)-LÄNGE(WECHSELN(A1;"Q";"") (Wechseln unterscheidet Groß/klein!*)
  • Mit dieser Differenz (LängDiff)geht man in eine weitere Wechseln-Funktion und wechselt dieses n-te Q gegen # aus:
  • WECHSELN(A1;"Q";"#";LängDiff) Längdiff ist also die Anzahl der Q im Text, gefunden werden soll das letzte, also das (Längdiff)-te.
  • Die Position des so gewonnenen, einzigen # wird nun mit Finden() (oder Suchen) ermittelt und mit Teil() ab dieser Position+1 der Rest ausgeschnitten, aber eben nicht der ganze Rest, sondern nur eine Länge bis zur Position von PC.
  • Die ist allerdings wiederum nur mit einer Dopplung des vorherigen Vorgehens herauszubekommen, deshalb der ganze Rattenschwanz nach "PC" nochmals:
  • ;FINDEN("PC";A1)-FINDEN("#";WECHSELN(A1;"Q";"#";LÄNGE(A1)-LÄNGE(WECHSELN(A1;"Q";""))))-1 . Somit ergibt sich am Ende:
  • (Für das Beispiel 23KL234092Q39Q409MQAX234234Q123PCM321123321AF in A1): Teil(A1;29;3)
  • *) will man Q und q finden, muss man anstatt A1 innerhalb Wechseln Groß(A1) einsetzen und kann dann jeweils Q/q als Q finden
Zu Deiner Zusatzfrage, wenn noch ein Q danach käme

: dann das (Längdiff-1)-te

Kommentar von dkilli ,

Danke Lamiam, eine ausgezeichnete Erklärung 

Eine Anmerkung:

Die Version mit "=TEIL(A1;FINDEN("#";WECHSELN(A1;"Q";".. 

klappt ohne Probleme auch mit OOo und LibreOffice! Aber komischerweise klappt es mit VERWEIS nicht. 

Es ist eine der wenige Fälle wo sich Openoffice bzw Libre Office von Excel unterscheidet. 

Superklein weich (Microsoft) wird jetzt immer unverschämter. Nicht nur, dass deren Programme, die sie alle abgekupfert haben, schon immer sehr viel Geld gekostet haben, denn ab jetzt gibt es keine neue Erweiterungen bzw. Neuerungen, außer man bezahlt  ein Abo für ab 70€/Jahr 

Kommentar von Iamiam ,

ich nehme an, Du beziehst dich auf eine andere Frage, denn hier tauchen weder Verweis noch Vergleich auf.

Verweis braucht im Ggs zu SVerweis immer geordnete Tabellen,  analog SVerweis(..;..;..;WAHR), das aber auch in xl.

älteres LO und wahrscheinlich immer noch OO akzeptieren nicht die Benennung ganzer Spalten, also A:A und B:B, ebenso nicht 1:1, 2:2, ...

ich hab LO Version: 5.1.3.2, da klappen auch A:A und 1:1

Musste bisher immer als A1:A1 000 000 oder so bezeichnet werden bzw als A1:AMJ1. Hatte den Nachteil, dass man beim Einfügen von Zeilen bzw Spalten sofort über die Grenzen des Arbeitsblattes rauskam,

bei Spalten, dass in xl die Spalten bis XFD gehen (was schlichtweg unnavigierbar ist und keiner braucht, aber um sich von OO abzusetzen halt mal schnell eingeführt wurde).

Oder Du verwendest Wildcards: Die sind in LO/OO .* bzw .? (also mit Punkt davor).

Wenns was anderes ist, bitte nochmal melden!

Kommentar von dkilli ,

In meiner 1. Antwort steht: "TEIL(A1;FINDEN("#";WECHSELN(A1;"Q";"#";LÄNGE..." 

Später habe ich eine kürzere Formel verwendet, die Verweis verwendet: =VERWEIS(9^9;FINDEN(B1;A1;SPALTE(1:1)))...

Diese Formel ergibt den Fehler #WERT!  bei LO 

Kommentar von PWolff ,

Das Problem hat mir keine Ruhe gelassen.

(Suchen eines Teilstücks zwischen vorgegebenen Texten ohne Verwendung von Makros)

Hier ist das Ergebnis (in A1 habe ich das Beispiel mehrfach modifiziert hintereinandergesetzt, um mehrere Vorkommen zu simulieren)

In A3 bis A6 stehen Formeln, die das erste Vorkommen des betreffenden Textes finden; in A7 steht die Zusammenfassung der Formeln von A3 bis A6.

In A10 bis A13 stehen Formeln, die das letzte Vorkommen des betreffenden Textes finden; in A14 steht die Zusammenfassung der Formeln von A10 bis A13.

(Ich habe nicht nur die Endergebnisse - A7 und A14 - gepostet, damit man eine Chance hat, die Formeln mit vertretbarem Aufwand nachzuvollziehen.)

Die Formeln sind "Matrixformeln", weil sie sich notwendigerweise auf einen ganzen Zellbereich beziehen. (Eingabe über Strg+Umschalt+Zeilenschaltung)

Hier die Inhalte der Zellen A1 bis A14:

A1:           2389089JKLQ344Q30PC2936MNW3490190JKLQ455Q41PC3047MNW4501201JKLQ566Q52PC4158MNW
A2:
A3: =SUCHEN("PC";$A$1)
A4: =MAX(WENN(ISTFEHLER(SUCHEN("Q";LINKS($A$1;$A$3);ZEILE(A:A)+1));-1;SUCHEN("Q";LINKS($A$1;$A$3);ZEILE(A:A)+1)))
A5: =TEIL($A$1;($A$4+1);$A$3-($A$4+1))
A6: =WERT($A$5)
A7: =WERT(TEIL($A$1;(MAX(WENN(ISTFEHLER(SUCHEN("Q";LINKS($A$1;SUCHEN("PC";$A$1));ZEILE(A:A)+1));-1;SUCHEN("Q";LINKS($A$1;SUCHEN("PC";$A$1));ZEILE(A:A)+1)))+1);SUCHEN("PC";$A$1)-(MAX(WENN(ISTFEHLER(SUCHEN("Q";LINKS($A$1;SUCHEN("PC";$A$1));ZEILE(A:A)+1));-1;SUCHEN("Q";LINKS($A$1;SUCHEN("PC";$A$1));ZEILE(A:A)+1)))+1)))
A8:
A9:
A10: =MAX(WENN(ISTFEHLER(SUCHEN("PC";$A$1;ZEILE(A:A)+1));-1;SUCHEN("PC";$A$1;ZEILE(A:A)+1)))
A11: =MAX(WENN(ISTFEHLER(SUCHEN("Q";LINKS($A$1;$A$10);ZEILE(A:A)+1));-1;SUCHEN("Q";LINKS($A$1;$A$10);ZEILE(A:A)+1)))
A12: =TEIL($A$1;($A$11+1);$A$10-($A$11+1))
A13: =WERT($A$12)
A14: =WERT(TEIL($A$1;(MAX(WENN(ISTFEHLER(SUCHEN("Q";LINKS($A$1;MAX(WENN(ISTFEHLER(SUCHEN("PC";$A$1;ZEILE(A:A)+1));-1;SUCHEN("PC";$A$1;ZEILE(A:A)+1))));ZEILE(A:A)+1));-1;SUCHEN("Q";LINKS($A$1;MAX(WENN(ISTFEHLER(SUCHEN("PC";$A$1;ZEILE(A:A)+1));-1;SUCHEN("PC";$A$1;ZEILE(A:A)+1))));ZEILE(A:A)+1)))+1);MAX(WENN(ISTFEHLER(SUCHEN("PC";$A$1;ZEILE(A:A)+1));-1;SUCHEN("PC";$A$1;ZEILE(A:A)+1)))-(MAX(WENN(ISTFEHLER(SUCHEN("Q";LINKS($A$1;MAX(WENN(ISTFEHLER(SUCHEN("PC";$A$1;ZEILE(A:A)+1));-1;SUCHEN("PC";$A$1;ZEILE(A:A)+1))));ZEILE(A:A)+1));-1;SUCHEN("Q";LINKS($A$1;MAX(WENN(ISTFEHLER(SUCHEN("PC";$A$1;ZEILE(A:A)+1));-1;SUCHEN("PC";$A$1;ZEILE(A:A)+1))));ZEILE(A:A)+1)))+1)))

Nachteil dieser Lösung: sehr lange Berechnungszeit (da Matrixformeln über eine ganze Spalte - leider habe ich bisher keinen Weg gefunden, dies auf die Länge eines beliebigen Strings zu begrenzen)

Antwort
von PWolff, 47

Ohne Makro wüsste ich da nichts, v. a. da ich keine Excel-Funktion kenne, die Reguläre Ausdrücke verwendet.

Immerhin kann man eigene Funktionen definieren, in denen man dann Reguläre Ausdrücke verwenden kann. Ich würde so eine Funktion REGEXEXTRACT nennen und sie aufrufen als

=REGEXEXTRACT(A1;"Q(\d+)PC";"$1")

Die Funktion selbst wird etwas komplizierter, da VBA bei einer Standardinstallation nur auf eine veraltete und eigenwillige Implementierung von Regular Expressions zurückgreifen kann.

Kommentar von Suboptimierer ,

Ich verwende immer als Bibliothek die "Microsoft VBScript Regular Expressions 5.5". Sind es die, die du auch verwendest?

Kommentar von PWolff ,

ja - das ist schließlich die, die sich am leichtesten einbauen lässt und bei der man sicher sein kann, dass der Kunde/Freund/... die installiert hat. Hab keine Lust auf überflüssige Klimmzüge.

Kommentar von Iamiam ,

ich hab keine Ahnung, wovon ihr redet! Programmierergriechisch?

Aber immerhin geht es auch ohne Makro (s. AW dkilli)

Expertenantwort
von Suboptimierer, Community-Experte für Excel, 87

Einmal suchst du mittels FINDEN nach "Q", einmal nach "PC". Dann extrahierst du den Teil dazwischen mit TEIL und den Ergebnissen der beiden Suchen.

Dann wiederholst du das, bis kein weiteres Q gefunden wird. In deinem Beispiel ist zu sehen, dass es mehrere Q vor PC gibt.

Schade, dass Excel nicht ohne Weiteres mit Regulären Ausdrücken umgehen kann.

Arbeite beim Testen mit Hilfszellen. Später kannst du sie zusammenfassen.

Antwort
von dkilli, 79

Steht die Zahl immer zwischen Q und PC und hat die Zahl immer die gleiche Stellenzahl?

Kommentar von Ioulios ,

Ich bitte um Verzeihung die Zahl kann variieren 2 Stellig / 3 Stellig / 4 Stellig - Die Zahl steht immer zwischen dem Q und dem PC

Kommentar von Oubyi ,

Ist das denn immer das zweite Q in dem String?
Gib mal ein paar mehr Beispiele.

Kommentar von Ioulios ,

Nein die Zahlen und Buchstaben davor sind immer anders die Zahl zwischen dem Q und dem PC ist auch unterschiedlich . Das einzige was Fix ist , ist das Q und das PC es kann auch sein dass 5 Q's vor dem eigentlichen Q kommen.

bsp.

23KL234092Q39Q409MQAX234234Q123PCM321123321AF

Kommentar von dkilli ,

Das ist die Lösung:

=TEIL(A1;FINDEN("#";WECHSELN(A1;"Q";"#";LÄNGE(A1)-LÄNGE(WECHSELN(A1;"Q";""))))+1;FINDEN("PC";A1)-FINDEN("#";WECHSELN(A1;"Q";"#";LÄNGE(A1)-LÄNGE(WECHSELN(A1;"Q";""))))-1)

Keine passende Antwort gefunden?

Fragen Sie die Community

Weitere Fragen mit Antworten