Yksinkertainen, ensi silmäyksellä ongelma, jonka ratkaisu ei ole ilmeinen: poimi viimeinen sana tekstiriviltä. No, tai yleisessä tapauksessa viimeinen fragmentti, joka on erotettu tietyllä erottimella (välilyönti, pilkku jne.) Toisin sanoen on tarpeen toteuttaa käänteinen haku (päästä alkuun) merkkijonossa annettu merkki ja pura sitten kaikki sen oikealla puolella olevat merkit.
Katsotaanpa perinteisesti useita tapoja valita: kaavat, makrot ja Power Queryn kautta.
Menetelmä 1. Kaavat
Jotta kaavan ydin ja mekaniikka olisi helpompi ymmärtää, aloitetaan vähän kaukaa. Kasvatetaan ensin lähdetekstimme sanojen välisiä välilyöntejä esimerkiksi 20 kappaleeseen. Voit tehdä tämän korvaustoiminnolla. SUBSTITUTE (KORVAA) ja funktio tietyn merkin toistamiseksi N kertaa – TOISTA (REPT):
Nyt leikataan pois 20 merkkiä tuloksena olevan tekstin lopusta funktiolla OIKEA (OIKEA):
Lämpenee, vai mitä? On vielä poistettava ylimääräiset välilyönnit toiminnolla TRIM (TRIMMATA) ja ongelma ratkeaa:
Englanninkielisessä versiossa kaavamme näyttää tältä:
=TRIM(OIKEA(KORVAA(A1;» «;TOISTA(» «;20));20))
Toivon, että on selvää, että periaatteessa ei ole tarpeen lisätä täsmälleen 20 välilyöntiä - mikä tahansa numero riittää, kunhan se on enemmän kuin lähdetekstin pisimmän sanan pituus.
Ja jos lähdetekstiä ei tarvitse jakaa välilyönnillä, vaan toisella erottimella (esimerkiksi pilkulla), kaavaamme on korjattava hieman:
Menetelmä 2. Makrotoiminto
Tehtävä poimia tekstistä viimeinen sana tai fragmentti voidaan ratkaista myös makrojen avulla, nimittäin kirjoittamalla Visual Basiciin käänteinen hakutoiminto, joka tekee mitä tarvitsemme – etsii tiettyä osamerkkijonoa merkkijonosta vastakkaiseen suuntaan – mistä lopusta alkuun.
Paina pikanäppäintä muut+F11 tai painiketta Visual Basic kieleke kehittäjä (Kehittäjä)avataksesi makroeditorin. Lisää sitten uusi moduuli valikon kautta Insert – moduuli ja kopioi sinne seuraava koodi:
Funktio LastWord(txt merkkijonona, valinnainen erotusmerkki merkkijonona = " ", valinnainen n kokonaislukuna = 1) merkkijonona arFragments = Split(txt, delim) LastWord = arFragments(UBound(arFragments) - n + 1) Loppufunktio
Nyt voit tallentaa työkirjan (makroa tukevassa muodossa!) ja käyttää luotua funktiota seuraavassa syntaksissa:
=LastWord(txt ; delim ; n)
jossa
- txt – solu, jossa on lähdeteksti
- erotin - erotinmerkki (oletus - välilyönti)
- n - mikä sana pitäisi poimia lopusta (oletuksena - ensimmäinen lopusta)
Jos lähdetekstiä muutetaan tulevaisuudessa, makrofunktiomme lasketaan uudelleen lennossa, kuten mikä tahansa tavallinen Excel-taulukkofunktio.
Tapa 3. Power Query
Tehokysely on Microsoftin ilmainen lisäosa, jonka avulla voit tuoda tietoja Exceliin melkein mistä tahansa lähteestä ja muuttaa sitten ladatut tiedot mihin tahansa muotoon. Tämän lisäosan teho ja viileys ovat niin suuria, että Microsoft on oletuksena rakentanut kaikki sen ominaisuudet Excel 2016:een. Excel 2010-2013 Power Queryn voi ladata ilmaiseksi täältä.
Tehtävämme erottaa viimeinen sana tai fragmentti tietyn erottimen kautta Power Queryn avulla ratkaistaan erittäin helposti.
Tehdään ensin tietotaulukkomme älykkääksi taulukoksi pikanäppäimillä. Ctrl+T tai käskyt Etusivu – Muotoile taulukoksi (Etusivu — Muotoile taulukkona):
Sitten lataamme luodun "älykkaan taulukon" Power Queryyn komennolla Pöydästä/valikoimasta (taulukosta/alueelta) kieleke Päiväys (jos sinulla on Excel 2016) tai välilehdellä Tehokysely (jos sinulla on Excel 2010-2013):
Avautuvan kyselyn muokkausikkunan välilehdellä Muutos (Muuttaa) valitse joukkue Jaettu sarake – erottimen mukaan (Jaettu sarake – erottimen mukaan) ja sitten on vielä asetettava erotinmerkki ja valittava vaihtoehto Oikeanpuoleisin erotinei leikata kaikkia sanoja, vaan vain viimeistä:
Napsauttamisen jälkeen OK viimeinen sana erotetaan uuteen sarakkeeseen. Tarpeeton ensimmäinen sarake voidaan poistaa napsauttamalla hiiren oikealla painikkeella sen otsikkoa ja valitsemalla poista (Poistaa). Voit myös nimetä uudelleen jäljellä olevan sarakkeen taulukon otsikossa.
Tulokset voidaan ladata takaisin arkille komennolla Etusivu — Sulje ja lataa — Sulje ja lataa… (Etusivu — Sulje & Lataa — Sulje & Lataa…):
Ja tuloksena saamme:
Näin – halpaa ja iloista, ilman kaavoja ja makroja, melkein koskematta näppäimistöön 🙂
Jos alkuperäinen luettelo muuttuu tulevaisuudessa, riittää, kun napsautat hiiren kakkospainiketta tai käytät pikanäppäintä Ctrl+muut+F5 päivittää pyyntömme.
- Tarttuvan tekstin jakaminen sarakkeisiin
- Tekstin jäsentäminen ja jäsentäminen säännöllisillä lausekkeilla
- Poimitaan ensimmäiset sanat tekstistä SUBSTITUTE-funktiolla