Se on otettu PositSum-blogista. Helpoin tapa jakaa soluja ja sarakkeita, yhdistää soluja ja sarakkeita sekä korjata muita yleisiä OCR-transkriptio- ja tietojen syöttövirheitä Google Sheetsissä on käyttää ilmaista lisäosaa, Paperpusheria. Yleensä Google Sheets hoitaa tämän melko hyvin. Jos minulla on esimerkiksi joukko pilkulla erotettuja arvoja, kuten alla näkyy:
Values: 5, 6, 8, 12
Voin yksinkertaisesti mennä kohtaan Data ja napsauttaa ”Split text to columns”, kuten alla olevassa kuvakaappauksessa näkyy. Voin antaa Googlen selvittää sen automaattisesti tai valita tarvitsemani erottimen. Näin helppoa.
Mutta entä jos erottimet eivät ole johdonmukaisia? Otetaan esimerkiksi seuraavat 9 solua:
Original: 56, 56A, 180 000 koiraa
Toivottu jako 1: 5, 56, 180 000
Toivottu jako 2: 6, A, koiraa
Ensimmäisessä haluan jakaa 56 kahteen soluun, 5 ja 6. Toisessa haluan jakaa 56A numeroihin ja kirjaimeen. Kolmannessa haluan jakaa numeron ja sanan
Jakaakseni näiden välillä minun on käytettävä säännöllisiä lausekkeita. Näin voin tarkastella ”merkkijonoja” eli tekstinpätkiä ja käsitellä niitä sääntöjen perusteella. Mitä yleisempiä säännöt ovat, sitä vähemmän minun täytyy kirjoittaa. Ennen kuin alan käyttää säännöllisiä lausekkeita, minun on myös pakotettava solut tekstiksi, minkä voin tehdä joko kaavakomennolla =TEXT tai seuraavalla valikkovaihtoehdolla.
Suunnitelmani on luoda säännöt, joiden avulla laitan oikean merkkijonon kohtaan ”Haluttu jako 1”, minkä jälkeen käsken Googlen laittaa jäljelle jäävän osan kohtaan ”Haluttu jako 2”. Lähdetään liikkeelle.
Voidakseni valita ”56”:sta ”5”, käsken Googlea valitsemaan merkkijonon ensimmäisen numeron. Teen tämän käyttämällä tätä kaavaa B2-solussa:
=REGEXEXTRACT(A2,””)
Tämä käskee Googlea poimimaan solusta A2 (”56″) minkä tahansa yksittäisen numeron välillä 0-9. Kun olen tehnyt tämän, voin käskeä Googlea laittamaan soluun C2 sen, mitä solusta A2 on jäljellä. Erityinen kaavani solulle C2 on:
=REGEXREPLACE(A2,B2,””)
Tämä käskee Googlen katsoa A2:ta ja korvata A2:ssa olevan ”5:n” millään ””. Käytän tätä samaa kaavaa soluille C3 ja C4.
Solulle B3 minun on kuitenkin keksittävä keino, jolla voin käskeä Googlea poimimaan vain numerot solusta A3. Teen tämän kaavalla
=REGEXEXTRACT(A3, ”*”)
Tämä käskee Googlea poimimaan solusta A3 minkä tahansa numeron, joka toistuu kuinka monta kertaa tahansa *. Saan siis tulokseksi 56. Käytän samaa kaavaa solusta C2 laittaakseni loput soluun C3.
Loppujen lopuksi solulle A4 minun on löydettävä keino, jolla voin käskeä Googlea poimimaan vain numerot. Mutta tässä on eräs käänne: pilkku sisältyy numeroon. Jos poimin numerot ilman pilkkua, jäljelle jää hankala roikkuva pilkku, jossa ei ole mitään järkeä. Käytän siis kaavaa:
=REGEXEXTRACT(A4, ”**”)
Tämä kertoo Googlelle, että se poimii A4:stä minkä tahansa määrän numeroita *, jota seuraa pilkku , jota seuraa mikä tahansa määrä numeroita *. Se poimii sen ja vain sen. Sen jälkeen voin toistaa solun C4 kaavan, jota käytin solun C3 kohdalla.
On selvää, että on mahdotonta yhdistää solujen B2, B3 ja B4 kaavoja, koska B2:ssa halusin erottaa numerot toisistaan, mutta B3:ssa ja B4:ssä halusin pitää ne yhdessä. Voin kuitenkin yhdistää B3:n ja B4:n kaavat ja kertoa Googlelle, että haluan aina poimia numeron riippumatta siitä, onko siinä pilkku vai ei, ja riippumatta siitä, onko viimeisen numeron ja ensimmäisen kirjaimen välissä välilyönti vai ei.
Käytän siis kaavaa:
=REGEXEXTRACT(A3, ”**|*”)
Tässä käytetään OR-operaattoria |, ja se kertoo Googlelle, että se poimii A3:sta joko numeron, joka on muotoiltu pilkulla, kuten ”180 000”, tai | numeron, joka on muotoiltu ilman pilkkua. Huomaa, että järjestys on tärkeä: pyydän Googlea ensin tarkistamaan, onko pilkku, ja jos ei ole, poimimaan numeron ilman pilkkua. Jos järjestys olisi päinvastainen, Google ei tarkistaisi, onko pilkkua, vaan poimisi vain numeron ”180” eli numerot ennen pilkkua.
Solujen ja sarakkeiden jakaminen, solujen ja sarakkeiden yhdistäminen ja muiden yleisten OCR-transkriptio- ja tietojen syöttövirheiden korjaaminen Google Sheetsissä on helpointa ilmaisella lisäosalla, Paperpusherilla. Tutustu siihen!