Top-Themen

AppleEntwicklungHardwareInternetLinuxMicrosoftMultimediaNetzwerkeOff TopicSicherheitSonstige SystemeVirtualisierungWeiterbildungZusammenarbeit

Aktuelle Themen

Administrator.de FeedbackApache ServerAppleAssemblerAudioAusbildungAuslandBackupBasicBatch & ShellBenchmarksBibliotheken & ToolkitsBlogsCloud-DiensteClusterCMSCPU, RAM, MainboardsCSSC und C++DatenbankenDatenschutzDebianDigitiales FernsehenDNSDrucker und ScannerDSL, VDSLE-BooksE-BusinessE-MailEntwicklungErkennung und -AbwehrExchange ServerFestplatten, SSD, RaidFirewallFlatratesGoogle AndroidGrafikGrafikkarten & MonitoreGroupwareHardwareHosting & HousingHTMLHumor (lol)Hyper-VIconsIDE & EditorenInformationsdiensteInstallationInstant MessagingInternetInternet DomäneniOSISDN & AnaloganschlüsseiTunesJavaJavaScriptKiXtartKVMLAN, WAN, WirelessLinuxLinux DesktopLinux NetzwerkLinux ToolsLinux UserverwaltungLizenzierungMac OS XMicrosoftMicrosoft OfficeMikroTik RouterOSMonitoringMultimediaMultimedia & ZubehörNetzwerkeNetzwerkgrundlagenNetzwerkmanagementNetzwerkprotokolleNotebook & ZubehörNovell NetwareOff TopicOpenOffice, LibreOfficeOutlook & MailPapierkorbPascal und DelphiPeripheriegerätePerlPHPPythonRechtliche FragenRedHat, CentOS, FedoraRouter & RoutingSambaSAN, NAS, DASSchriftartenSchulung & TrainingSEOServerServer-HardwareSicherheitSicherheits-ToolsSicherheitsgrundlagenSolarisSonstige SystemeSoziale NetzwerkeSpeicherkartenStudentenjobs & PraktikumSuche ProjektpartnerSuseSwitche und HubsTipps & TricksTK-Netze & GeräteUbuntuUMTS, EDGE & GPRSUtilitiesVB for ApplicationsVerschlüsselung & ZertifikateVideo & StreamingViren und TrojanerVirtualisierungVisual StudioVmwareVoice over IPWebbrowserWebentwicklungWeiterbildungWindows 7Windows 8Windows 10Windows InstallationWindows MobileWindows NetzwerkWindows ServerWindows SystemdateienWindows ToolsWindows UpdateWindows UserverwaltungWindows VistaWindows XPXenserverXMLZusammenarbeit

Excel 2010 - Komplizierte bedingte Formatierung anwenden

Frage Microsoft Microsoft Office

Mitglied: lordofremixes

lordofremixes (Level 1) - Jetzt verbinden

09.04.2013, aktualisiert 15:43 Uhr, 6991 Aufrufe, 19 Kommentare

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ß
Mitglied: colinardo
09.04.2013, aktualisiert um 16:16 Uhr
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:
01.
Sub SearchInvalidBLZ() 
02.
    Dim myRegExp 
03.
    Dim cell As Range 
04.
    Set myRegExp = CreateObject("vbscript.regexp") 
05.
    myRegExp.Pattern = "^\d{8}$" 
06.
    myRegExp.IgnoreCase = True 
07.
    For Each cell In Range("A2:A10000").Cells 
08.
        If Not myRegExp.Test(cell.Value) Then 
09.
            cell.Interior.Color = RGB(255, 0, 0) 
10.
        End If 
11.
    Next 
12.
End Sub
Grüße Uwe
Bitte warten ..
Mitglied: lordofremixes
09.04.2013 um 16:05 Uhr
Hallo Uwe!

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

Gruß
Bitte warten ..
Mitglied: Ravers
09.04.2013 um 16:05 Uhr
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 ;)
Bitte warten ..
Mitglied: lordofremixes
09.04.2013 um 16:09 Uhr
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.
Bitte warten ..
Mitglied: colinardo
09.04.2013, aktualisiert um 16:14 Uhr
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")
01.
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)
Bitte warten ..
Mitglied: lordofremixes
09.04.2013 um 16:37 Uhr
Für die BLZ ist das schon genau die richtige Lösung!
Bitte warten ..
Mitglied: colinardo
09.04.2013, aktualisiert um 17:01 Uhr
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}$"
Bitte warten ..
Mitglied: lordofremixes
09.04.2013 um 16:59 Uhr
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
Bitte warten ..
Mitglied: colinardo
09.04.2013 um 17:02 Uhr
Sorry da hat eine Zahl gefehlt, habe es oben korrigiert...
Bitte warten ..
Mitglied: lordofremixes
09.04.2013 um 17:05 Uhr
Super, danke!!!!!!!!!!!
Bitte warten ..
Mitglied: colinardo
09.04.2013, aktualisiert um 17:26 Uhr
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
Bitte warten ..
Mitglied: Biber
09.04.2013 um 17:57 Uhr
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
Bitte warten ..
Mitglied: colinardo
09.04.2013 um 19:40 Uhr
@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
Bitte warten ..
Mitglied: Biber
09.04.2013, aktualisiert um 21:24 Uhr
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]
Bitte warten ..
Mitglied: lordofremixes
09.04.2013 um 20:39 Uhr
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...
Bitte warten ..
Mitglied: lordofremixes
09.04.2013 um 20:42 Uhr
wobei ich
=ISTZAHL($A1*1)*Länge(Glätten(zelle))=8

noch nicht ausprobiert habe
Bitte warten ..
Mitglied: Biber
09.04.2013, aktualisiert um 21:24 Uhr
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:
01.
=SUMME(--ISTZAHL(1*TEIL(A2&"MfGBiber";ZEILE(A$1:$A$8);1)))=8
bzw. für "--"-Meider
01.
=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]
Bitte warten ..
Mitglied: lordofremixes
09.04.2013 um 21:38 Uhr
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
Bitte warten ..
Mitglied: Biber
12.04.2013 um 15:48 Uhr
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
Bitte warten ..
Neuester Wissensbeitrag
Windows 10

Powershell 5 BSOD

(8)

Tipp von agowa338 zum Thema Windows 10 ...

Ähnliche Inhalte
Microsoft Office
gelöst EXCEL Bedingte Formatierung wenn bestimmtes Jahr im Datum ist (6)

Frage von Hobi84 zum Thema Microsoft Office ...

Microsoft Office
gelöst Excel bedingte Formatierung per Formel mit übernehmen (16)

Frage von Florian86 zum Thema Microsoft Office ...

Microsoft Office
gelöst Excel 2010 Zellen mit bestimmten Inhalt mit Makro formartierten (5)

Frage von packmann2016 zum Thema Microsoft Office ...

Microsoft Office
Excel 2010 - Microsoft Excel kann die Daten nicht kopieren (4)

Frage von EDV-Oellerking zum Thema Microsoft Office ...

Heiß diskutierte Inhalte
Microsoft
Ordner mit LW-Buchstaben versehen und benennen (20)

Frage von Xaero1982 zum Thema Microsoft ...

Outlook & Mail
gelöst Outlook 2010 findet ost datei nicht (19)

Frage von Floh21 zum Thema Outlook & Mail ...

Netzwerkmanagement
gelöst Anregungen, kleiner Betrieb, IT-Umgebung (18)

Frage von Unwichtig zum Thema Netzwerkmanagement ...

Festplatten, SSD, Raid
M.2 SSD wird nicht erkannt (14)

Frage von uridium69 zum Thema Festplatten, SSD, Raid ...