Microsoft Excel

Herbers Excel/VBA-Archiv

Datenüberprüfung - Platzhalter nicht erlauben


Betrifft: Datenüberprüfung - Platzhalter nicht erlauben
von: Communicator
Geschrieben am: 21.04.2019 00:36:23

Hallo,

ich habe nun verschiedentliche Dinge probiert sowie im Forum gestöbert. Aber ich komme nicht weiter.

Ich möchte Text in eine Zelle eingeben. Der Text darf aus den Buchstaben A-Z, den Buchstaben a-z, den Zahlen 0-9 sowie den Zeichen Unterstrich und Bindestrich bestehen. Weiterhin darf der Text 50 Zeichen lang sein.

Ich möchte das nicht über VBA lösen, sondern über die Datenüberprüfung. Dort habe ich bereits folgende Formel in Verwendung (teilweise auch aus einem Forum kopiert). Leider ist es möglich, in den Text die Zeichen ?, * und ~ einzugeben. Das sollte so nicht sein. Dementsprechend wollte ich die Formel erweitern. Ich habe es leider nicht hinbekommen. Die Zelle mit dem Text befindet sich in B24.

=UND(LÄNGE($B$24)<=50;SUMMENPRODUKT(ISTZAHL(SUCHEN(TEIL($B$24;ZEILE(INDIREKT("1:"&LÄNGE($B$24))); 1); "abcdefghijklmnopqrstuvwxyzäöü0123456789-_"))*1)=LÄNGE($B$24))

Die Platzhalter können in verschiedenen Formeln über die Strings "~?", "~*" oder "~~" gefunden, der Code ermittelt werden usw.
Ich bekomme es aber leider nicht kombiniert mit der bestehende Formel, da man bei der benutzerdefinierten Formel nur die erlaubten Zeichen angibt. Im Gegensatz zu den bedingten Formatierungen kann man ja auch nur eine Formel nutzen.

Ich hoffe es umfänglich beschrieben zu haben. Sollte dies nicht so sein, bitte einfach nachfragen.

  

Betrifft: AW: Datenüberprüfung - Platzhalter nicht erlauben
von: cysu11
Geschrieben am: 21.04.2019 01:58:43

Hi,

probiere es mal hiermit:

=SUMMENPRODUKT(ISTZAHL(FINDEN(TEIL("?/A";ZEILE(INDIREKT("B1:B"&3));1);B24;1))*1) =0
Der erste fette Teil, hier gibst du die Zeichen ein die du nicht erlaubst und der zweite fette Teil hier gibst du die Anzahl der Zeichen die du vorher eingegeben hast.
Alternativ kannst du auch die ganzen Zeichen die du nicht erlaubst in eine Zelle eingeben und mit Länge() die Länge auslesen.
=SUMMENPRODUKT(ISTZAHL(FINDEN(TEIL($C$1;ZEILE(INDIREKT("A1:A"&LÄNGE($C$1)));1);B24;1))*1)=0
LG
Alexandra


  

Betrifft: AW: Datenüberprüfung - Platzhalter nicht erlauben
von: cysu11
Geschrieben am: 21.04.2019 02:03:03

Die Formel ist von hier:

http://www.office-loesung.de/ftopic199116_0_0_asc.php

LG
Alexandra


  

Betrifft: AW: Datenüberprüfung - Platzhalter nicht erlauben
von: Communicator
Geschrieben am: 21.04.2019 13:28:14

Ich habe die Lösung gefunden, vielen Dank Alexandra für die Anregungen. Es musste SUCHEN durch FINDEN ersetzt werden. Weil FINDEN zwischen Groß- und Kleinschreibung unterscheidet,mussten noch die Buchstaben A-Z nachgetragen werden.

Die folgende Formel in der Datenüberprüfung erlaubt die aufgeführten Zeichen in dem String und begrenzt die maximale Eingabelänge auf 50 Zeichen. Weiterhin können die problematischen Platzhalter nun auch nicht mehr in die Zelle B24 eingetragen werden.


=UND(LÄNGE($B$24)<=50;SUMMENPRODUKT(ISTZAHL(FINDEN(TEIL($B$24;ZEILE(INDIREKT("1:"&LÄNGE($B$24))); 1);"ABCDEFGHIJKLMNOPQRSTUVWXYZabcdefghijklmnopqrstuvwxyzäöü0123456789-_"))*1)=LÄNGE($B$24))


Die vorgeschlagenen Lösungen, in der jedes verbotene Zeichen einzeln in die Formel eingetragen muss oder in eine Zelle des Arbeitsblattes geschrieben werden muss, hat mir nicht so zugesagt. Ich konnte daraus dann allerdings eine für mich funktionierende Lösung ableiten, vielen Dank dafür.


  

Betrifft: AW: geht mit SUCHEN() und auch ohne INDIREKT() ...
von: neopa C
Geschrieben am: 22.04.2019 11:12:20

Hallo,

... z.B. so:

Arbeitsblatt mit dem Namen 'Tabelle1'
 B
24Ein_109-AZaz-ÄÜ-äü

NameBezug
Pruef=(ANZAHL(SUCHEN(TEIL(!B24;ZEILE(INDEX(!B:B;1):INDEX(!B:B;50));1);"abcdefghijklmnopqrstuvwxyzäöü0123456789-_"))=50)*(LÄNGE(!B24)<=50)

ZelleGültigkeitstypOperatorWert1Wert2
B24Benutzerdefiniert =Pruef 
Verwendete Systemkomponenten: [Windows (32-bit) NT 6.02] MS Excel 2010
Diese Tabelle wurde mit Tab2Html (v2.6.0) erstellt. ©Gerd alias Bamberg


Gruß Werner
.. , - ...


  

Betrifft: AW: geht mit SUCHEN() und auch ohne INDIREKT() ...
von: Communicator
Geschrieben am: 22.04.2019 13:33:15

Hallo Werner,

vielen Dank für die Formel, sieht interessant aus, da wesentlich kürzer. Leider kann ich sie nicht in das Formelfeld eintragen. Da kommt eine Fehlermeldung (sinngemäß "Sie wollen keine Formel eingeben? Tragen Sie 1+1 ein...").


  

Betrifft: AW: geht mit SUCHEN() und auch ohne INDIREKT() ...
von: Hajo_Zi
Geschrieben am: 22.04.2019 17:04:00

ersetze das ! durch nichts. Fals ! muss die Tabelle davor stehen

GrußformelHomepage

Beiträge von Werner, Luc, robert, J.O.Maximo und folgende lese ich nicht.
Die Beiträge werden auch ignoriert, es erfolgt keine Antwort.


  

Betrifft: AW: Formel ist nicht als Zellformel definiert ...
von: neopa C
Geschrieben am: 22.04.2019 17:45:45

Hallo,

... und diese Formel kannst Du, genauso wie von mir angegeben, bei aktivierter Zelle im Namensmanager definieren. Als Namen hab ich dieser Formel "Pruef" zugewiesen und diese benannte Formel dann in der Datengültigkeit der Zelle B24 als benutzerdefinierte Formel zugewiesen. Beachte: im konkreten Fall ist es notwendig, dass bei der Option "Leere Zellen ignorieren" kein Häkchen gesetzt ist.

Als reine Zellformel macht meine Formel keinen Sinn (und würde so auch wie von Dir festgestellt nur zu einer Fehlermeldung führen), da Du ja in der Zelle ja Eingaben vornehmen willst und mit dieser nur die von Dir gewünschte Eingaben zulassen willst. Die Formel kannst Du aber auch nicht ohne vorherige Definition als benannte Formel wie angegeben direkt als benutzerdefinierte Datengültigkeitsformel eingeben.

Mehr zum Namensmanager sieh mal hier: https://www.online-excel.de/excel/singsel.php?f=60 und ff.

Mehr zur Datengültigkeit sieh mal hier: https://www.online-excel.de/excel/singsel.php?f=120


Gruß Werner
.. , - ...


  

Betrifft: AW: Formel ist nicht als Zellformel definiert ...
von: Communicator
Geschrieben am: 22.04.2019 18:53:36

Hallo Werner,

da musste ich jetzt aber tüfteln, es ist nicht offensichtlich, dass man in den Namenmanager in das Feld "Bezieht sich auf:" auch eine Formel eingeben kann. Aber es funktioniert prinzipiell. Leider ist es eine durchaus andere Vorgehensweise zu meinen weiteren Datenüberprüfungen. Ich würde das bei späterer Überarbeitung des Arbeitsblattes wahrscheinlich erst mal nicht wiederfinden. Weiterhin werden die Sonderzeichen ?, * und ~ dennoch erlaubt, obwohl nicht als erlaubte Zeichen angegeben. Dies ließe sich wahrscheinlich mit FINDEN anstatt SUCHEN erledigen. Ich habe mich jetzt jedenfalls für die eingans gefundene Lösung entschieden. Eines ist aber klar geworden, es führen oftmals mehrere Wege zum Ziel. Ich danke Dir recht herzlich für Deine Mühe. Ich werde wohl dazu noch einen weiteren Thread einstellen, bezüglich der Möglichkeit, die Datenüberprüfung per VBA den Zellen zuzuordnen, da ich davon mehrere habe. Zuvor will ich aber mal schauen, ob das ggf. wegen verschiedener Excel-Versionen momentan nicht funktionieren will. Einen schönen Rest Ostermontag wünsche ich Dir.


  

Betrifft: AW: zusätzli. die Zeichen ?, *, ~ unterbinden ...
von: neopa C
Geschrieben am: 23.04.2019 12:36:32

Hallo,

... dazu bedarf es in meinem Formelvorschlag (im Namensmanager) nun doch der Funktion FINDEN() anstelle SUCHEN() und die Erweiterung mit der Funktion KLEIN().

Also dann so:

=(ANZAHL(FINDEN(TEIL(KLEIN(!B24);ZEILE(INDEX(!B:B;1):INDEX(!B:B;50));1);
"abcdefghijklmnopqrstuvwxyzäöü0123456789-_"))=50)*(LÄNGE(!B24)<=50)


Dazu ist zu ergänzen, dass meine Formel ohne die Definition einer benannten Formel nicht direkt als Formel in der Datengültigkeit einsetzbar ist.

Es ist natürlich Dir überlassen, was Du letztendlich einsetzt. Zum Einsatz des Namensmanagers würde ich nach wie vor raten, denn die Beherrschung der Möglichkeiten des Namensmanagers hat über diese hier aufgezeigte spez. Anwendung mE eine Reihe von Vorteilen.

Gruß Werner
.. , - ...