Wir wissen, dass die Excel-Funktionen TRIM und CLEAN verwendet werden, um nicht druckbare Zeichen und zusätzliche Leerzeichen aus Strings zu entfernen, aber sie helfen nicht viel bei der Identifizierung von Strings, die Sonderzeichen wie @ oder enthalten! usw. In solchen Fällen verwenden wir UDFs.
Wenn Sie also wissen möchten, ob eine Zeichenfolge Sonderzeichen enthält, finden Sie keine Excel-Formel oder -Funktion dafür. Wir können die Sonderzeichen, die sich auf der Tastatur befinden, durch manuelle Eingabe finden, aber Sie können nicht wissen, ob die Zeichenfolge Symbole enthält oder nicht.
Das Auffinden von Sonderzeichen kann für die Datenbereinigung sehr wichtig sein. Und in einigen Fällen muss es getan werden. Wie machen wir das in Excel? Wie können wir wissen, ob eine Zeichenfolge Sonderzeichen enthält? Nun, wir können UDF verwenden, um dies zu tun.
Die folgende Formel gibt TRUE zurück, wenn eine Zelle andere Zeichen als 1 bis 0 und A bis Z enthält (in beiden Fällen). Wenn keine Sonderzeichen gefunden werden, wird FALSE zurückgegeben.
Generische Formel
| =Enthält Sonderzeichen(string) |
Zeichenfolge: Die Zeichenfolge, die Sie auf Sonderzeichen überprüfen möchten.
Damit diese Formel funktioniert, müssen Sie den folgenden Code in das Modul Ihrer Arbeitsmappe einfügen.
Also Excel öffnen. Drücken Sie ALT+F11, um VBE zu öffnen. Fügen Sie ein Modul aus dem Menü Einfügen ein. Kopieren Sie den folgenden Code und fügen Sie ihn in das Modul ein.
Funktion EnthältSpecialCharacters(str As String) As Boolean For I = 1 To Len(str) ch = Mid(str, I, 1) Select Case ch Case "0" To "9", "A" To "Z", "a " Bis "z", " " EnthältSpecialCharacters = False Case Else ContainsSpecialCharacters = True Exit For End Select Next End Function
Jetzt ist die Funktion einsatzbereit.
Wechseln Sie zu dem Arbeitsblatt in der Arbeitsmappe, das die Zeichenfolgen enthält, die Sie überprüfen möchten.
Schreiben Sie die folgende Formel in Zelle C2:
| =Enthält Sonderzeichen(B13) |
Es gibt TRUE für den ersten String zurück, da es ein Sonderzeichen enthält. Wenn Sie die Formel nach unten kopieren, wird FALSE für B14-String usw. angezeigt.
Aber seltsamerweise zeigt es TRUE für die letzte Zeichenfolge "Exceltip.com". Dies liegt daran, dass es einen Punkt (.) enthält. Aber warum so? Lassen Sie uns den Code untersuchen, um ihn zu verstehen.
Wie funktioniert die Funktion?
Wir durchlaufen alle Zeichen der Zeichenfolge mit der For-Schleife und der Mid-Funktion von VBA. Die Mid-Funktion extrahiert jeweils ein Zeichen aus dem String und speichert es in der ch-Variablen.
For I = 1 To Len(str) ch = Mid(str, I, 1)
Jetzt kommt der Hauptteil. Wir verwenden die select case-Anweisung, um zu überprüfen, was die Variable ch enthält.
Wir weisen VBA an, zu überprüfen, ob ch einen der definierten Werte enthält. Ich habe 0 bis 9, A bis Z, a bis z und " " (Leerzeichen) definiert. Wenn c
h einen dieser Werte enthält, setzen wir seinen Wert auf False.
Select Case ch Case "0" bis "9", "A" bis "Z", "a" bis "z", " " EnthältSpecialCharacters = False
Sie können sehen, dass wir keinen Punkt (.) in der Liste haben und deshalb erhalten wir TRUE für die Zeichenfolge, die einen Punkt enthält. Sie können der Liste jedes beliebige Zeichen hinzufügen, das von der Formel ausgenommen werden soll.
Wenn ch ein anderes als die aufgelisteten Zeichen enthält, setzen wir das Ergebnis der Funktion auf True und beenden die Schleife genau dort.
Fall sonst enthältSpecialCharacters = True Exit For
Also ja, so funktioniert es. Wenn Sie jedoch Sonderzeichen bereinigen möchten, müssen Sie einige Änderungen an der Funktion vornehmen.
So bereinigen Sie Sonderzeichen aus Strings in Excel?
Um Sonderzeichen aus einer Zeichenfolge in Excel zu bereinigen, habe ich eine weitere benutzerdefinierte Funktion in VBA erstellt. Die Syntax der Funktion lautet:
| =CleanSpecialCharacters(string) |
Diese Funktion gibt eine bereinigte Version des übergebenen Strings zurück. Die neue Zeichenfolge enthält keine der Sonderzeichen.
Der Code dieser Funktion sieht so aus:
Funktion CleanSpecialCharacters(str As String) Dim nstr As String nstr = str For I = 1 To Len(str) ch = Mid(str, I, 1) Select Case ch Case "0" To "9", "A" To " Z", "a" To "z", " " 'Nichts tun Case Else nstr = Replace(nstr, ch, "") End Select Next CleanSpecialCharacters = nstr End Function
Kopieren Sie dies in das gleiche Modul, das Sie im obigen Code kopiert haben.
Wenn Sie diese Formel für die Zeichenfolgen verwenden, sehen die Ergebnisse folgendermaßen aus:
Sie können sehen, dass jedes Sonderzeichen einschließlich des Punkts aus der Zeichenfolge entfernt wird.
Wie funktioniert es?
Es funktioniert genauso wie die obige Funktion. Der einzige Unterschied besteht darin, dass diese Funktion jedes Sonderzeichen durch ein Nullzeichen ersetzt. Bildet einen neuen String und gibt diesen String zurück.
Select Case ch Case "0" Bis "9", "A" To "Z", "a" To "z", " " 'ContainsSpecialCharacters = False Case Else nstr = Replace(nstr, ch, "") End Select
Wenn Sie ein Zeichen von der Reinigung ausnehmen möchten, können Sie es der Liste im Code hinzufügen. Excel verzeiht dieses Sonderzeichen.
Also ja Leute, so können Sie Sonderzeichen aus einer Zeichenfolge in Excel finden und ersetzen. Ich hoffe, das war erklärend genug und hilfreich. Wenn es Ihnen bei der Lösung Ihres Problems nicht hilft, teilen Sie uns dies im Kommentarbereich unten mit.
So verwenden Sie die TRIM-Funktion in Excel: Die TRIM-Funktion wird verwendet, um Zeichenfolgen zu trimmen und alle nachfolgenden oder führenden Leerzeichen von Zeichenfolgen zu entfernen. Dies hilft uns sehr bei der Bereinigung der Daten.
So verwenden Sie die CLEAN-Funktion in Excel: Die Clean-Funktion wird verwendet, um nicht druckbare Zeichen aus der Zeichenfolge zu entfernen. Diese Funktion wird meistens mit der TRIM-Funktion verwendet, um importierte Fremddaten zu bereinigen.
Text am Ende einer Zeichenfolge ersetzen, beginnend mit variabler Position: Um Text am Ende des Strings zu ersetzen, verwenden wir die REPLACE-Funktion. Die REPLACE-Funktion verwendet die Position von Text in der Zeichenfolge, um ihn zu ersetzen.
So überprüfen Sie, ob eine Zeichenfolge einen von vielen Texten in Excel enthält: Um zu überprüfen, ob eine Zeichenfolge mehrere Texte enthält, verwenden wir diese Formel. Wir verwenden die SUM-Funktion, um alle Übereinstimmungen zu summieren, und führen dann eine Logik durch, um zu überprüfen, ob die Zeichenfolge eine der mehreren Zeichenfolgen enthält.
Zellen zählen, die bestimmten Text enthalten: Eine einfache ZÄHLENWENN-Funktion wird die Magie bewirken. Um die Anzahl mehrerer Zellen zu zählen, die eine bestimmte Zeichenfolge enthalten, verwenden wir den Platzhalteroperator mit der Funktion ZÄHLENWENN.
Excel ERSETZEN vs. ERSATZ-Funktion: Die Funktionen REPLACE und SUBSTITUTE sind die am meisten missverstandenen Funktionen. Um einen bestimmten Text zu finden und zu ersetzen, verwenden wir die Funktion SUBSTITUTE. Wo REPLACE verwendet wird, um eine Reihe von Zeichen in einer Zeichenfolge zu ersetzen …
Populäre Artikel:
50 Excel-Kurzbefehle zur Steigerung Ihrer Produktivität | Werden Sie schneller bei Ihrer Aufgabe. Mit diesen 50 Tastenkombinationen arbeiten Sie noch schneller mit Excel.
So verwenden Sie die Excel SVERWEIS-Funktion| Dies ist eine der am häufigsten verwendeten und beliebtesten Funktionen von Excel, die verwendet wird, um Werte aus verschiedenen Bereichen und Blättern zu suchen.
So verwenden Sie Excel ZÄHLENWENN-Funktion| Zählen Sie Werte mit Bedingungen mit dieser erstaunlichen Funktion. Sie müssen Ihre Daten nicht filtern, um bestimmte Werte zu zählen. Die Countif-Funktion ist unerlässlich, um Ihr Dashboard vorzubereiten.
So verwenden Sie die SUMIF-Funktion in Excel | Dies ist eine weitere wesentliche Funktion des Dashboards. Dies hilft Ihnen, Werte unter bestimmten Bedingungen zusammenzufassen.