Vieme, že funkcie TRIM a CLEAN Excel sa používajú na čistenie netlačiteľných znakov a medzier od reťazcov, ale príliš nepomáhajú pri identifikácii reťazcov obsahujúcich žiadny špeciálny znak ako @ alebo! atď. V takýchto prípadoch používame UDF.
Ak teda chcete vedieť, či reťazec obsahuje nejaké špeciálne znaky, nenájdete na to žiadny vzorec ani funkciu programu Excel. Špeciálne znaky, ktoré sú na klávesnici, môžeme nájsť manuálnym zadaním, ale nemôžete vedieť, či sa v reťazci nachádzajú symboly alebo nie.
Nájdenie špeciálnych znakov môže byť veľmi dôležité pre účely čistenia údajov. A v niektorých prípadoch sa to musí urobiť. Ako to teda urobíme v programe Excel? Ako môžeme vedieť, či reťazec obsahuje nejaké špeciálne znaky? Môžeme na to použiť UDF.
Nasledujúci vzorec vráti hodnotu TRUE, ak ľubovoľná bunka obsahuje akékoľvek znaky iné ako 1 až 0 a A až Z (v oboch prípadoch). Ak nenájde žiadne špeciálne znaky, vráti hodnotu FALSE.
Generický vzorec
| = Obsahuje špeciálne znaky (reťazec) |
Reťazec: Reťazec, v ktorom chcete skontrolovať špeciálne znaky.
Aby tento vzorec fungoval, budete musieť vložiť kód uvedený nižšie do modulu vášho zošita.
Takže otvorte Excel. VBE otvoríte stlačením klávesov ALT+F11. Vložte modul z ponuky Vložiť. Skopírujte nižšie uvedený kód a vložte ho do modulu.
Funkcia obsahuje špeciálne znaky (str ako reťazec) ako booleovský pre I = 1 až Len (str) ch = stred (str, I, 1) Vyberte prípad ch Prípad „0“ až „9“, „A“ až „Z“, „a "Do" z "," "ContainsSpecialCharacter = False Case Else ContainsSpecialChar Character = True Exit For End Select Next End Function
Teraz je funkcia pripravená na použitie.
Prejdite na pracovný hárok v zošite, ktorý obsahuje reťazce, ktoré chcete skontrolovať.
Do bunky C2 napíšte nasledujúci vzorec:
| = Obsahuje špeciálne znaky (B13) |
Pre prvý reťazec vráti hodnotu TRUE, pretože obsahuje špeciálny znak. Keď skopírujete vzorec nadol, zobrazí sa FALSE pre reťazec B14 atď.
Ale napodiv to ukazuje PRAVDU pre posledný reťazec „Exceltip.com“. Dôvodom je, že obsahuje bodku (.). Ale prečo? Preskúmajme kód, aby sme mu rozumeli.
Ako funkcia funguje?
Iterujeme všetkými znakmi reťazca pomocou funkcie For loop a mid VBA. Funkcia Mid extrahuje jeden znak naraz z reťazca a ukladá ho do premennej ch.
For I = 1 To Len (str) ch = Mid (str, I, 1)
Teraz prichádza hlavná časť. Pomocou príkazu select case skontrolujeme, čo premenná ch obsahuje.
Hovoríme VBA, aby skontrolovala, či ch neobsahuje niektoré z definovaných hodnôt. Definoval som 0 až 9, A až Z, a až z a "" (medzera). Ak c
h obsahuje niektoré z nich, nastavili sme jeho hodnotu na hodnotu False.
Vyberte prípad alebo prípad „0“ až „9“, „A“ až „Z“, „a“ až „z“, „„ Obsahuje špeciálne znaky = nepravda
Môžete vidieť, že v zozname nemáme bodku (.), A preto dostaneme PRAVDU pre reťazec, ktorý obsahuje bodku. Do zoznamu môžete pridať ľubovoľný znak, aby bol zo vzorca vyňatý.
Ak ch obsahuje akýkoľvek iný znak ako uvedené znaky, nastavíme výsledok funkcie na True a slučku ukončíme práve tam.
Iný prípad obsahuje špeciálne znaky = skutočný východ pre
Tak takto to funguje. Ak však chcete vyčistiť špeciálne znaky, budete musieť vo funkcii vykonať niekoľko zmien.
Ako vyčistiť špeciálne znaky zo strún v programe Excel?
Na čistenie špeciálnych znakov z reťazca v programe Excel som vytvoril ďalšiu vlastnú funkciu vo VBA. Syntax funkcie je:
| = CleanSpecialCharacter (reťazec) |
Táto funkcia vráti vyčistenú verziu reťazca, ktorý bol do nej vložený. Nový reťazec nebude obsahovať žiadne špeciálne znaky.
Kód tejto funkcie vyzerá takto:
Funkcia CleanSpecialCharacter (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 "Do" z "," "'Nerobiť nič Prípad inak nstr = Nahradiť (nstr, ch," ") Koniec Vybrať Ďalej CleanSpecialCharacters = nstr Ukončiť funkciu
Skopírujte to do rovnakého modulu, ktorý ste skopírovali do vyššie uvedeného kódu.
Keď použijete tento vzorec na reťazce, výsledky budú tieto:
Môžete vidieť, že každý špeciálny znak je odstránený z reťazca vrátane bodky.
Ako to funguje?
Funguje rovnako ako vyššie uvedená funkcia. Jediným rozdielom je, že táto funkcia nahradí každý špeciálny znak nulovým znakom. Vytvorí nový reťazec a vráti tento reťazec.
Vyberte Case ch Case "0" To "9", "A" To "Z", "a" To "z", "" 'ContainsSpecialCharacter = False Case Else nstr = Replace (nstr, ch, "") End Select
Ak chcete niektorý znak oslobodiť od čistenia, môžete ho pridať do zoznamu v kóde. Excel tento špeciálny znak odpustí.
Takže áno, chlapci, takto môžete nájsť a nahradiť špeciálne znaky z reťazca v programe Excel. Dúfam, že to bolo dostatočne vysvetľujúce a užitočné. Ak vám to pri riešení vášho problému nepomôže, dajte nám vedieť v sekcii komentárov nižšie.
Ako používať funkciu TRIM v Exceli: Funkcia TRIM sa používa na orezanie reťazcov a čistenie všetkých koncových alebo úvodných medzier od reťazca. To nám veľmi pomáha v procese čistenia údajov.
Ako používať funkciu CLEAN v programe Excel: Funkcia Clean sa používa na vyčistenie netlačiteľných znakov z reťazca. Táto funkcia sa väčšinou používa s funkciou TRIM na čistenie importovaných zahraničných údajov.
Nahradiť text od konca reťazca počnúc od variabilnej pozície: Na nahradenie textu z konca reťazca používame funkciu REPLACE. Funkcia REPLACE používa na nahradenie pozíciu textu v reťazci.
Ako skontrolovať, či reťazec obsahuje jeden z mnohých textov v programe Excel: Na nájdenie kontroly, či reťazec obsahuje viacnásobný text, použijeme tento vzorec. Na zhrnutie všetkých zápasov použijeme funkciu SUMA a potom vykonáme logiku, aby sme skontrolovali, či reťazec obsahuje niektorý z viacerých reťazcov.
Spočítajte bunky, ktoré obsahujú konkrétny text: Kúzlo urobí jednoduchá funkcia COUNTIF. Na spočítanie počtu viacerých buniek, ktoré obsahujú daný reťazec, používame zástupný znak s funkciou COUNTIF.
Funkcia Excel REPLACE vs SUBSTITUTE: Funkcie REPLACE a SUBSTITUTE sú najviac nepochopenými funkciami. Na nájdenie a nahradenie daného textu používame funkciu SUBSTITUTE. Kde sa REPLACE používa na nahradenie niekoľkých znakov v reťazci …
Populárne články:
50 skratiek programu Excel na zvýšenie produktivity | Vykonajte svoju úlohu rýchlejšie. Týchto 50 skratiek vám umožní pracovať ešte rýchlejšie v Exceli.
Ako používať funkciu Excel VLOOKUP| Toto je jedna z najpoužívanejších a najobľúbenejších funkcií programu Excel, ktorá sa používa na vyhľadávanie hodnôt z rôznych rozsahov a hárkov.
Ako používať Excel Funkcia COUNTIF| Počítajte hodnoty s podmienkami pomocou tejto úžasnej funkcie. Na počítanie konkrétnych hodnôt nie je potrebné filtrovať údaje. Funkcia Countif je potrebná na prípravu palubnej dosky.
Ako používať funkciu SUMIF v programe Excel | Toto je ďalšia zásadná funkcia palubnej dosky. To vám pomôže zhrnúť hodnoty za konkrétnych podmienok.