Vemo, da se funkciji TRIM in CLEAN Excel uporabljata za čiščenje nenatisljivih znakov in dodatnih presledkov iz nizov, vendar ne pomagata veliko pri prepoznavanju nizov, ki vsebujejo posebne znake, kot je @ ali! itd. V takih primerih uporabljamo UDF -je.
Če torej želite vedeti, ali niz vsebuje posebne znake, ne boste našli nobene formule ali funkcije Excel za to. Posebne znake, ki so na tipkovnici, lahko najdemo z ročnim tipkanjem, vendar ne morete vedeti, ali so v nizu simboli ali ne.
Iskanje posebnih znakov je lahko zelo pomembno za namene čiščenja podatkov. In v nekaterih primerih je treba to storiti. Kako torej to storimo v Excelu? Kako lahko vemo, ali niz vsebuje posebne znake? No, za to lahko uporabimo UDF.
Spodnja formula bo vrnila TRUE, če katera koli celica vsebuje znake, ki niso 1 do 0 in A do Z (v obeh primerih). Če ne najde nobenih posebnih znakov, bo vrnil FALSE.
Splošna formula
| = VsebujeSpecialCharacters (niz) |
Vrvica: Niz, ki ga želite preveriti za posebne znake.
Če želite, da ta formula deluje, boste morali v modul svojega delovnega zvezka vnesti spodnjo kodo.
Zato odprite Excel. Pritisnite ALT+F11, da odprete VBE. Vstavite modul iz menija Vstavi. Kopirajte spodnjo kodo in jo prilepite v modul.
Funkcija vsebuje SpecialCharacters (str As String) As Boolean For I = 1 To Len (str) ch = Mid (str, I, 1) Izberite črko ch Primer "0" do "9", "A" do "Z", "a "To" z "," "ContainsSpecialCraracters = Napačne črke, sicer ContainsSpecialCharacters = True Exit For End Izberite Next End Funkcija
Zdaj je funkcija pripravljena za uporabo.
Pojdite na delovni list v delovnem zvezku, ki vsebuje nize, ki jih želite preveriti.
V celico C2 zapišite spodnjo formulo:
| = Vsebuje posebne znake (B13) |
Vrne TRUE za prvi niz, saj vsebuje poseben znak. Ko kopirate formulo navzdol, je prikazano FALSE za niz B14 in tako naprej.
Nenavadno pa kaže TRUE za zadnji niz "Exceltip.com". To je zato, ker vsebuje piko (.). Ampak zakaj tako? Poglejmo kodo, da jo razumemo.
Kako deluje funkcija?
Ponavljamo vse znake niza z uporabo zanke For in mid funkcije VBA. Funkcija Mid izvleče enega znaka naenkrat iz niza in ga shrani v spremenljivko ch.
For I = 1 To Len (str) ch = Mid (str, I, 1)
Zdaj prihaja glavni del. Za preverjanje, kaj vsebuje spremenljivka ch, uporabljamo stavek select case.
VBA povemo, naj preveri, ali ch vsebuje katero od definiranih vrednosti. Določil sem 0 do 9, od A do Z, od a do z in "" (presledek). Če c
h vsebuje katero koli od teh vrednosti, vrednost nastavimo na False.
Izberite Case ch Case "0" To "9", "A" to "Z", "a" To "z", "ContainsSpecialCharacters = False
Vidite, da na seznamu nimamo pike (.), Zato za niz, ki vsebuje piko, dobimo TRUE. Seznamu lahko dodate kateri koli znak, ki je izvzet iz formule.
Če ch vsebuje kateri koli znak razen navedenih znakov, nastavimo rezultat funkcije na True in končamo zanko tam.
Primer drugače ContainsSpecialCharacters = Pravi izhod za
Torej ja, tako to deluje. Če pa želite očistiti posebne znake, boste morali narediti nekaj sprememb v funkciji.
Kako očistiti posebne znake iz nizov v Excelu?
Za čiščenje posebnih znakov iz niza v Excelu sem v VBA ustvaril še eno funkcijo po meri. Sintaksa funkcije je:
| = CleanSpecialCharacters (niz) |
Ta funkcija vrne očiščeno različico niza, posredovanega vanj. Novi niz ne bo vseboval nobenega posebnega znaka.
Koda te funkcije je videti tako:
Funkcija CleanSpecialCharacters (str kot niz) Dim nstr kot niz nstr = str Za I = 1 do Len (str) ch = srednji (str, I, 1) Izberite črko ch Primer "0" do "9", "A" To " Z "," a "Za" z "," "'Ne naredi nič drugega Primer nstr = Zamenjaj (nstr, ch," ") Konec Izberite Naprej CleanSpecialCharacters = nstr Končna funkcija
Kopirajte to v isti modul, ki ste ga kopirali v zgornji kodi.
Ko uporabite to formulo za nizove, bodo rezultati naslednji:
Vidite lahko, da je vsak poseben znak odstranjen iz niza, vključno s piko.
Kako deluje?
Deluje enako kot zgornja funkcija. Edina razlika je v tem, da ta funkcija nadomesti vsak poseben znak z ničelnim znakom. Oblikuje nov niz in vrne ta niz.
Izberite Case ch Primer "0" Do "9", "A" do "Z", "a" To "z", "" ContainsSpecialCharacters = False Case Drugače nstr = Zamenjaj (nstr, ch, "") Konec Izberi
Če želite kateri koli znak izvzeti iz čiščenja, ga lahko dodate na seznam v kodi. Excel bo oprostil ta posebni znak.
Torej, fantje, tako lahko najdete in zamenjate posebne znake iz niza v Excelu. Upam, da je bilo to dovolj razlagalno in koristno. Če vam ne pomaga pri reševanju vaše težave, nam to sporočite v spodnjem razdelku za komentarje.
Kako uporabljati funkcijo TRIM v Excelu: Funkcija TRIM se uporablja za obrezovanje nizov in čiščenje vseh zaporednih ali vodilnih presledkov iz niza. To nam zelo pomaga pri čiščenju podatkov.
Kako uporabljati funkcijo CLEAN v Excelu: Funkcija Clean se uporablja za čiščenje znakov, ki jih ni mogoče natisniti iz niza. Ta funkcija se večinoma uporablja s funkcijo TRIM za čiščenje uvoženih tujih podatkov.
Zamenjajte besedilo s konca niza, začenši s spremenljivega položaja: Za zamenjavo besedila s konca niza uporabimo funkcijo REPLACE. Funkcija REPLACE uporablja položaj besedila v nizu, da ga nadomesti.
Kako preveriti, ali niz vsebuje eno od številnih besedil v Excelu: Za preverjanje, ali niz vsebuje več besedil, uporabimo to formulo. S funkcijo SUM povzamemo vsa ujemanja in nato izvedemo logiko, da preverimo, ali niz vsebuje katerega od več nizov.
Preštej celice, ki vsebujejo določeno besedilo: Preprosta funkcija COUNTIF bo naredila čarovnijo. Za štetje števila več celic, ki vsebujejo dani niz, uporabimo nadomestni operator s funkcijo COUNTIF.
Excel REPLACE vs SUBSTITUTE funkcija: Funkciji REPLACE in SUBSTITUTE sta najbolj napačno razumljeni funkciji. Za iskanje in zamenjavo danega besedila uporabimo funkcijo SUBSTITUTE. Kjer se REPLACE uporablja za zamenjavo številnih znakov v nizu …
Priljubljeni članki:
50 bližnjic v Excelu za večjo produktivnost | Poskrbite, da bo vaša naloga hitrejša. S temi 50 bližnjicami boste še hitreje delali v Excelu.
Kako uporabljati funkcijo Excel VLOOKUP| To je ena najpogosteje uporabljenih in priljubljenih funkcij programa Excel, ki se uporablja za iskanje vrednosti iz različnih obsegov in listov.
Kako uporabljati Excel COUNTIF funkcija| Štejte vrednosti s pogoji s to neverjetno funkcijo. Za štetje določenih vrednosti vam ni treba filtrirati podatkov. Funkcija Countif je bistvena za pripravo vaše armaturne plošče.
Kako uporabljati funkcijo SUMIF v Excelu | To je še ena bistvena funkcija armaturne plošče. To vam pomaga povzeti vrednosti za posebne pogoje.