Me teame, et TRIM ja CLEAN Exceli funktsioone kasutatakse trükitavate märkide ja tühikute puhastamiseks stringidest, kuid need ei aita palju tuvastada stringe, mis sisaldavad erimärke nagu @ või! jne. Sellistel juhtudel kasutame UDF -e.
Seega, kui soovite teada, kas string sisaldab erimärke, ei leia te selleks Exceli valemit ega funktsiooni. Leiame klaviatuuril olevad erimärgid käsitsi sisestades, kuid te ei saa teada, kas stringis on sümboleid või mitte.
Andmete puhastamiseks võib erimärkide leidmine olla väga oluline. Ja mõnel juhul tuleb seda teha. Niisiis, kuidas seda Excelis teha? Kuidas me saame teada, kas string sisaldab mingeid erimärke? Selleks saame kasutada UDF -i.
Allolev valem tagastab väärtuse TRUE, kui mõni lahter sisaldab muid märke peale 1 kuni 0 ja A kuni Z (mõlemal juhul). Kui see ei leia erimärke, tagastab see FALSE.
Üldine valem
| = Sisaldab erimärke (string) |
String: String, mida soovite erimärkide leidmiseks kontrollida.
Selle valemi toimimiseks peate oma töövihiku moodulisse sisestama alloleva koodi.
Nii et avage Excel. VBE avamiseks vajutage ALT+F11. Sisestage moodul menüüst Lisa. Kopeerige allolev kood ja kleepige see moodulisse.
Funktsioon sisaldab erimärke (str kui string) Booleanina I = 1 kuni Len (str) ch = Mid (str, I, 1) Valige juhtum ch Case "0" to "9", "A" to "Z", "a "To" z "," "ContainsSpecialCharacters = False case Else ContaSSpecialCharacters = True Exit for End Valige järgmine lõppfunktsioon
Nüüd on funktsioon kasutamiseks valmis.
Minge töövihiku töölehele, mis sisaldab stringe, mida soovite kontrollida.
Kirjutage lahtrisse C2 järgmine valem:
| = Sisaldab erimärke (B13) |
Tagastab esimese stringi väärtuse TRUE, kuna see sisaldab erimärki. Kui kopeerite valemi allapoole, kuvatakse see B14 stringi jaoks vale ja nii edasi.
Kuid kummalisel kombel näitab see viimase tõe "Exceltip.com" jaoks tõde. Põhjus on selles, et see sisaldab punkti (.). Aga miks nii? Uurime koodi, et aru saada.
Kuidas funktsioon töötab?
Me kordame läbi stringi kõik märgid, kasutades VBA funktsiooni For loop ja mid. Funktsioon Mid ekstraheerib stringist ühe märgi korraga ja salvestab selle muutuja ch sisse.
I = 1 kuni Len (str) ch = keskel (str, I, 1)
Nüüd tuleb põhiosa. Me kasutame lauset select case, et kontrollida, mida ch muutuja sisaldab.
Käskime VBA -l kontrollida, kas ch sisaldab mõnda määratletud väärtust. Olen määratlenud 0 kuni 9, A kuni Z, a kuni z ja "" (tühik). Kui c
h sisaldab mõnda neist, määrame selle väärtuseks Väär.
Valige juhtum ch Juhtum "0" kuni "9", "A" kuni "Z", "a" kuni "z", "" Sisaldab SpecialCharacters = False
Näete, et meil pole loendis punkti (.) Ja seetõttu saame punkti sisaldava stringi jaoks tõese. Valemist vabastamiseks saate loendisse lisada mis tahes märgi.
Kui ch sisaldab muid märke peale loetletud märkide, määrame funktsiooni tulemuseks tõese ja lõpetame tsükli seal.
Juhtum muidu sisaldab SpecialCharacters = True Exit For
Nii et jah, see töötab nii. Aga kui soovite erimärke puhastada, peate funktsiooni muutma.
Kuidas Excelis tähemärke stringidest puhastada?
Erimärkide puhastamiseks Excelist stringist olen loonud VBA -s veel ühe kohandatud funktsiooni. Funktsiooni süntaks on järgmine:
| = CleanSpecialCharacters (string) |
See funktsioon tagastab sinna edastatud stringi puhastatud versiooni. Uus string ei sisalda erimärke.
Selle funktsiooni kood on järgmine:
Funktsioon CleanSpecialCharacters (str As String) Dim nstr As String nstr = str For I = 1 To Len (str) ch = Mid (str, I, 1) Valige juhtum ch Case "0" to "9", "A" To " Z "," a "To" z "," "'Do do nothing Case Else nstr = Asenda (nstr, ch," ") End Vali Järgmine CleanSpecialCharacters = nstr Lõppfunktsioon
Kopeerige see samasse moodulisse, mille kopeerisite ülaltoodud koodi.
Kui kasutate seda valemit stringidel, on tulemused järgmised:
Näete, et stringist eemaldatakse kõik erimärgid, sealhulgas punkt.
Kuidas see töötab?
See toimib samamoodi nagu ülaltoodud funktsioon. Ainus erinevus on see, et see funktsioon asendab kõik erimärgid nullmärgiga. Moodustab uue stringi ja tagastab selle stringi.
Valige juhtum ch Juhtum "0" kuni "9", "A" kuni "Z", "a" kuni "z", "" "Sisaldab SpecialCharacters = False case Else nstr = Replace (nstr, ch," ") End Select
Kui soovite mõne märgi puhastamisest vabastada, saate selle koodi loendisse lisada. Excel annab sellele erimärgile andeks.
Nii et jah, poisid, nii saate Exceli stringist erimärke leida ja asendada. Loodan, et see oli piisavalt selgitav ja kasulik. Kui see ei aita teil probleemi lahendada, andke meile sellest teada allpool olevas kommentaaride jaotises.
Kuidas kasutada funktsiooni TRIM Excelis: Funktsiooni TRIM kasutatakse stringide kärpimiseks ja nööride taga- või esiruumide puhastamiseks. See aitab meil andmete puhastamisel palju kaasa.
Funktsiooni CLEAN kasutamine Excelis: Puhastusfunktsiooni kasutatakse stringist prinditavate märkide puhastamiseks. Seda funktsiooni kasutatakse enamasti koos TRIM -funktsiooniga imporditud välisandmete puhastamiseks.
Asendage tekst stringi lõpust, alustades muutuvast positsioonist: Stringi lõpus oleva teksti asendamiseks kasutame funktsiooni REPLACE. Funktsioon REPLACE kasutab selle asendamiseks teksti positsiooni stringis.
Kuidas kontrollida, kas string sisaldab ühte paljudest Exceli tekstidest: Selle valemi abil saate kontrollida, kas string sisaldab mitut teksti. Kasutame funktsiooni SUM, et kõik vasted kokku võtta ja seejärel teostame loogikat, et kontrollida, kas string sisaldab mõnda mitmest stringist.
Loendage lahtrid, mis sisaldavad konkreetset teksti: Lihtne COUNTIF -funktsioon teeb võlu. Antud stringi sisaldavate mitme lahtri arvu loendamiseks kasutame funktsiooni COUNTIF metamärgioperaatorit.
Funktsioon Excel REPLACE vs ASENDUS: Funktsioonid REPLACE ja SUBSTITUTE on kõige valesti mõistetud funktsioonid. Antud teksti leidmiseks ja asendamiseks kasutame funktsiooni SUBSTITUTE. Kui asendust REPLACE kasutatakse rea tähemärkide asendamiseks stringis…
Populaarsed artiklid:
50 Exceli otseteed tootlikkuse suurendamiseks | Saa oma ülesandega kiiremini hakkama. Need 50 otseteed aitavad teil Excelis veelgi kiiremini töötada.
Kuidas kasutada funktsiooni Excel VLOOKUP| See on üks enim kasutatud ja populaarsemaid Exceli funktsioone, mida kasutatakse erinevate vahemike ja lehtede väärtuse otsimiseks.
Kuidas Excelit kasutada Funktsioon COUNTIF| Loendage väärtused tingimustega, kasutades seda hämmastavat funktsiooni. Te ei pea oma andmeid teatud väärtuste loendamiseks filtreerima. Countif funktsioon on armatuurlaua ettevalmistamisel hädavajalik.
Funktsiooni SUMIF kasutamine Excelis | See on veel üks armatuurlaua oluline funktsioon. See aitab teil teatud tingimustel väärtusi kokku võtta.