Luku 17 · 17.11
Katu, talonumero ja lisäosa yhdestä osoitesarakkeesta
Verkkokaupan, jäsenrekisterin tai vanhan asiakastiedoston osoitteet ovat usein yhdessä sarakkeessa: "Dorpsstraat 12-2". Osoitetarroja, postitusta tai kuljetusyhtiön tuontia varten katu, talonumero ja lisäosa pitää kuitenkin erottaa toisistaan. Text to Columns ei sovi tähän hyvin, koska katujen nimissäkin on välilyöntejä. Etu- ja sukunimen erottamisesta kerrotaan ohjeessa Etu- ja sukunimen erottaminen yhdestä sarakkeesta; osoitteet ovat hankalampia. Euro-Officessa on tätä varten funktiot REGEXPOIMI ja REGEXKORVAA, jotka toimivat hakukuvion avulla.
Esimerkkinä käytämme Bloemisterij Het Zonnetjen asiakasluetteloa, jonka sarakkeessa A on:
- Koningin Julianalaan 15 a
- Dorpsstraat 12-2
- Laan 1940-1945 8B
- Kerkplein 7
- Hoofdstraat 120 bis
Vaihe 1: talonumero lisäosineen
Kirjoita soluun B2:
=REGEXPOIMI(A2;"\d+\S*( ?[A-Za-z]{1,3})?$")
Lainausmerkkien välissä oleva kuvio tarkoittaa: yksi tai useampi numero (\d+), sen jälkeen mahdollisesti merkkejä ilman välilyöntiä (\S*, kuten -2 tai B), sen jälkeen mahdollisesti välilyönti ja yhdestä kolmeen kirjainta (kuten " a" tai " bis"), ja kaikki tämä aivan tekstin lopussa ($). Tulos: 15 a, 12-2, 8B, 7 ja 120 bis. Vuosiluvut kohdassa "Laan 1940-1945" jäävät pois, koska vain osoitteen viimeinen osa otetaan huomioon.
Vaihe 2: katu
Kirjoita soluun C2:
=POISTA.VÄLIT(REGEXKORVAA(A2;"\d+\S*( ?[A-Za-z]{1,3})?$";""))
REGEXKORVAA korvaa saman osan tyhjällä, ja POISTA.VÄLIT poistaa lopun välilyönnin. Jäljelle jää Koningin Julianalaan, Dorpsstraat, Laan 1940-1945 ja niin edelleen.
Vaihe 3: numero ja lisäosa erikseen
Jos myös numero ja lisäosa pitää erottaa, jatka vaiheen 1 tuloksesta solussa B2:
- Pelkkä numero soluun D2: =REGEXPOIMI(B2;"^\d+") (alun numerot): 15, 12, 8, 7, 120.
- Lisäosa soluun E2: =POISTA.VÄLIT(REGEXKORVAA(B2;"^\d+[- ]?";"")): a, 2, B, ei mitään, bis.
Vedä kaavat sen jälkeen viimeiselle riville asti. Voit myös edetä sarake kerrallaan: valitse esimerkiksi B2:B6, kirjoita kaava ja paina Ctrl+Enter. Kaava lisätään silloin kerralla kaikkiin valittuihin soluihin.

Osoite, jossa on pilkkuja
Jos koko osoite on yhdessä solussa, kuten "Dorpsstraat 12, 1234 AB Utrecht", =TEKSTIJAKO(A9;", ") jakaa sen pilkun kohdalta: "Dorpsstraat 12" tulee itse soluun ja "1234 AB Utrecht" viereiseen soluun. Postinumeron ja paikkakunnan saat sen jälkeen erilleen funktioilla TEKSTI.ENNEN ja TEKSTI.JÄLKEEN, jotka palauttavat tekstin ennen tiettyä erotinta tai sen jälkeen. Kun "1234 AB Utrecht" on solussa C9:
- Postinumero: =TEKSTI.ENNEN(C9;" ";2) palauttaa 1234 AB (kaiken ennen toista välilyöntiä).
- Paikkakunta: =TEKSTI.JÄLKEEN(C9;" ";2) palauttaa Utrecht.
Valmista? Muuta kaavat kiinteiksi arvoiksi
Jaetut sarakkeet ovat kaavoja, jotka viittaavat sarakkeeseen A. Jos haluat poistaa sarakkeen A tai lähettää tiedoston jollekulle, jolla on vanhempi Excel, muuta tulokset tavalliseksi tekstiksi: valitse sarakkeet, kopioi ne näppäimillä Ctrl+C ja liitä ne samaan kohtaan näppäimillä Ctrl+V. Liitettyjen solujen alle ilmestyy pieni (Ctrl)-painike. Napsauta sitä ja valitse kohdasta Arvo Paste only value.

Sudenkuopat
- Poikkeukset jäävät edelleen: "Postbus 12", "Hoofdstraat 3 hs", "tegenover 14". Lisää sarakkeisiin suodatin ja tarkista tyhjät tai oudot tulokset ennen kuin käytät luetteloa.
- Isot kirjaimet: kuvio [A-Za-z] ottaa mukaan sekä pienet että isot kirjaimet. Jos käytät pelkkää [a-z], 8B jää ulkopuolelle.
- #PUUTTUU REGEXPOIMI-funktiossa tarkoittaa, että kuviota ei löydy, esimerkiksi osoitteesta, jossa ei ole talonumeroa.
- Muut taulukko-ohjelmat: Excelissä nämä REGEX-funktiot ovat vain Microsoft 365:n uusimmissa versioissa. Vanhempaa versiota käyttävä kollega näkee virheen #NIMI?. Muuta tulokset silloin ensin arvoiksi.
- Ilman REGEX-funktioitakin onnistuu, mutta rajallisemmin: =TEKSTI.JÄLKEEN(A2;" ";-1) palauttaa kaiken viimeisen välilyönnin jälkeen. Se toimii osoitteille "Kerkplein 7" ja "Dorpsstraat 12-2", mutta osoitteesta "15 a" saat vain a:n.
