Yleinen kaava
=LOWER(TRIM(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(A1,"("," "),")"," "),"-"," "),":"," "),";"," "),"!"," "),","," "),"."," ")))
Yhteenveto
Voit poistaa osan tekstin luonnollisesta monimutkaisuudesta (välimerkit, kirjainten normalisointi, ylimääräisten välilyöntien poistaminen) käyttämällä SUBSTITUTE-funktioon perustuvaa kaavaa TRIM- ja LOWER-toimintojen avulla.
Asiayhteys
Joskus joudut poistamaan osan tekstin vaihtelevuudesta ennen muuta käsittelyä. Yksi esimerkki on, kun haluat laskea tiettyjä sanoja suurempien tekstimerkkijonojen sisällä. Koska Excel ei tue säännöllisiä lausekkeita, et voi luoda tarkkoja vastaavuuksia. Esimerkiksi, jos haluat laskea, kuinka monta kertaa sana "kettu" esiintyy solussa, lasket lopulta "ketut". Voit etsiä "ketua" (välilyönnillä), mutta se epäonnistuu "ketun" tai "ketun" kanssa. Yksi kiertotapa on yksinkertaistaa tekstiä ensin kaavalla auttaja-sarakkeessa, sitten suorittaa laskenta yksinkertaistetussa versiossa. Tämän sivun esimerkki näyttää yhden tavan tehdä tämä.
Selitys
Tässä esimerkissä esitetty kaava käyttää sarjaa sisäkkäisiä SUBSTITUTE-funktioita sulkujen, yhdysmerkkien, kaksoispisteiden, puolipisteiden, huutomerkkien, pilkujen ja pisteiden poistamiseksi. Prosessi kulkee sisältä ulospäin, jolloin kukin SUBSTITUTE korvaa yhden merkin yhdellä välilyönnillä ja antaa sen jälkeen seuraavalle SUBSTITUTE. Sisempi korvike poistaa vasemman sulun ja tulos luovutetaan seuraavalle korvikkeelle, joka poistaa oikeat sulut ja niin edelleen.
Alla olevassa versiossa rivinvaihdot on lisätty luettavuuden ja korvausten muokkaamisen helpottamiseksi. Excel ei välitä rivikaukoista kaavoissa, joten voit käyttää kaavaa sellaisenaan.
= LOWER( TRIM( SUBSTITUTE( SUBSTITUTE( SUBSTITUTE( SUBSTITUTE( SUBSTITUTE( SUBSTITUTE( SUBSTITUTE( SUBSTITUTE( A1, "("," "), ")"," "), "-"," "), ":"," "), ";"," "), "!"," "), ","," "), "."," ")))
Kun kaikki korvaukset on suoritettu, tulos ajetaan TRIM: n läpi tilojen normalisoimiseksi ja sitten LOWER-toiminto pakottaa kaiken tekstin pieniksi.
Huomaa: Sinun on mukautettava todelliset korvaukset tietojesi mukaan.
Lisäämällä etu- ja takatila
Joissakin tapauksissa kannattaa ehkä lisätä välilyönti puhdistetun tekstin alkuun ja loppuun. Esimerkiksi, jos haluat laskea sanat tarkasti, voit etsiä sanaa välilyöntien ympäröimänä (eli etsiä sanoja "kettu", "kartta") väärän vastaavuuden välttämiseksi. Lisää etu- ja lopputila vain liittämällä välilyönti ("") alkuun ja loppuun:
=" "&formula&" "
Jossa "kaava" on yllä oleva pidempi kaava.
