Comment trouver des caractères spéciaux dans une chaîne dans Excel
Nous savons que link: / excel-text-formules-excel-trim-function [TRIM]
et link: / excel-text-formulas-comment-utiliser-la-fonction-de-nettoyage-dans-Excel [CLEAN]
Les fonctions Excel sont utilisées pour nettoyer les caractères non imprimables et les espaces supplémentaires des chaînes, mais elles n’aident pas beaucoup à identifier les chaînes contenant un caractère spécial comme @ ou! etc. Dans de tels cas, nous utilisons des UDF.
Donc, si vous voulez savoir si une chaîne contient des caractères spéciaux, vous ne trouverez aucune formule ou fonction Excel pour le faire. Nous pouvons trouver les caractères spéciaux qui se trouvent sur le clavier en tapant manuellement mais vous ne pouvez pas savoir s’il y a des symboles dans la chaîne ou non.
La recherche de caractères spéciaux peut être très importante pour le nettoyage des données. Et dans certains cas, cela doit être fait. Alors, comment pouvons-nous faire cela dans Excel? Comment savoir si une chaîne contient des caractères spéciaux?
Eh bien, nous pouvons utiliser UDF pour le faire. La formule ci-dessous renverra TRUE si une cellule contient des caractères autres que 1 à 0 et A à Z (dans les deux cas). S’il ne trouve aucun caractère spécial, il retournera FALSE.
Formule générique
|
=ContainsSpecialCharacters(string) |
Chaîne: la chaîne dont vous souhaitez vérifier les caractères spéciaux.
Pour que cette formule fonctionne, vous devrez mettre le code ci-dessous dans le module de votre classeur.
Alors ouvrez Excel. Appuyez sur ALT + F11 pour ouvrir VBE. Insérez un module à partir du menu Insertion. Copiez le code ci-dessous et collez-le dans le module.
Function ContainsSpecialCharacters(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" To "z", " " ContainsSpecialCharacters = False Case Else ContainsSpecialCharacters = True Exit For End Select Next End Function
La fonction est maintenant prête à être utilisée.
Accédez à la feuille de calcul dans le classeur qui contient les chaînes que vous souhaitez vérifier.
Écrivez la formule ci-dessous dans la cellule C2:
|
=ContainsSpecialCharacters(B13) |
Il renvoie TRUE pour la première chaîne car elle contient un caractère spécial. Lorsque vous copiez la formule vers le bas, elle affiche FALSE pour la chaîne B14 et ainsi de suite. Mais étrangement, il montre TRUE pour la dernière chaîne « Exceltip.com ». C’est parce qu’il contient un point (.). Mais pourquoi cela? Examinons le code pour comprendre.
Comment fonctionne la fonction?
Nous parcourons tous les caractères de la chaîne en utilisant le link: / vba-for-loops-with-7-examples [For loop]
et fonction intermédiaire de VBA. La fonction Mid extrait un caractère à la fois de la chaîne et la stocke dans la variable ch.
For I = 1 To Len(str) ch = Mid(str, I, 1)
Maintenant, la partie principale vient. Nous utilisons l’instruction select case pour vérifier ce que contient la variable ch.
Nous demandons à VBA de vérifier si ch contient l’une des valeurs définies. J’ai défini 0 à 9, A à Z, a à z et « » (espace). Si c h contient l’un de ces éléments, nous définissons sa valeur sur False.
Select Case ch Case "0" To "9", "A" To "Z", "a" To "z", " " ContainsSpecialCharacters = False
Vous pouvez voir que nous n’avons pas de point (.) Sur la liste et c’est pourquoi nous obtenons TRUE pour la chaîne qui contient le point. Vous pouvez ajouter n’importe quel caractère à la liste pour être exempté de la formule.
Si ch contient un caractère autre que les caractères listés, nous définissons le résultat de la fonction sur True et terminons la boucle ici.
Case Else ContainsSpecialCharacters = True Exit For
Alors oui, voici comment ça marche. Mais si vous souhaitez nettoyer des caractères spéciaux, vous devrez apporter des modifications à la fonction.
Comment nettoyer les caractères spéciaux des chaînes dans Excel? Pour nettoyer les caractères spéciaux d’une chaîne dans Excel, j’ai créé une autre fonction personnalisée dans VBA. La syntaxe de la fonction est:
|
=CleanSpecialCharacters(string) |
Cette fonction renvoie une version nettoyée de la chaîne qui y est passée.
La nouvelle chaîne ne contiendra aucun des caractères spéciaux. Le code de cette fonction va comme ceci:
Function 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", " " 'Do nothing Case Else nstr = Replace(nstr, ch, "") End Select Next CleanSpecialCharacters = nstr End Function
Copiez-le dans le même module que vous avez copié dans le code ci-dessus.
Lorsque vous utilisez cette formule sur les chaînes, voici comment les résultats seront:
Vous pouvez voir que chaque caractère spécial est supprimé de la chaîne, y compris le point.
Comment ça marche?
Cela fonctionne de la même manière que la fonction ci-dessus. La seule différence est que cette fonction remplace chaque caractère spécial par un caractère nul. Forme une nouvelle chaîne et renvoie cette chaîne.
Select Case ch Case "0" To "9", "A" To "Z", "a" To "z", " " 'ContainsSpecialCharacters = False Case Else nstr = Replace(nstr, ch, "") End Select
Si vous souhaitez empêcher un caractère d’être nettoyé, vous pouvez l’ajouter à la liste dans le code. Excel pardonnera ce caractère spécial.
Alors oui les gars, voici comment vous pouvez trouver et remplacer les caractères spéciaux d’une chaîne dans Excel. J’espère que c’était suffisamment explicatif et utile.
Si cela ne vous aide pas à résoudre votre problème, faites-le nous savoir dans la section commentaires ci-dessous.
Articles liés:
link: / excel-text-formulas-excel-trim-function [Comment utiliser la fonction TRIM dans Excel]: * La fonction TRIM est utilisée pour couper les chaînes et nettoyer les espaces de fin ou de début de chaîne. Cela nous aide beaucoup dans le processus de nettoyage des données.
link: / excel-text-formulas-how-to-use-the-clean-function-in-excel [Comment utiliser la fonction CLEAN dans Excel]: La fonction Clean est utilisée pour nettoyer les caractères non imprimables de la chaîne. Cette fonction est principalement utilisée avec la fonction TRIM pour nettoyer les données étrangères importées.
link: / excel-text-replace-text-from-end-of-a-string-from-variable-position [Remplacer le texte de la fin d’une chaîne à partir de la position de la variable]: * Pour remplacer le texte de la fin de la chaîne, nous utilisons la fonction REPLACE.
La fonction REPLACE utilise la position du texte dans la chaîne pour le remplacer.
link: / excel-text-formulas-check-a-list-of-text-in-string [Comment vérifier si une chaîne contient l’un des nombreux textes dans Excel]: * Pour trouver, vérifiez si une chaîne contient l’un des texte multiple, nous utilisons cette formule. Nous utilisons la fonction SOMME pour résumer toutes les correspondances, puis exécutons une logique pour vérifier si la chaîne contient l’une des multiples chaînes.
link: / counting-count-cells-that-contain-specific-text [Count Cells that contain specific text]: * Une simple fonction COUNTIF fera la magie. Pour compter le nombre de cellules multiples contenant une chaîne donnée, nous utilisons l’opérateur générique avec la fonction COUNTIF.
link: / excel-text-formulas-excel-replace-vs-substitute-function [Excel REPLACE vs SUBSTITUTE function]: * Les fonctions REPLACE et SUBSTITUTE sont les fonctions les plus mal comprises. Pour rechercher et remplacer un texte donné, nous utilisons la fonction SUBSTITUTE. Où REPLACE est utilisé pour remplacer un certain nombre de caractères dans la chaîne…
Articles populaires:
lien: / clavier-formule-raccourcis-50-excel-raccourcis-pour-augmenter-votre-productivité [50 raccourcis Excel pour augmenter votre productivité] | Accélérez votre tâche. Ces 50 raccourcis vous permettront de travailler encore plus rapidement sur Excel.
lien: / formules-et-fonctions-introduction-de-vlookup-function [Comment utiliser la fonction RECHERCHEV Excel] | C’est l’une des fonctions les plus utilisées et les plus populaires d’Excel qui est utilisée pour rechercher des valeurs à partir de différentes plages et feuilles. link: / tips-countif-in-microsoft-excel [Comment utiliser le]
lien: / formules-et-fonctions-introduction-de-vlookup-function [Excel]
lien: / tips-countif-in-microsoft-excel [Fonction COUNTIF] | Comptez les valeurs avec des conditions en utilisant cette fonction étonnante.
Vous n’avez pas besoin de filtrer vos données pour compter des valeurs spécifiques. La fonction Countif est indispensable pour préparer votre tableau de bord.
lien: / excel-formule-et-fonction-excel-sumif-function [Comment utiliser la fonction SUMIF dans Excel] | C’est une autre fonction essentielle du tableau de bord. Cela vous aide à résumer les valeurs sur des conditions spécifiques.