Live-Forum - Die aktuellen Beiträge
Datum
Titel
30.04.2024 21:27:28
Bei Autofilter immer leere Zeilen einschließen
30.04.2024 20:46:46
Dopplungen Markieren, nur wenn die Zelle nebenan ident. ist
30.04.2024 20:14:55
Datum filtern
30.04.2024 19:41:34
VBA: Zellinhalt wird nicht angezeigt
Du findest keine passende Antwort auf Deine Frage?
Stelle Deine Frage in unserem Live-Forum
Anzeige
Archiv - Navigation
1920to1924
Aktuelles Verzeichnis
Verzeichnis Index
Übersicht Verzeichnisse
Vorheriger Thread
Rückwärts Blättern
Nächster Thread
Vorwärts blättern
Anzeige
HERBERS
Excel-Forum (Archiv)
20+ Jahre Excel-Kompetenz: Von Anwendern, für Anwender
Vorheriger Thread
Vorheriger Thread
Rückwärts Blättern
1920to1924
1920to1924
Aktuelles Verzeichnis
Verzeichnis Index
Verzeichnis Index
Übersicht Verzeichnisse
Nächster Thread
Nächster Thread
Vorwärts blättern
Inhaltsverzeichnis

Prüfen ob letzte Zeichen Großbuchstaben

Prüfen ob letzte Zeichen Großbuchstaben
02.03.2023 16:28:26
Herbert
Hallo,
ich habe eine lange Liste mit URLs in einer Spalte und muss daraus jene herausfiltern und löschen, welche mit zwei Großbuchstaben enden.
Beispiel:
25hours-hotel-bikini-berlin-11KDME
10-karakoey-IST02013FH
a-rosa-kitzbuehel-kitzbuehel%3Binn29299ws
...
Am Beispiel müssten die ersten beiden damit herausgefiltert werden und der dritte nicht.
Könntet ihr mir da helfen?
Ich habe es hiermit versucht, aber es funzt nicht:
=ISTFEHLER(WENN(ISTZAHL(WERT(RECHTS(A1;2)));WENN(ISTZAHL(WERT(TEIL(A1;LÄNGE(A1)-2;1))));WERT(RECHTS(A1;2))=GROSS(WERT(RECHTS(A1;2))))))
DANKE!!!!
Herbert

40
Beiträge zum Forumthread
Beiträge zu diesem Forumthread

Betreff
Datum
Anwender
Anzeige
=PRODUKT(--(ABS(CODE(RECHTS(A1;{1.2}))-77,5)<13))
02.03.2023 16:31:56
lupo1
AW: =PRODUKT(--(ABS(CODE(RECHTS(A1;{1.2}))-77,5)<13))
02.03.2023 16:41:54
onur 
=IDENTISCH(GROSS(RECHTS(A1;2));RECHTS(A1;2))

siehe Bedenken zu Daniel unten
02.03.2023 16:44:04
lupo1
AW: siehe Bedenken zu Daniel unten
02.03.2023 16:45:49
onur
Du hattest ja auch vergessen, zu erwähnen, dass auch Ziffern im Spiel sein können - oder ?
ich bin unschuldig
02.03.2023 16:47:06
lupo1
AW: ich bin unschuldig
02.03.2023 16:47:45
onur
Sorry - nicht du. :)
AW: siehe Bedenken zu Daniel unten
02.03.2023 16:54:57
Herbert
Ja! Ich habe nicht bedacht, dass eine der letzten beiden Zeichen auch eine Ziffer sein könnten.
Also Nachbesserung: Ich muss rausfiltern wenn die beiden letzten Zeichen in Großbuchstaben sind. Achtung: Es kann in den beiden letzten Zeichen eine Ziffer vorkommen. Dennoch muss ich nur jene rausfiltern, wo die letzen beiden Zeichen jeweils ein Großbuchstabe sind.
=UND(ABS(CODE(RECHTS(A1;{1.2}))-77,5)<13) (kürzer)
02.03.2023 16:34:42
lupo1
AW: Prüfen ob letzte Zeichen Großbuchstaben
02.03.2023 16:38:21
Daniel
Hi
du kannst mit dieser Formel die gesuchten Werte mit WAHR kennzeichnen und die anderen mit FALSCH:
Identisch ist die Funktion, die zwei Werte miteinander vergleicht und dabei die Groß-Kleinschreibung berücksichtigt.
=IDENTISCH(RECHTS(A1;2);GROSS(RECHTS(A1;2)))
Dein Fehler ist, dass du die Funktion WERT mit der Funktion CODE verwechselst
WERT wandelt einen Text, der wie eine Zahl aussieht in eine Zahl um ("1" wird zu 1, "a" wird zu einem Fehler)
CODE gibt dir den ASCII-Code des Zeichens, dh Code("a") = Code(Gross("a")) könnte man schon verwenden, aber dafür gibts IDENTISCH und das ist nicht nur auf ein Zeichen begrenzt.
Gruß Daniel
Anzeige
AW: Prüfen ob letzte Zeichen Großbuchstaben
02.03.2023 16:42:19
Herbert
Danke Daniel!!!
Aber 25hours-hotel-the-circle-11cLP3 wird auch als WAHR erkannt obwohl das letzte Zeichen eine Zahl ist
AW: Prüfen ob letzte Zeichen Großbuchstaben
02.03.2023 16:54:37
Daniel
Ja Gross("3") = Klein("3")
dann musst du die Methode von Lupo nehmen, der prüft explizit auf die Zeichen A-Z
allerdings fallen bei im dafür ÄÖÜ raus.
Gruß Daniel
AW: Prüfen ob letzte Zeichen Großbuchstaben
02.03.2023 16:59:56
Herbert
OK!
Umlaute habe ich definitiv nicht enthalten. Es sind ja Teile einer URL und da gibt es sowas nicht. Bin zum Glück auf einem Unix Server ;-)
Auch URLs können Umlaute haben, auch wenn ...
02.03.2023 17:02:57
lupo1
... die dann vermutlich irgendwie umgesetzt werden.
AW: Prüfen ob letzte Zeichen Großbuchstaben
02.03.2023 16:39:33
Herbert
Super! Danke lupo1.
Ich versteh es nicht aber es funzt!
Daniels Formel ist einfacher
02.03.2023 16:43:09
lupo1
...meine hätte dann den Vorteil, wenn es z.B. auf den letzten, drittletzten und sechstletzten Buchstaben ankäme:
=UND(ABS(CODE(RECHTS(A1;{1.3.6}))-77,5)13)
Anzeige
AW: Daniels Formel ist einfacher
02.03.2023 16:51:35
Daniel
nenen wir es mal unterschiede.
meine Funktion akzeptiert auch Zeichen am Ende, bei denen Groß- und Kleinschreibung gleich ist, also auch Ziffern oder Interpunktionszeichen.
Deine Methode schließt solche Zeichen zwar aus, aber auch alle Zeichen, die außerhalb des 26-Zeichen-Standard-Aphabets liegen (ÄÜÖ), da diese nicht im selben ASCII-Codebreich liegen wie die Standardzeichen. Es wäre bei deiner Methode aufwendig, diese auch noch zu integrieren.
Gruß Daniel
Stimmt, aber selten in solchen Strings äöüß drinne
02.03.2023 16:55:20
lupo1
AW: Stimmt, aber selten in solchen Strings äöüß drinne
02.03.2023 16:58:31
Daniel
sowas muss der Anwender wissen. wir können da nur vermuten.
aber in dem eine Beispiel "kitzbuel" steht, dürfte die Verwendung von Umlauten hier unwahrscheinlich sein.
Gruß Daniel
Anzeige
AW: Stimmt, aber selten in solchen Strings äöüß drinne
02.03.2023 17:01:40
Herbert
Umlaute und sowas habe ich definitiv nicht. Sind ja URL Teile und ich bin auf nem Unix server
AW: Prüfen ob letzte Zeichen Großbuchstaben
02.03.2023 16:46:34
Daniel
Lupo prüft, ob der ASCII-Code der beiden letzten Zeichen zwischen 65 ("A") und 90 ("Z") liegt.
damit man für diese Prüfung nicht zwei Vergleiche braucht (&gt=65, &lt=90), verschiebt man den Wert so dass die Mitte zwischen beiden Grenzwerten bei 0 liegt und obere und untere Grenze vom Betrag her gleich sind. dann kommt man mit Hilfe der Funktion ABS mit einem Vergleich aus (&lt=ABS(13))
Gruß Daniel
AW: Prüfen ob letzte Zeichen Großbuchstaben
02.03.2023 16:50:25
Herbert
Cool der Ansatz von Lupo!
Und Danke Daniel für die Erklärung!!!
AW: Prüfen ob letzte Zeichen Großbuchstaben
02.03.2023 17:06:35
Daniel
noch ne Variante von Lupos Methode.
hier stehen zumindest die "bekannten" ASCII-Codes unverändert im Klartext in der Formel
Einzelne weitere Zeichen wären auch relativ einfach zu integrieren
=UND(VERWEIS(CODE(RECHTS(A3;{1.2}));{0.65.90};{0.1.0})=1)
Gruß Daniel
Anzeige
korrektur
02.03.2023 17:17:32
Daniel
da ist noch ein kleiner Fehler drin, besser so , damit da Z auch drin ist und auch gleich mit der Integration der Umlaute:
=UND(VERWEIS(CODE(RECHTS(A3;{1.2}));{0.65.91.196.197.214.215.220.221};{0.1.0.1.0.1.0.1.0})=1)
Gruß Daniel
85 statt 93 Zeichen bei Daniel|etwas anders gelöst
02.03.2023 17:33:56
lupo1
=ANZAHL(FINDEN(TEIL("ABCDEFGHIJKLMNOPQRSTUVWYYZÄÖÜ";ZEILE($1:$29);1);RECHTS(A1;2)))=2
Hier kann man vollflexibel die einzelnen Zeichen bestimmen (ohne Anfang und Ende der ASCII-Codes, also völlig unsystematisch)
OT:
=ANZAHL(FINDEN(TEIL("ABCDEFGHIJKLMNOPQRSTUVWYYZÄÖÜ";ZEILE($1:$29);1);LINKS(RECHTS(A1; {1.3.6})))) =3
Hier kommt noch der Freiheitsgrad der nicht zusammenhängenden Buchstaben im Auswertestring (1st-, 3t-, 6t-letzter) dazu.
Anzeige
Korrektur: natürlich ...WXYZ... statt ...WYYZ...
02.03.2023 17:36:28
lupo1
AW: 85 statt 93 Zeichen bei Daniel|etwas anders gelöst
02.03.2023 17:53:50
Daniel
naja etwas kürzer, aber mit ein paar anderen Nachteilen und einem Fehler:
- es ist aufwendiger, da die Schleife nicht nur über 2, sodern über 29 Zeichen läuft.
- wenn man Zeichen hinzufügt oder löscht, muss man auch den Zellbereich für Zeile() anpassen (warum hast du hier nicht mit LET und Sequenz gearbeitet?)
- bei älteren Excelversionen ist der Matrixformelabschluss erforderlich.
und jetzt der Fehler:
Diese Formel gibt nur einen Treffer, wenn die beiden Buchstaben am Ende unterschiedlich sind. Sind die Buchstaben gleich, so wird das nicht erkannt.
ich würde das, wenn man die Liste der Zeichen vollständig vorgeben will, dann lieber so machen:
=UND(ISTZAHL(FINDEN(LINKS(RECHTS(A1;{1.2});1);"ABCDEFGHIJKLMNOPQRSTUVWYYZÄÖÜ")))
Gruß Daniel
Anzeige
Ok, ok ...
02.03.2023 18:03:05
lupo1
... hatte das jetzt nicht getestet mit den gleichen Einträgen (stimmt, ist dann falsch). Dafür ist dann die zweite OT-Version nötig, die es kann (mit der Zusatzfunktionalität auseinanderliegender RECHTS-Zeichen).
29 Schleifen: Einen Tod muss man halt sterben für die Unsystematik. Dein Verweis hat ja auch mehr als 2 Zahlen (aber: binär suchend, daher schnell).
Anpassung: Dann wäre es nicht so kurz. Außerdem haben wir früher immer alles Mögliche anpassen müssen. Wenn ich daraus eine Funktion mache (LET oder LAMBDA), ist es natürlich egal.
AW: Ok, ok ...
02.03.2023 18:21:37
Daniel
"Länge" steht bei mir in der Priorität bei der Bewertung von Formeln nicht an erster Stelle. "Intern Erzeugter Rechenwaufwand" ist da schon wichtiger.
Überall wo sowas wie Zeile(1:29) drin steht, hat man zusätzlich das Problem, dass nachträgliche Änderungen am Tabellenblatt, die u.U. nichts mit der eigentlichen Formel zu tun haben, diese verändern können so dass sie nicht mehr funktionert (daher besser Sequenz statt Zeile für Zahlenfolgen)
du hast doch früher gerne die Fragesteller mit Lösungen für Excel 365 "beglückt", auch wenn diese eine ältere Excelversion haben, und jetzt, wo der Fragesteller Excel 365 verwendet, verzichtest du auf die Vorteile dieser Version... naja ich muss dich ja nicht verstehen.
und zum Verweis, ob da mehr oder weniger Zahlen drin stehen, ist schon was anderes als eine Matrixformel, in der die komplette Formelberechnung in einer Schleife durchgewältz wird...
aber trotzdem , wenn die erlaubten Zeichen als ASCII-Wert wenige, aber große zusammenhängende Gruppen bilden, dann würde ich den Verweis vorziehen, wenn es viele Einzelne Zeichen sind, diese Methode.
Gruß Daniel
Anzeige
Wenn 365 nicht so entscheidende Vorteile bietet ..
02.03.2023 19:48:07
lupo1
... darf man ja auch mal für die Vergangenheit was liefern.
ZEILE(INDIREKT("1:29")) ginge ja auch.
Ansonsten siehe Tutorium MATCH.CHAR. Dort jetzt
gespillt (also gleich für eine Spalte)
von links oder rechts zählend möglich
unzusammenhängende Stellen möglich oder halt {1.2.3} oder SEQUENZ(;3)
pro Stelle ist ein eigener Zeichenbereich möglich, aber nicht nötig
AW: Wenn 365 nicht so entscheidende Vorteile bietet ..
03.03.2023 08:09:01
Herbert
Hey,
was habe ich denn hier nur losgetreten? ;-)
Ich verstehe zwar wenig von euer weiteren Diskussion und Ansätzen, finde es aber echt klasse wie ihr versucht nicht nur meinen Fall zu lösen und dabei auch Performance Aspekte und erweiterte Anwendungsmöglichkeiten einbezieht.
Respekt!
Anzeige
B1#: =MATCH.CHAR(A1:A3;"AB..ZÄÖÜ";1;{1.2})
03.03.2023 08:37:05
lupo1
... aus dem Tutorium verwenden. Hier die Formel noch einmal ausgeschrieben (passte nicht in den Betreff):
B1#: =MATCH.CHAR(A1:A3;"ABCDEFGHIJKLMNOPQRSTUVWXYZÄÖÜ";1;{1.2})
AW: korrektur
02.03.2023 17:56:42
Herbert
WoWW! Ihr seid cool!
DANKE!
Antwort, die keiner mehr braucht
02.03.2023 18:13:09
Yal
Moin,
schon viele Lösungen eingereicht.
Trotz "VBA nein" schlage ich eine "fast ohne VBA": man legt in VBA einen Code, den Man in Excel als Formel verwendet. Es heisst "Use Defined Function".
So kannst Du mit
=Letzte2Gross(A2)
ein "WAHR" oder "FALSCH" bekommen.
Dafür muss man nur
_ VB-Editor öffnen (Alt+F11)
_ einen Modul öffnen: Menü "Einfügen", "Modul"
_ Code dorthin copy-pasten
_ Formel in Excel verwenden
Code (sieht eigentlich sehr ähnlich, wie was wir schon in den Formeln gesehen haben, aber VBA kann die Zwischenergebnisse in Variablen ablegen): 
Public Function Letzte2Gross(ByVal Target As Range) As Boolean
Dim S1
Dim S2
    S1 = Left(Right(Trim(Target), 2), 1)
    S2 = Right(Trim(Target), 1)
    Letzte2Gross = Not (IsNumeric(S1)) And UCase(S1) = S1 And Not (IsNumeric(S2)) And UCase(S2) = S2
End Function
Da 365 könnte man mit Lambda-Ausdrücke etwas ähnliches bekommmen. Ich bin überrascht, dass keiner drauf gekommen ist (oh je: jetzt geht es nochmal los! ;-)
VG
Yal
AW: Antwort, die keiner mehr braucht
02.03.2023 18:24:03
Daniel
da schließt du Zahlen aus.
Sollten Interpunktionen am Ende vorkommen, bekommen die aber ein WAHR.
Gruß Daniel
Na gut, dann mit Kanonen...
02.03.2023 18:45:23
Yal
Who knows the beauty of RegExp (da ist man aber weit von "VBA nein").
Public Function Letzte2Gross(ByVal Target As Range) As Boolean
Dim R As Object
    Set R = CreateObject("VBScript_RegExp_55")
    R.Pattern = "[A-ZÄÖÜ]{2}"
    Letzte2Gross = R.Test(Right(Trim(Target), 2))
End Function
VG
Yal
AW: Na gut, dann mit Kanonen...
02.03.2023 18:56:53
Daniel
Hi
da braucht man jetzt noch nicht mal RegExp, da reicht auch einfaches LIKE, welches zumindest mal in der OnLinie-Hilfe beschrieben ist:
Public Function Letzte2Gross(ByVal Target As Range) As Boolean
Letzte2Gross = Target(1).Text Like "*" & "[A-ZÄÖÜ][A-ZÄÖÜ]"
End Function
Gruß Daniel
Nice!
02.03.2023 19:34:03
Yal
so ein Art versteckter RegExp.
Der Stern kann rühig in dieselbe String wie die beiden Bereiche einrücken.
VG
Yal
AW: Nice!
02.03.2023 19:44:01
Daniel
ja
ich hatte da erst eine Variable mit [A-ZÄÜÖ] vorgesehen, die man dann entsprechend der gewünschten Stringlänge dupliziert (muss ja nicht immer 2 sein), also im Prinzip ein Like "*" Worksheetfunction.Rept("[A-ZÄÜÖ]", 2)
und das dann wieder zurückgebaut.
Da übersieht man sowas auch mal
Gruß Daniel

Beliebteste Forumthreads (12 Monate)

Anzeige
Jetzt das Excel-Portal bewerten: Deine Stimme zählt!

Beliebteste Forumthreads (12 Monate)

Anzeige
Anzeige
Anzeige