09.04.2013, aktualisiert um 15:43:52 Uhr

11555

Excel 2010 - Komplizierte bedingte Formatierung anwenden

Hallo zusammen,

kurze Vorgeschichte. Ich habe in Excel 2 Spalten (Kontonummer, BLZ) mit tausenden von Daten.
Ich habe jetzt gesehen, dass in manchen Werte statt einer Ziffer 0 z.B. der Buchstabe O eingetragen wurde.

Hallo,

ich habe in Excel 2 Spalten (Kontonummer, BLZ) mit tausenden von Daten untereinander stehen.
Ich habe jetzt gesehen, dass in manchen Werte statt einer Ziffer 0 z.B. der Buchstabe O eingetragen wurde. Oder statt eienr 1 ein I eingetragen wurde.
Kann ich irgendwie, eventuell mit der bedingten Formatierung, alle Zellen rot markieren, die einen oder mehrere Buchstaben enthalten (also alles außer Zahlen)?

Beispiel:
BLZ
62040000
64240000
6587777p
6o782345

Die 3. und 4. sollten dann rot farbig hinterlegt sein.

Kann mir bitte jemand sagen, wie ich das hinbekomme`?

Vielen Dank!!

Gruß

Bitte markiere auch die Kommentare, die zur Lösung des Beitrags beigetragen haben

Content-ID: 204709

Url: https://administrator.de/forum/excel-2010-komplizierte-bedingte-formatierung-anwenden-204709.html

Ausgedruckt am: 03.04.2025 um 21:04 Uhr

19 Kommentare

Neuester Kommentar

Hi lordofremixes,

Das könntest du schnell mit VBA machen. ALT-F11 drücken, Code in die entsprechende Tabelle einfügen, den Zell-Bereich noch in Zeile 7 des Codes an deine Gegebenheiten anpassen. Cursor in der Prozedur bewegen und F5 drücken.

Code für inkorrekte deutsche BLZ mit 8 Stellen:

Sub SearchInvalidBLZ()
    Dim myRegExp
    Dim cell As Range
    Set myRegExp = CreateObject("vbscript.regexp")  
    myRegExp.Pattern = "^\d{8}$"  
    myRegExp.IgnoreCase = True
    For Each cell In Range("A2:A10000").Cells  
        If Not myRegExp.Test(cell.Value) Then
            cell.Interior.Color = RGB(255, 0, 0)
        End If
    Next
End Sub

Grüße Uwe

Hallo Uwe!

Kannst du mir eventuell noch sagen, wie ich Zelle 5 anpassen muss, damit er nach alles Buchstaben sucht?

Gruß

Moin,

willst du die Zahlen in Rot haben oder nur herausfinden welche falsch sind?
Da sollte die Sortierfunktion ausreichen.
Oder, da vermutlich nur I und O`s vorkommen (alter Hase hat vermutlich das Sheet gemacht, Vatti machte es auch immer so

)
Suchen und Ersetzen - wunderbare Funktion.

Natürlich auch mit VBA machbar ;)

Zahlen sollen in rot sein (Korrekturen / Abgleich)
Sortierfunktion bringt nichts, da alle Buchstaben a,b,c,, x,y,z, in jeder Position der Zelle stehen kann.
Shit, von VBA hab ich keine Ahnung.

Sorry das war Zeile 7
Einfach den Zellbereich in dem gesucht werden soll angeben, also z.B. mit Range("A1:A5") sucht er in Spalte A von Zeile 1-5. Die ganze Zeile A wird z.B. so ausgewählt: Range("A:A")

For Each cell In Range("A2:A10000").Cells   

den Rest erledigt das Script, es markiert alle Zellen die nicht einer Bankleitzahl entsprechen [also nur 8 DIGITS und keine Buchstaben] (Regex in Zeile 5)

Für die BLZ ist das schon genau die richtige Lösung!

Für die Kontonummer ebenfalls leicht umbaubar !!
Ich gehe davon aus das die Kontonummer maximal 10 DIGITS lang ist.
dazu Zeile 5 so abändern, und deinen Zellen-Range in dem die Kontonummern stehen anpassen:

myRegExp.Pattern = "^\d{1,10}$"

Hallo,

da hat er jetzt alles markiert:

Sub SearchInvalidKontoNr()
Dim myRegExp
Dim cell As Range
Set myRegExp = CreateObject("vbscript.regexp")
myRegExp.Pattern = "^\d{,10}$"
myRegExp.IgnoreCase = True
For Each cell In Range("C2:C10000").Cells
If Not myRegExp.Test(cell.Value) Then
cell.Interior.Color = RGB(255, 0, 0)
End If
Next
End Sub

Sorry da hat eine Zahl gefehlt, habe es oben korrigiert...

Super, danke!!!!!!!!!!!

Für alle die doch nur eine Formel mit einer bedingten Formatierung benutzen können bzw. dürfen, hier ist sie für die Prüfung der BLZ auf unkorrekte Zeichen:
In der Formel den Text "A1" ersetzen durch die Zelle in der die erste Bankleitzahl steht

=WENN(UND(CODE(TEIL(A1;1;1))>=48;CODE(TEIL(A1;1;1))<=57);WENN(UND(CODE(TEIL(A1;2;1))>=48;CODE(TEIL(A1;2;1))<=57);WENN(UND(CODE(TEIL(A1;2;1))>=48;CODE(TEIL(A1;2;1))<=57);WENN(UND(CODE(TEIL(A1;3;1))>=48;CODE(TEIL(A1;3;1))<=57);WENN(UND(CODE(TEIL(A1;4;1))>=48;CODE(TEIL(A1;4;1))<=57);WENN(UND(CODE(TEIL(A1;5;1))>=48;CODE(TEIL(A1;5;1))<=57);WENN(UND(CODE(TEIL(A1;6;1))>=48;CODE(TEIL(A1;6;1))<=57);WENN(UND(CODE(TEIL(A1;7;1))>=48;CODE(TEIL(A1;7;1))<=57);WENN(UND(CODE(TEIL(A1;8;1))>=48;CODE(TEIL(A1;8;1))<=57);FALSCH;WAHR);WAHR);WAHR);WAHR);WAHR);WAHR);WAHR);WAHR);WAHR)

Für bedingte Formatierungen gibt die Formel bei fehlerhafter BLZ WAHR zurück ansonsten FALSCH
(ich krieg gleich nen Klammeraffen)

Grüße Uwe

Min alle,

von Excel habe ich ja nur Grundkenntnisse. Und VBA mit RegEx kenne ich nur aus dem PC-WELT-Sonntagsrätsel.
Aber würde es für diese Lollipop-Anforderung nicht vollkommen reichen, die Bankleitzahl und die Kontonummer so wie vor der Erweiterung der maximalen Formellänge auf 1023 Zeichen mit "=ISTZAHL(A1)" zu prüfen?

Vielleicht bin ich da zu altmodisch...

Grüße
Biber

@Biber
stimmt, da sieht man vor lauter Birnen den Baum nicht mehr

dann muss er aber die Zellen mit einem benutzerdefinierten Format formatieren, denn Kontonummern können auch führende Nullen haben. Denn wenn die Zelle als Text formatiert ist funktioniert deine Formel nicht.

G. Uwe

Moin @colinardo,

Denn wenn die Zelle als Text formatiert ist funktioniert deine Formel nicht

Da geb ich dir recht - habe es gerade probiert.
Eine (Kontonummer) "00000815" in Zelle A1 als Text formatiert liefert bei =ISTZAHL(A1) tatsächlich FALSCH.

Aber auch da kann ich mir helfen. Ich ändere die Formel auf =ISTZAHL(A1*1) und dann passt es wieder.

Und =ISTZAHL(zelle*1) passt sowohl bei BLZ wie Kontonummern.

Grüße
Biber

[Edit] P.S. Und wenn die Zusatzbedingung bei BLZ und/oder Kontonummern eine exakte Länge, z.B. 8 Zeichen sein muss, dann eben ergänzen auf
=ISTZAHL(zelle*1)*Länge(Glätten(zelle))=8

...dann bliebe nur die Lücke, das ein gehässiger User ein Plus/Minuszeichen mit eingeben hat oder eine Kontonummer "22222^22" oder ähnlich Gemeines.
[/Edit]

Hallo Biber,

das hatte ich auch erst, kam mir aber irgendwie zu einfach vor..

In meinem Fall wäre dass dann wohl, =ISTZAHL($A1), aber ich kann dir eins dazu sagen (also zumindest in unserem Fall):
Die Formel scheint bei 2000 Datensätze einen "an der Waffel" zu haben, es gibt Datensätze, die zu 100 % korrekt sind und markiert werden, und widerum Falsche, werden ganz normal markiert.
Wollt ich nur mal kurz loswerden...

wobei ich

=ISTZAHL($A1*1)*Länge(Glätten(zelle))=8

noch nicht ausprobiert habe

Moin lordofremixes,

na ja, ich gebe zu, ich würde auch (wenn es mein Excel-Sheet wäre) natürlich eine wasserdichte Prüfung einbauen (also meinetwegen genau 8 Stellen lang und nur aus Ziffern bestehend).

@colinardos Formel von heute, 09.04.2013 um 17:11 Uhr kann ich bei meinem ollen Excel 2002 nicht nehmen, da sagt dat Dingen mir was von "zu großer Verschachtelungstiefe".

Aber auch dafür habe ich einen Workaround.

Wenn ich alle Zellen per "Bedingter Formatierung" in "Grün" markieren wollte, die eine Länge von 8 Zeichen nur aus Ziffern bestehend haben, dann würde ich den Bereich der BLZ/Kontonummern markieren (Beispiel: von $A$2:$A4000) und als Formel eingeben:

=SUMME((CODE(TEIL(A2&"MfGBiber";ZEILE(A$1:$A$8);1))>=48)*(CODE(TEIL(A2&"MfGBiber";ZEILE(A$1:$A$8);1))<=57))=8

... und als Muster "Grün" wählen
Oder alternativ

=SUMME((CODE(TEIL(A2&"MfGBiber";ZEILE(A$1:$A$8);1))>=48)*(CODE(TEIL(A2&"MfGBiber";ZEILE(A$1:$A$8);1))<=57))<>8

... und als Muster "Rot" wählen

Falls nicht über "Bedingte Formatierung", sondern in einer separaten Spalte als WAHR/FALSCH angezeigt werden soll, dann muss es als Matrixformel eingegeben werden.

Heispiel: in Hilfsspalte "Z" in Zelle "Z2" eingeben:

=SUMME((CODE(TEIL(A2&"MfGBiber";ZEILE(A$1:$A$8);1))>=48)*(CODE(TEIL(A2&"MfGBiber";ZEILE(A$1:$A$8);1))<=57))=8

... mit Strg-Shift-Enter bestätigen und anch unten kopieren.

Wenn es richtig ist, wird die Formel angezeigt als

{=SUMME((CODE(TEIL(A2&"MfGBiber";ZEILE(A$1:$A$8);1))>=48)*(CODE(TEIL(A2&"MfGBiber";ZEILE(A$1:$A$8);1))<=57))=8}

Falls ihr eine kürzere und ebenso wasserdichte Formel-Prüfung habt, bin ich für Ideen offen.

Grüße
Biber

[Edit]
P.S. Habe inzwischen selbst eine kürzere Matrixformel gefunden für die o.g. Prüfung:

=SUMME(--ISTZAHL(1*TEIL(A2&"MfGBiber";ZEILE(A$1:$A$8);1)))=8  

bzw. für "--"-Meider

=SUMME(1*ISTZAHL(1*TEIL(A2&"MfGBiber";ZEILE(A$1:$A$8);1)))=8  

Wieder mit Strg-Shift-Enter eingeben (wenn nicht als "Bedingte Formatierung" verwendet).

Irgendwann muss ich mal mit diesem Excel-zeugs auseinandersetzen, glaube ich.

[/Edit]

Hallo Biber,
die Lösungen werden ich morgen alle ausprobieren (habe leider die Datei grad nicht zur Hand) und ich finde die Lösungen auch höchst informativ ^^

Fakt ist aber auch, (ist mir leider erst jetzt eingefallen) , BLZ in D ok, 8 stellig. aber international? und Kontonummer, gibt es da wegen der Normierung über Regeln?
Ich klär das morgen früh gleich ab und dann probiere ich alles aus.
Die Lösungen sind aber auf den ersten Blick echt spitze.

Danke schonmal

Moin lordofremixes,

ich weiss nicht, ob du dich noch erinnerst... neulich trafen sich hier zu deinem Problem ein paar hilfsbereite User.
Das kurze Brainstorming brach vor drei Tagen ab, als einer der Plauderer schrieb "..die Lösungen werden ich morgen alle ausprobieren... .

Seitdem ist es hier ziemlich ruhig.
Gibt es denn noch Bewegung?

Grüße
Biber

Frage Microsoft Microsoft Office

Mehr von lordofremixes

Crowdstrike Problem - best Practise gesuchtlordofremixes - 15 Kommentare

ExtensionAttribute1 umschreibenlordofremixes - 6 Kommentare

Einer alten Cent OS VM einen forward proxy mit IP und Port beibringenlordofremixes - 1 Kommentar

AD extensionAttribute auslesen, bearbeiten und neu schreibenlordofremixes - 6 Kommentare

Heiß diskutiert