Mes žinome, kad „TRIM“ ir „CLEAN Excel“ funkcijos naudojamos išvalyti nespausdinamus simbolius ir papildomas tarpus iš eilučių, tačiau jos nelabai padeda identifikuoti eilutes, kuriose yra specialiųjų simbolių, tokių kaip @ arba! ir tt Tokiais atvejais mes naudojame UDF.
Taigi, jei norite sužinoti, ar eilutėje yra kokių nors specialių simbolių, nerasite jokios „Excel“ formulės ar funkcijos. Klaviatūroje esančius specialiuosius simbolius galime rasti rankiniu būdu įvesdami, bet jūs negalite žinoti, ar eilutėje yra simbolių, ar ne.
Norint išvalyti duomenis, gali būti labai svarbu rasti specialių simbolių. Ir kai kuriais atvejais tai turi būti padaryta. Taigi, kaip tai padaryti „Excel“? Kaip mes galime žinoti, ar eilutėje yra specialiųjų simbolių? Na, tam galime naudoti UDF.
Žemiau pateikta formulė grąžins TRUE, jei bet kuriame langelyje yra bet kokių simbolių, išskyrus 1–0 ir nuo A iki Z (abiem atvejais). Jei jis neranda jokių specialių simbolių, jis grąžina FALSE.
Bendra formulė
| = Sudėtyje yra specialių simbolių (eilutė) |
Styga: Eilutė, kurią norite patikrinti, ar nėra specialiųjų simbolių.
Kad ši formulė veiktų, turėsite įdėti žemiau esantį kodą į savo darbo knygos modulį.
Taigi atidarykite „Excel“. Norėdami atidaryti VBE, paspauskite ALT+F11. Įdėkite modulį iš meniu Įterpti. Nukopijuokite žemiau esantį kodą ir įklijuokite jį į modulį.
Funkcijoje yraSpecialCharacters (str As String) kaip Boolean For I = 1 To Len (str) ch = Mid (str, I, 1) Pasirinkite atvejo ch atvejį „0“ iki „9“, „A“ iki „Z“, „a "Į" z "," "Yra
Dabar funkcija yra paruošta naudoti.
Eikite į darbalapio darbalapį, kuriame yra norimos patikrinti eilutės.
C2 langelyje parašykite šią formulę:
| = Sudėtyje yra specialių simbolių (B13) |
Ji grąžina TRUE pirmajai eilutei, nes joje yra specialus simbolis. Kai nukopijuojate formulę žemyn, ji rodo klaidingą B14 eilutę ir pan.
Tačiau keista, kad paskutinėje eilutėje „Exceltip.com“ rodoma TIESA. Taip yra todėl, kad jame yra taškas (.). Bet kodėl taip? Panagrinėkime kodą, kad suprastume.
Kaip veikia funkcija?
Mes kartojame visus eilutės simbolius naudodami VBA funkciją „For loop“ ir „mid“. Funkcija „Mid“ iš eilutės išskiria vieną simbolį ir išsaugo jį kintamajame ch.
I = 1 iki Len (str) ch = vidurys (str, I, 1)
Dabar ateina pagrindinė dalis. Mes naudojame pasirinktą atvejį, kad patikrintume, ką turi kintamasis ch.
Mes liepiame VBA patikrinti, ar ch yra kokia nors apibrėžta reikšmė. Aš apibrėžiau nuo 0 iki 9, nuo A iki Z, nuo a iki z ir "" (tarpas). Jei c
h yra bet kuris iš šių, mes nustatome jo reikšmę False.
Pasirinkite atvejį ch atvejis „0“ iki „9“, „A“ iki „Z“, „a“ iki „z“, „“ sudėtyjeSpecialCharacters = False
Matote, kad sąraše nėra taško (.), Todėl eilutei, kurioje yra taškas, gauname TRUE. Į sąrašą galite įtraukti bet kurį simbolį, kad jis nebūtų įtrauktas į formulę.
Jei ch yra bet koks simbolis, išskyrus išvardytus simbolius, mes nustatome funkcijos rezultatą į True ir baigiame ciklą.
Kitoje byloje yra „SpecialCharacters“ = tikras išėjimas
Taigi taip, kaip tai veikia. Bet jei norite išvalyti specialiuosius simbolius, turėsite atlikti tam tikrus funkcijos pakeitimus.
Kaip išvalyti specialiuosius simbolius iš „Excel“ eilučių?
Norėdami išvalyti specialiuosius simbolius iš „Excel“ eilutės, VBA sukūriau kitą pasirinktinę funkciją. Funkcijos sintaksė yra tokia:
| = „CleanSpecialCharacters“ (eilutė) |
Ši funkcija grąžina išvalytą į ją perduotos eilutės versiją. Naujoje eilutėje nebus jokių specialiųjų simbolių.
Šios funkcijos kodas yra toks:
Funkcija CleanSpecialCharacters (str As String) Dim nstr As String nstr = str For I = 1 To Len (str) ch = Mid (str, I, 1) Pasirinkite Case ch Case "0" to "9", "A" To " Z "," a "Iki" z "," "'Nieko nedaryti Case Else nstr = Pakeisti (nstr, ch," ") Pabaiga Pasirinkite Kitas CleanSpecialCharacters = nstr Pabaigos funkcija
Nukopijuokite tai tame pačiame modulyje, kurį nukopijavote aukščiau esančiame kode.
Kai naudojate šią formulę eilutėse, rezultatai bus tokie:
Matote, kad visi specialūs simboliai pašalinami iš eilutės, įskaitant tašką.
Kaip tai veikia?
Jis veikia taip pat, kaip ir aukščiau pateikta funkcija. Vienintelis skirtumas yra tai, kad ši funkcija kiekvieną specialųjį simbolį pakeičia nuliniu simboliu. Suformuoja naują eilutę ir grąžina šią eilutę.
Pasirinkite atvejo ch atvejį „0“ iki „9“, „A“ iki „Z“, „a“ iki „z“, „“ „IncludesSpecialCharacters = False case Else nstr = Replace (nstr, ch," ") End Select
Jei norite atleisti bet kurį simbolį nuo valymo, galite jį įtraukti į kodo sąrašą. „Excel“ atleis tą ypatingą simbolį.
Taigi, vaikinai, taip galite rasti ir pakeisti specialiuosius simbolius iš eilutės „Excel“. Tikiuosi, kad tai buvo pakankamai paaiškinanti ir naudinga. Jei tai nepadeda jums išspręsti problemos, praneškite mums toliau pateiktame komentarų skyriuje.
Kaip naudoti „TRIM“ funkciją „Excel“: TRIM funkcija naudojama stygoms apipjaustyti ir bet kokiems galiniams ar priekiniams tarpams išvalyti. Tai labai padeda mums išvalyti duomenis.
Kaip naudoti funkciją „CLEAN“ programoje „Excel“: Valymo funkcija naudojama išvalyti nespausdinamus simbolius iš eilutės. Ši funkcija dažniausiai naudojama su TRIM funkcija, norint išvalyti importuotus užsienio duomenis.
Pakeiskite tekstą iš eilutės pabaigos, pradedant nuo kintamos padėties: Norėdami pakeisti tekstą iš eilutės pabaigos, naudojame funkciją REPLACE. Funkcija PAKEISTI naudoja teksto padėtį eilutėje, kad ją pakeistų.
Kaip patikrinti, ar eilutėje yra vienas iš daugelio „Excel“ tekstų: Norėdami sužinoti, ar eilutėje yra kelių tekstų, naudojame šią formulę. Mes naudojame SUM funkciją, kad apibendrintume visas atitiktis ir atliktume logiką, kad patikrintume, ar eilutėje yra bet kuri iš kelių eilučių.
Skaičiuoti langelius, kuriuose yra konkretaus teksto: Paprasta COUNTIF funkcija padarys stebuklą. Norėdami suskaičiuoti kelių langelių, kuriuose yra tam tikra eilutė, skaičių, naudojame pakaitos simbolių operatorių su funkcija COUNTIF.
„Excel“ funkcija „PAKEISTI VISUOMENĖS“: Funkcijos REPLACE ir SUPSTITUTE yra labiausiai nesuprantamos funkcijos. Norėdami rasti ir pakeisti tam tikrą tekstą, naudojame funkciją SUBSTITUTE. Kai REPLACE naudojamas pakeisti eilutės simbolių skaičių…
Populiarūs straipsniai:
50 „Excel“ nuorodų, skirtų produktyvumui padidinti | Greičiau atlikite savo užduotį. Šie 50 sparčiųjų klavišų leis dar greičiau dirbti naudojant „Excel“.
Kaip naudotis „Excel VLOOKUP“ funkcija| Tai yra viena iš dažniausiai naudojamų ir populiariausių „Excel“ funkcijų, naudojama norint ieškoti vertės iš įvairių diapazonų ir lapų.
Kaip naudotis „Excel“ Funkcija COUNTIF| Naudodami šią nuostabią funkciją, suskaičiuokite reikšmes su sąlygomis. Norint suskaičiuoti konkrečias vertes, nereikia filtruoti duomenų. Skaitiklio funkcija yra būtina norint paruošti prietaisų skydelį.
Kaip naudoti „SUMIF“ funkciją „Excel“ | Tai dar viena esminė prietaisų skydelio funkcija. Tai padeda apibendrinti vertes konkrečiomis sąlygomis.