In Excel in einer Zelle nach 2 Großbuchstaben nebeneinander suchen und das auflisten/anzeigen lassen?
Ich habe eine Liste mit über 20.000 Einträgen. Ich möchte nun alle Einträge finden, die mind. 2 Großbuchstaben nebeneinander beinhalten.
Also z.B. ABc, DonALd usw.
Entweder sollen die Einträge markiert sein oder aber in der Zelle neben den Einträgen steht so etwas wie Wahr o.ä.
Freue mich auf Eure Hilfe.
HHIndi
2 Antworten
Die {Matrix}Formel *(siehe unten)
{=SUMME(WENN(IDENTISCH(TEIL(D2;ZEILE(INDIREKT("1:"&LÄNGE(D2)));1);KLEIN(TEIL(D2;ZEILE(INDIREKT("1:"&LÄNGE(D2)));1)));0;1))-1}
gibt die Anzahl der Großbuchstaben in hier D2 wieder. Setze sie also in eine Zelle der Zeile 2 und kopiere sie erst mal nach Bedarf nach rechts oder links, bis der Relativbezug auf Deine fragliche Spalte verweist. Als Format setzt Du -mein Standardvorschlag- das benutzerdefinierte Zahlenformat
0;[Rot]-0;[Farbe7]"٠" (auch anderweitig von Vorteil). Schrift: Arial Black
Diese Kennzeichnung ist besser als jedes bedingte Format:
-1 in rot, wenn kein , die unscheinbare indische Null, wenn ein, die dicke schwarze Zahl, wenn mehrere Großbuchstaben auftreten. Sehr gut sichtbar beim Überblättern, auch zu filtern nach >0.
Diese Zelle kopierst du nach unten.
Noch einfacher (bei 20000 Zeilen): Du fügst vor Deiner Textespalte eine neue ein, verschiebst die richtige Formel in Zeile2 dahin.
Dann machst Du einen Doppelklick aufs Ausfüllkästchen: Die Formel kopiert sich bis neben den letzten Eintrag (kontrolliere das mit Ende ↓, ich glaube, Leerzellen zwischendurch stoppen das nach-unten-Kopieren. (ggf mehrfach wiederholen: bei 20000 Zeilen könnte sich das lohnen)
Es ist denkbar, dass sich bei so vielen Matrixformeln die Performance in die Knie geht. Dann markierst du bis VOR die letzte Formel, kopierst und fügst das Ergebnis ALS WERTE an selber Stelle wieder ein und kopierst dann die Formel weiter nach unten.
*) Achtung: Dies ist eine Matrixformel! Die geschweiften {Klammern} NICHT miteingeben, sondern die Formel anstatt mit einfachem Enter mit der Kombination (gleichzeitig) Strg+Shift+Enter abschließen (nach jeder Neubearbeitung wieder!). Die {Klammern} entstehen dann automatisch.
Schreibtechnisch nimmst Du am besten die rechte Strg- und Umschalttaste, dann gehts mit einer Hand und lässt sich auch leichter merken!
Nach dem Kommentar von Schmiddi steht fest, dass Du ohnehin die treffendere Lösung hast, siehe aber trotzdem meine Kommentare dort!
Durch das -1 am Ende der Formel werden nicht die Großbuchstaben gezählt, sondern die Doppel. beliebig variierbar, hab ich nur wegen der Formatdarstellung so gemacht Ohne das -1 wäre es die Anzahl der Großbuchstaben.
Leere Zellen ergeben #Bezug!, was bei reduz. Spaltenbreite als Gartenzaun #### erscheint
Sind die Einträge auf mehrere Spalten verteilt, brauchst du ebensoviele Formelspalten, ansonsten würde die Formel ein Ungetüm.
Betroffene Zellen sind zählbar mit =Zählenwenn(Formelbereich;">0") .
Durch das -1 am Ende der Formel werden nicht die Großbuchstaben gezählt, sondern die Doppel.
Wenn deine Formel die Anzahl an Großbuchstaben berechnet, müsstest du dann nicht am Ende /2 rechnen, anstelle -1, um die Anzahl an Doppler zu bestimmen?
Das setzt natürlich voraus, dass die Großbuchstaben, wenn sie vorkommen, hintereinander stehen.
Innerhalb einer Formel ist es bestimmt auch möglich, aber es ist auch einfacher möglich.
Schaffe dir rechts von der Spalte genügend Spalten (Anzahl der max. Wortlänge +1).
Bei mir Steht in Spalte A: ABc, DonALd,...
Ich nutze Spalten bis I, also maximale Wortlänge von 8
B1: =UND(ISTZAHL(FINDEN(TEIL($A1;SPALTE()-1;1);"ABCDEFGHIJKLMNOPQRSTUVWXYZÄÖÜ"));ISTZAHL(FINDEN(TEIL($A1;SPALTE();1);"ABCDEFGHIJKLMNOPQRSTUVWXYZÄÖÜ"));SPALTE()<=LÄNGE($A1))
Das ziehst du bis H1.
I1: =ODER(B1:H1)
B1:I1 kannst du jetzt bis zum Ende der Spalte A herunterziehen.
Überall, wo in der Spalte I WAHR steht, hast du einen Großbuchstabendoppler in Spalte A.
Wem das mit
ISTZAHL(FINDEN(TEIL($A1;SPALTE()-1;1);"ABCDEFGHIJKLMNOPQRSTUVWXYZÄÖÜ"))
zu kompliziert ist, dem kann ich eine Alternative bieten.
=UND(NICHT(IDENTISCH(KLEIN(TEIL($A1;SPALTE()-1;1));TEIL($A1;SPALTE()-1;1)));NICHT(IDENTISCH(KLEIN(TEIL($A1;SPALTE();1));TEIL($A1;SPALTE();1)));SPALTE()<=LÄNGE($A1))
Leider machen die Funktionen NICHT(IDENTISCH( die Formel nicht kürzer.
Aber Achtung, man kann nicht einfach auf <> prüfen, weil für "a"<>"A" liefert Excel FALSCH!
Das ist das, was ich bei dieser Aufgabe mitnehme.
auch für ß=ss liefert xl Wahr (Maße=Masse), identisch aber FALSCH.
allerdings werden die Umlaut-Umschreibungen ae=ä etc von = nicht erkannt. Kein System!
Ich verstehe nicht so ganz.
ss ungleich ß
Maße ungleich Masse
Körpermaße können auch Länge und Breite sein. Körpermasse ist die Schwerkraft. Das sind unterschiedliche Wörter und Excel bestätigt auch, dass sie unterschiedlich sind.
Der Befehl IDENTISCH arbeitet meines Erachtens diesbezüglich korrekt.
Oder meinst du, dass es ein Fehler ist, dass KLEIN("ß") = ß ist?
Ja, das ist der Sonderfall ß. Es gibt hierfür keinen Großbuchstaben, weil das ß nicht am Anfang stehen kann.
Hier würde aber die Alternative, die FINDEN-Variante korrekt arbeiten.
Auch fällt mir kein deutsches Wort ein, bei dem ß an zweiter Stelle steht. Deswegen dürften niemals zwei Großbuchstaben, bzw. Großbuchstabe + ß nebeneinander stehen.
Hm, irgendwas scheine ich falsch zu machen: bei mir erscheint dann überall FALSCH, obwohl ich in A1 den Eintrag AaBbCcAa stehen habe.
Ah jetzt hab ichs anscheinend: Deine Formel erkennt nur aufeinanderfolgende Groß-Doppler?
Und ich dachte schon, ich hätt mir die fast ganze stunde arbeit vergeblich gemacht (umsonst machen wirs ja alle!)
Müsstest wahrscheinlich anstatt mit UND() mit Summe() arbeiten?
HiHi da steht DonALd, also großes AL.
Der FS hat sich auch schon in einer Extra Antwort bedankt :))
Genau. :) Das mit IDENTISCH war meine nächste Idee. Scheint naheliegend zu sein. Ich habe nicht abgeguckt. ;)
Das ist eine gute Lösung zum Zählen von Großbuchstaben.