Tiedämme, että TRIM- ja CLEAN Excel -toimintoja käytetään puhdistamaan tulostamattomat merkit ja ylimääräiset välilyönnit merkkijonoista, mutta ne eivät auta paljoa merkkijonojen tunnistamisessa, kuten @ tai! jne. Tällaisissa tapauksissa käytämme UDF -tiedostoja.
Joten jos haluat tietää, sisältääkö merkkijono erikoismerkkejä, et löydä Excel -kaavaa tai -toimintoa siihen. Voimme löytää näppäimistön erikoismerkit kirjoittamalla manuaalisesti, mutta et voi tietää, onko merkkijonossa symboleja vai ei.
Erikoismerkkien löytäminen voi olla erittäin tärkeää tietojen puhdistamiseen. Ja joissakin tapauksissa se on tehtävä. Joten miten teemme tämän Excelissä? Mistä voimme tietää, sisältääkö merkkijono erikoismerkkejä? No, voimme käyttää UDF: ää tähän.
Alla oleva kaava palauttaa arvon TOSI, jos jokin solu sisältää muita merkkejä kuin 1 - 0 ja A - Z (molemmissa tapauksissa). Jos se ei löydä erikoismerkkejä, se palauttaa FALSE.
Yleinen kaava
| = SisältääSpecialCharacters (merkkijono) |
Jono: Merkkijono, jonka haluat tarkistaa erikoismerkkien varalta.
Jotta tämä kaava toimisi, sinun on lisättävä alla oleva koodi työkirjasi moduuliin.
Avaa siis Excel. Avaa VBE painamalla ALT+F11. Aseta moduuli Lisää -valikosta. Kopioi alla oleva koodi ja liitä se moduuliin.
Toiminto sisältääSpecialCharacters (str kuten merkkijono) Boolen muodossa I = 1 Len (str) ch = Mid (str, I, 1) Valitse kirjain ch Case "0" - "9", "A" to "Z", "a "Vastaanottaja" z "," "SisältääSpecialCharacters = False Case Else ContaSSpecialCharacters = True Exit For End Valitse Seuraava End -toiminto
Toiminto on nyt käyttövalmis.
Siirry työkirjan laskentataulukkoon, joka sisältää tarkistettavat merkkijonot.
Kirjoita alla oleva kaava soluun C2:
| = SisältääSpecialCharacters (B13) |
Se palauttaa TOSI ensimmäisen merkkijonon, koska se sisältää erikoismerkin. Kun kopioit kaavan alas, se näyttää FALSE B14 -merkkijonolle ja niin edelleen.
Mutta kummallisesti se näyttää TRUE viimeiselle merkkijonolle "Exceltip.com". Se johtuu siitä, että se sisältää pisteen (.). Mutta miksi niin? Tarkastellaan koodia ymmärtääksemme.
Kuinka toiminto toimii?
Käymme läpi kaikki merkkijonon merkit käyttämällä VBA: n For loop- ja mid -funktiota. Keskitoiminto purkaa merkkijonon kerrallaan ja tallentaa sen ch -muuttujaan.
I = 1 Len (str) ch = Mid (str, I, 1)
Nyt tulee pääosa. Käytämme select case -lauseketta tarkistaaksemme, mitä ch -muuttuja sisältää.
Kerromme VBA: lle tarkistaa, sisältääkö ch jotain määriteltyjä arvoja. Olen määritellyt 0-9, A - Z, a - z ja "" (välilyönti). Jos c
h sisältää minkä tahansa näistä, asetamme sen arvoksi False.
Valitse tapaus ch Tapaus "0" - "9", "A" - "Z", "a" Z "," "SisältääSpecialCharacters = False
Näet, että luettelossa ei ole pistettä (.), Ja siksi saamme TOSI merkkijonolle, joka sisältää pisteen. Voit lisätä luetteloon minkä tahansa merkin vapauttaaksesi kaavan.
Jos ch sisältää muita merkkejä kuin luetellut merkit, asetamme funktion tulokseksi True ja lopetamme silmukan heti.
Tapaus Muu Sisältää SpecialCharacters = True Exit For
Joten joo näin se toimii. Mutta jos haluat puhdistaa erikoismerkkejä, sinun on tehtävä joitain muutoksia toimintoon.
Kuinka puhdistaa erikoismerkit Excel -merkkijonoista?
Puhdistaakseni erikoismerkit Excel -merkkijonosta olen luonut toisen mukautetun toiminnon VBA: han. Funktion syntaksi on:
| = CleanSpecialCharacters (merkkijono) |
Tämä toiminto palauttaa puhdistetun version siihen syötetystä merkkijonosta. Uusi merkkijono ei sisällä mitään erikoismerkkejä.
Tämän toiminnon koodi menee näin:
Toiminto CleanSpecialCharacters (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 "Vastaanottaja" z "," "'Älä tee mitään Tapaus Else nstr = Korvaa (nstr, ch," ") End Valitse Seuraava CleanSpecialCharacters = nstr End Function
Kopioi tämä samaan moduuliin, jonka kopioit yllä olevaan koodiin.
Kun käytät tätä kaavaa merkkijonoissa, tulokset ovat seuraavat:
Näet, että jokainen erikoismerkki poistetaan merkkijonosta, piste mukaan lukien.
Kuinka se toimii?
Se toimii samalla tavalla kuin yllä oleva toiminto. Ainoa ero on, että tämä toiminto korvaa kaikki erikoismerkit nollamerkillä. Muodostaa uuden merkkijonon ja palauttaa tämän merkkijonon.
Valitse tapaus ch Asia "0" - "9", "A" - "Z", "a" Z "," "" Sisältää SpecialCharacters = False Case Else nstr = Korvaa (nstr, ch, "") End Select
Jos haluat vapauttaa jonkin hahmon puhdistamisesta, voit lisätä sen koodin luetteloon. Excel antaa anteeksi tämän erikoismerkin.
Joten kyllä kaverit, tällä tavalla voit löytää ja korvata erikoismerkkejä merkkijonosta Excelissä. Toivottavasti tämä oli tarpeeksi selittävä ja hyödyllinen. Jos se ei auta sinua ratkaisemaan ongelmasi, kerro siitä meille alla olevassa kommenttiosiossa.
TRIM -toiminnon käyttäminen Excelissä: TRIM -toimintoa käytetään merkkijonojen leikkaamiseen ja mahdollisten perä- ja etureunojen puhdistamiseen merkkijonosta. Tämä auttaa meitä paljon tietojen puhdistamisessa.
CLEAN -toiminnon käyttäminen Excelissä: Puhdistustoimintoa käytetään tulostamattomien merkkien poistamiseen merkkijonosta. Tätä toimintoa käytetään enimmäkseen TRIM -toiminnon kanssa tuodun ulkomaisen datan puhdistamiseen.
Korvaa teksti merkkijonon lopusta muuttujan sijainnista alkaen: Korvataksesi tekstin merkkijonon lopusta käytämme REPLACE -funktiota. REPLACE -toiminto käyttää tekstin sijaintia merkkijonossa sen korvaamiseksi.
Kuinka tarkistaa, sisältääkö merkkijono yhden monista Excelin teksteistä: Käytämme tätä kaavaa selvittääksesi, sisältääkö merkkijono useita tekstiä. Käytämme SUM -funktiota kaikkien vastaavuuksien yhteenvetoon ja suoritamme sitten logiikan tarkistamaan, sisältääkö merkkijono jotakin useista merkkijonoista.
Laske solut, jotka sisältävät tiettyä tekstiä: Yksinkertainen COUNTIF -toiminto tekee taian. Voit laskea useita soluja, jotka sisältävät tietyn merkkijonon, käyttämällä yleismerkkioperaattoria COUNTIF -funktion kanssa.
Excel KORVAA vs. KORVAA -toiminto: KORVAUS- ja KORVAUS -toiminnot ovat väärinymmärretyt toiminnot. Tietyn tekstin löytämiseksi ja korvaamiseksi käytämme SUBSTITUTE -funktiota. Jos REPLACE -näppäintä käytetään korvaamaan useita merkkijonon merkkejä…
Suosittuja artikkeleita:
50 Excel -pikanäppäintä tuottavuuden lisäämiseksi | Nopeuta tehtävääsi. Nämä 50 pikanäppäintä tekevät työskentelystäsi entistä nopeampaa Excelissä.
Excel VLOOKUP -toiminnon käyttäminen| Tämä on yksi eniten käytetyistä ja suosituimmista Excel -toiminnoista, jota käytetään arvon etsimiseen eri alueilta ja arkeilta.
Excelin käyttäminen COUNTIF -toiminto| Laske arvot olosuhteilla käyttämällä tätä hämmästyttävää toimintoa. Sinun ei tarvitse suodattaa tietoja laskeaksesi tiettyjä arvoja. Laskutoiminto on välttämätön kojelaudan valmistelemiseksi.
SUMIF -toiminnon käyttäminen Excelissä | Tämä on toinen kojelaudan olennainen toiminto. Tämä auttaa sinua laskemaan yhteen arvot tietyissä olosuhteissa.