Știm că funcțiile TRIM și CLEAN Excel sunt folosite pentru a curăța caractere neimprimabile și spații suplimentare din șiruri, dar nu ajută prea mult la identificarea șirurilor care conțin caractere speciale precum @ sau! În astfel de cazuri folosim UDF-uri.
Deci, dacă doriți să știți dacă un șir conține caractere speciale, nu veți găsi nicio formulă sau funcție Excel pentru a face acest lucru. Putem găsi caracterele speciale care sunt pe tastatură tastând manual, dar nu puteți ști dacă există sau nu simboluri în șir.
Găsirea unor caractere speciale poate fi foarte importantă în scopul curățării datelor. Și, în unele cazuri, trebuie făcut. Deci, cum facem acest lucru în Excel? Cum putem ști dacă un șir conține caractere speciale? Ei bine, putem folosi UDF pentru a face acest lucru.
Formula de mai jos va reveni ADEVĂRAT dacă orice celulă conține alte caractere decât 1 la 0 și de la A la Z (în ambele cazuri). Dacă nu găsește caractere speciale, va reveni FALS.
Formula generică
| = ContainsSpecialCharacters (șir) |
Şir: Șirul pe care doriți să îl verificați pentru caracterele speciale.
Pentru ca această formulă să funcționeze, va trebui să puneți codul de mai jos în modulul registrului dvs. de lucru.
Deci, deschideți Excel. Apăsați ALT + F11 pentru a deschide VBE. Introduceți un modul din meniul Inserare. Copiați codul de mai jos și lipiți-l în modul.
Funcția conține caractere speciale (str As String) Ca boolean pentru I = 1 To Len (str) ch = Mid (str, I, 1) Selectați Case ch Case "0" To "9", "A" To "Z", "a „Până la„ z ”,„ „ContainsSpecialCharacters = False Case Altse ContainsSpecialCharacters = True Exit For End Select Next End Function
Acum funcția este gata de utilizare.
Mergeți la foaia de lucru din registrul de lucru care conține șirurile pe care doriți să le verificați.
Scrieți formula de mai jos în celula C2:
| = Conține caractere speciale (B13) |
Returnează TRUE pentru primul șir, deoarece conține un caracter special. Când copiați formula în jos, apare FALSE pentru șirul B14 și așa mai departe.
Dar, în mod ciudat, arată ADEVĂRAT pentru ultimul șir „Exceltip.com”. Acest lucru se datorează faptului că conține un punct (.). Dar de ce da? Să examinăm codul pentru a înțelege.
Cum funcționează funcția?
Parcurgem toate caracterele șirului folosind funcția For loop și mid a VBA. Funcția Mid extrage câte un caracter din șir și îl stochează în variabila ch.
Pentru I = 1 To Len (str) ch = Mid (str, I, 1)
Acum vine partea principală. Folosim declarația select case pentru a verifica ce conține variabila ch.
Îi spunem VBA să verifice dacă ch conține oricare dintre valorile definite. Am definit de la 0 la 9, de la A la Z, de la A la Z și „” (spațiu). Dacă c
h conține oricare dintre acestea, am setat valoarea sa la False.
Selectați majuscule majuscule „0” la „9”, „A” la „Z”, „a„ La „z”, „„ ContainsSpecialCharacters = False
Puteți vedea că nu avem un punct (.) Pe listă și de aceea obținem ADEVĂRAT pentru șirul care conține punct. Puteți adăuga orice caracter la listă pentru a fi scutit de formulă.
Dacă ch conține orice alt caracter decât caracterele listate, setăm rezultatul funcției la True și încheiem bucla chiar acolo.
Case Alse ContainsSpecialCharacters = Ieșire adevărată pentru
Deci da, așa funcționează. Dar dacă doriți să curățați caractere speciale, va trebui să faceți câteva modificări în funcție.
Cum se curăță caracterele speciale de șiruri în Excel?
Pentru a curăța caractere speciale dintr-un șir în Excel, am creat o altă funcție personalizată în VBA. Sintaxa funcției este:
| = CleanSpecialCharacters (șir) |
Această funcție returnează o versiune curată a șirului trecut în ea. Noul șir nu va conține niciunul dintre caracterele speciale.
Codul acestei funcții merge astfel:
Funcție CleanSpecialCharacters (str As String) Dim nstr As String nstr = str For I = 1 To Len (str) ch = Mid (str, I, 1) Selectați Case ch Case "0" To "9", "A" To " Z "," a "Către" z "," "'Nu faceți nimic Altceva nstr = Înlocuiți (nstr, ch," ") Sfârșit Selectați Următorul CleanSpecialCharacters = nstr Funcție de sfârșit
Copiați acest lucru în același modul pe care l-ați copiat în codul de mai sus.
Când utilizați această formulă pe șiruri, așa vor fi rezultatele:
Puteți vedea că fiecare caracter special este eliminat din șir, inclusiv punctul.
Cum functioneazã?
Funcționează la fel ca funcția de mai sus. Singura diferență este că această funcție înlocuiește fiecare caracter special cu un caracter nul. Formează un șir nou și returnează acest șir.
Selectați Case majuscule Case "0" Până la "9", "A" Până la "Z", "a" Până la "z", "" 'ContainsSpecialCharacters = False Case Altele nstr = Înlocuiți (nstr, ch, "") Sfârșit Selectați
Dacă doriți să scutiți orice personaj de la curățare, îl puteți adăuga la lista din cod. Excel va ierta acel caracter special.
Deci, băieți, așa puteți găsi și înlocui caractere speciale dintr-un șir în Excel. Sper că acest lucru a fost suficient de explicativ și util. Dacă nu vă ajută să rezolvați problema, anunțați-ne în secțiunea de comentarii de mai jos.
Cum se folosește funcția TRIM în Excel: Funcția TRIM este utilizată pentru tăierea șirurilor și curățarea oricărui spațiu final sau de la șir. Acest lucru ne ajută foarte mult în procesul de curățare a datelor.
Cum se utilizează funcția CLEAN în Excel: Funcția de curățare este utilizată pentru a curăța caractere neprimabile din șir. Această funcție este utilizată mai ales cu funcția TRIM pentru a curăța datele străine importate.
Înlocuiți textul de la capătul unui șir începând de la poziția variabilă: Pentru a înlocui textul de la sfârșitul șirului, folosim funcția REPLACE. Funcția REPLACE folosește poziția textului în șir pentru a-l înlocui.
Cum să verificați dacă un șir conține unul dintre textele din Excel: Pentru a afla dacă un șir conține mai multe texte, folosim această formulă. Folosim funcția SUM pentru a însuma toate meciurile și apoi efectuăm o logică pentru a verifica dacă șirul conține oricare dintre șirurile multiple.
Numărați celulele care conțin text specific: O funcție simplă COUNTIF va face magia. Pentru a număra numărul de celule multiple care conțin un șir dat, folosim operatorul wildcard cu funcția COUNTIF.
Funcția Excel REPLACE vs SUBSTITUTE: Funcțiile REPLACE și SUBSTITUTE sunt cele mai neînțelese. Pentru a găsi și a înlocui un text dat, folosim funcția SUBSTITUTE. În cazul în care REPLACE este utilizat pentru a înlocui un număr de caractere în șir …
Articole populare:
50 de comenzi rapide Excel pentru a vă crește productivitatea | Deveniți mai rapid la sarcina dvs. Aceste 50 de comenzi rapide vă vor face să lucrați și mai rapid pe Excel.
Cum se utilizează funcția Excel VLOOKUP| Aceasta este una dintre cele mai utilizate și populare funcții ale Excel, care este utilizată pentru a căuta valoarea din diferite intervale și foi.
Cum se folosește Excel Funcția COUNTIF| Numărați valorile cu condiții folosind această funcție uimitoare. Nu trebuie să vă filtrați datele pentru a număra anumite valori. Funcția Countif este esențială pentru a vă pregăti tabloul de bord.
Cum se utilizează funcția SUMIF în Excel | Aceasta este o altă funcție esențială a tabloului de bord. Acest lucru vă ajută să rezumați valori în condiții specifice.