Znamo da se funkcije TRIM i CLEAN Excel koriste za čišćenje neispisivih znakova i dodatnih razmaka iz nizova, ali ne pomažu puno u identificiranju nizova koji sadrže posebne znakove poput @ ili! itd. U takvim slučajevima koristimo UDF -ove.
Dakle, ako želite znati sadrži li niz posebne znakove, nećete pronaći nikakvu Excel formulu ili funkciju za to. Posebne znakove koji se nalaze na tipkovnici možemo pronaći ručnim upisivanjem, ali ne možete znati postoje li simboli u nizu ili ne.
Pronalaženje posebnih znakova može biti vrlo važno za čišćenje podataka. A u nekim slučajevima to se mora učiniti. Pa kako to možemo učiniti u Excelu? Kako možemo znati sadrži li niz posebne znakove? Pa, za to možemo upotrijebiti UDF.
Formula u nastavku vraća TRUE ako bilo koja ćelija sadrži bilo koje znakove osim 1 do 0 i A do Z (u oba slučaja). Ako ne pronađe nikakve posebne znakove, vratit će FALSE.
Opća formula
| = SadržiSpecialCharacters (niz) |
Niz: Niz koji želite provjeriti ima li posebnih znakova.
Da bi ova formula funkcionirala, morat ćete unijeti donji kôd u modul svoje radne knjige.
Pa otvorite Excel. Pritisnite ALT+F11 za otvaranje VBE. Umetnite modul iz izbornika Umetanje. Kopirajte donji kod i zalijepite ga u modul.
Funkcija sadržiSpecialCharacters (str kao niz) kao logičko za I = 1 do Len (str) ch = srednji (str, I, 1) Odaberite velika slova velika slova "0" do "9", "A" do "Z", "a "To" z "," "ContainsSpecialCharacters = Netačna slova Ostalo ContaSSpecialCharacters = Istinski izlaz za kraj Odaberite Sljedeća krajnja funkcija
Sada je funkcija spremna za upotrebu.
Idite na radni list u radnoj knjizi koji sadrži nizove koje želite provjeriti.
Napišite donju formulu u ćeliju C2:
| = Sadrži posebne znakove (B13) |
Vraća TRUE za prvi niz budući da sadrži poseban znak. Kada kopirate formulu prema dolje, prikazuje se LAŽ za niz B14 i tako dalje.
Ali čudno prikazuje TRUE za zadnji niz "Exceltip.com". To je zato što sadrži točku (.). Ali zašto tako? Ispitajmo kod kako bismo ga razumjeli.
Kako funkcionira funkcija?
Ponavljamo sve znakove niza pomoću for petlje i mid funkcije VBA. Funkcija Mid izdvaja jedan po jedan znak iz niza i pohranjuje ga u ch varijablu.
Za I = 1 To Len (str) ch = Mid (str, I, 1)
Sada dolazi glavni dio. Koristimo izraz case case za provjeru što sadrži varijabla ch.
Kažemo VBA -i da provjeri sadrži li ch bilo koju od definiranih vrijednosti. Definirao sam 0 do 9, A do Z, a do z i "" (razmak). Ako c
h sadrži bilo što od ovoga, vrijednost smo postavili na Netačno.
Odaberite Case ch Case "0" To "9", "A" to "Z", "a" To "z", "ContainsSpecialCharacters = False
Možete vidjeti da nemamo točku (.) Na popisu i zato dobivamo TRUE za niz koji sadrži točku. Na popis možete dodati bilo koji znak da biste ga izuzeli iz formule.
Ako ch sadrži bilo koji znak osim navedenih znakova, rezultat funkcije postavljamo na True i tu završavamo petlju.
Drugi slučaj SadržiSpecialCharacters = Istinski izlaz za
Pa da, ovako to funkcionira. No, ako želite očistiti posebne znakove, morat ćete unijeti neke promjene u funkciju.
Kako očistiti posebne znakove iz nizova u Excelu?
Za čišćenje posebnih znakova iz niza u Excelu stvorio sam drugu prilagođenu funkciju u VBA -i. Sintaksa funkcije je:
| = CleanSpecialCharacters (niz) |
Ova funkcija vraća očišćenu verziju niza prenesenu u nju. Novi niz neće sadržavati nikakve posebne znakove.
Kod ove funkcije ide ovako:
Funkcija CleanSpecialCharacters (str kao niz) Dim nstr kao niz nstr = str Za I = 1 do Len (str) ch = srednji (str, I, 1) Odaberite velika slova velika slova "0" do "9", "A" do " Z "," a "To" z "," "'Ne poduzimaj ništa Slučaj Inače nstr = Zamijeni (nstr, ch," ") Kraj Odaberi sljedeće CleanSpecialCharacters = nstr Završi funkciju
Kopirajte ovo u isti modul koji ste kopirali u gornji kod.
Kada koristite ovu formulu na nizovima, rezultati će biti sljedeći:
Možete vidjeti da je svaki poseban znak uklonjen iz niza uključujući točku.
Kako radi?
Radi isto kao i gornja funkcija. Jedina razlika je u tome što ova funkcija zamjenjuje svaki poseban znak s null znakom. Formira novi niz i vraća ovaj niz.
Odaberite Case ch Slučaj "0" do "9", "A" do "Z", "a" To "z", "" ContainsSpecialCharacters = Lažno slovo Ostalo nstr = Zamijeni (nstr, ch, "") Kraj Odaberi
Ako želite izuzeti bilo koji znak čišćenja, možete ga dodati na popis u kodu. Excel će oprostiti taj poseban znak.
Pa da, dečki, ovako možete pronaći i zamijeniti posebne znakove iz niza u Excelu. Nadam se da je ovo bilo dovoljno objašnjenje i pomoć. Ako vam ne pomaže u rješavanju vašeg problema, javite nam u donjem odjeljku komentara.
Kako koristiti funkciju TRIM u Excelu: Funkcija TRIM koristi se za obrezivanje nizova i čišćenje svih zaostalih ili vodećih prostora od niza. To nam puno pomaže u procesu čišćenja podataka.
Kako koristiti funkciju CLEAN u Excelu: Funkcija čišćenja koristi se za čišćenje neispisivih znakova iz niza. Ova se funkcija uglavnom koristi s funkcijom TRIM za čišćenje uvezenih inozemnih podataka.
Zamijenite tekst s kraja niza počevši od promjenjivog položaja: Za zamjenu teksta s kraja niza koristimo funkciju ZAMJENA. Funkcija ZAMJENA koristi položaj teksta u nizu da ga zamijeni.
Kako provjeriti sadrži li niz jedan od mnogih tekstova u Excelu: Koristimo ovu formulu da provjerimo sadrži li niz više od više teksta. Koristimo funkciju SUM za zbrajanje svih podudaranja, a zatim izvodimo logiku da provjerimo sadrži li niz bilo koji od više nizova.
Brojanje ćelija koje sadrže određeni tekst: Jednostavna funkcija COUNTIF učinit će čaroliju. Za brojanje više ćelija koje sadrže dati niz koristimo zamjenski operator s funkcijom COUNTIF.
Excel ZAMJENA vs ZAMJENA funkcija: Funkcije ZAMJENA i ZAMJENA su najčešće pogrešno shvaćene funkcije. Za pronalaženje i zamjenu zadanog teksta koristimo funkciju ZAMJENA. Gdje se REPLACE koristi za zamjenu brojnih znakova u nizu …
Popularni članci:
50 Excel prečaca za povećanje vaše produktivnosti | Budite brži u izvršavanju svojih zadataka. Ovih 50 prečaca učinit će da radite još brže na Excelu.
Kako koristiti Excel VLOOKUP funkciju| Ovo je jedna od najčešće korištenih i popularnih funkcija programa Excel koja se koristi za traženje vrijednosti iz različitih raspona i listova.
Kako koristiti Excel COUNTIF funkcija| Brojte vrijednosti s uvjetima pomoću ove nevjerojatne funkcije. Ne morate filtrirati svoje podatke da biste brojali određene vrijednosti. Funkcija Countif bitna je za pripremu vaše nadzorne ploče.
Kako koristiti funkciju SUMIF u Excelu | Ovo je još jedna bitna funkcija nadzorne ploče. To vam pomaže pri zbrajanju vrijednosti u posebnim uvjetima.