17. peatükk · 17.11
Tänav, majanumber ja lisand ühest aadressiveerust
Veebipoe, liikmeregistri või vana kliendifaili aadressid on sageli ühes veerus: „Dorpsstraat 12-2“. Saadetiste siltide, postituse või vedaja süsteemi importimise jaoks peavad tänav, majanumber ja lisand olema eraldi. Funktsiooniga Text to Columns (tekst veergudesse) see hästi ei õnnestu, sest tänavanimedes endis on tühikud. Ees- ja perekonnanime eraldamisest räägib Eesnime ja perekonnanime eraldamine ühest veerust; aadressid on keerulisemad. Euro-Office'is on selleks funktsioonid REGEXEXTRACT ja REGEXREPLACE, mis töötavad otsingumustriga.
Näiteks võtame ettevõtte Bloemisterij Het Zonnetje kliendiloendi, mille veerus A on:
- Koningin Julianalaan 15 a
- Dorpsstraat 12-2
- Laan 1940-1945 8B
- Kerkplein 7
- Hoofdstraat 120 bis
1. samm: majanumber koos lisandiga
Sisesta lahtrisse B2:
=REGEXEXTRACT(A2;"\d+\S*( ?[A-Za-z]{1,3})?$")
Jutumärkide vahel olev muster tähendab: üks või mitu numbrit (\d+), seejärel soovi korral tühikuta märgid (\S*, näiteks -2 või B), seejärel soovi korral tühik ja 1 kuni 3 tähte (näiteks „ a“ või „ bis“), ja seda kõike päris teksti lõpus ($). Tulemus: 15 a, 12-2, 8B, 7 ja 120 bis. Aastaarvud aadressis „Laan 1940-1945“ jäävad puutumata, sest arvesse läheb ainult aadressi viimane osa.
2. samm: tänav
Sisesta lahtrisse C2:
=TRIM(REGEXREPLACE(A2;"\d+\S*( ?[A-Za-z]{1,3})?$";""))
REGEXREPLACE asendab sama osa tühjusega ja TRIM eemaldab lõpust tühiku. Alles jääb Koningin Julianalaan, Dorpsstraat, Laan 1940-1945 ja nii edasi.
3. samm: number ja lisand eraldi
Kui ka number ja lisand peavad olema eraldi, jätka 1. sammu tulemusega lahtris B2:
- Ainult number, lahtrisse D2: =REGEXEXTRACT(B2;"^\d+") (numbrid alguses): 15, 12, 8, 7, 120.
- Lisand, lahtrisse E2: =TRIM(REGEXREPLACE(B2;"^\d+[- ]?";"")): a, 2, B, mitte midagi, bis.
Seejärel lohista valemid viimase reani. Või tööta veergude kaupa: vali näiteks B2:B6, sisesta valem ja vajuta Ctrl+Enter; nii jõuab valem korraga kõigisse valitud lahtritesse.

Komadega aadress
Kui kogu aadress on ühes lahtris, näiteks „Dorpsstraat 12, 1234 AB Utrecht“, jagab =TEXTSPLIT(A9;", ") selle koma kohalt: „Dorpsstraat 12“ jääb lahtrisse endasse ja „1234 AB Utrecht“ kõrvallahtrisse. Postiindeksi ja linna saad seejärel lahutada funktsioonidega TEXTBEFORE ja TEXTAFTER, mis võtavad teksti enne või pärast n-ndat eraldajat. Kui „1234 AB Utrecht“ on lahtris C9:
- Postiindeks: =TEXTBEFORE(C9;" ";2) annab 1234 AB (kõik enne teist tühikut).
- Linn: =TEXTAFTER(C9;" ";2) annab Utrecht.
Valmis? Tee neist püsiväärtused
Jaotatud veerud on valemid, mis viitavad veerule A. Kui tahad veeru A kustutada või saata faili kellelegi, kellel on vanem Excel, teisenda tulemused tavaliseks tekstiks: vali veerud, kopeeri need klahvidega Ctrl+C ja kleebi samasse kohta klahvidega Ctrl+V. Kleebitud lahtrite alla ilmub väike nupp (Ctrl). Klõpsa sellel ja vali jaotises Value (väärtus) Paste only value (kleebi ainult väärtus).

Lõksud
- Erandid jäävad alles: „Postbus 12“, „Hoofdstraat 3 hs“, „tegenover 14“. Lisa veergudele filter ja vaata enne loendi kasutamist üle tühjad või kummalised tulemused.
- Suurtähed: muster [A-Za-z] arvestab nii väike- kui ka suurtähti. Kui kasutad ainult [a-z], jääb 8B välja.
- #N/A funktsiooni REGEXEXTRACT puhul tähendab, et mustrit ei leitud, näiteks majanumbrita aadressi korral.
- Teised arvutustabelid: Excelis on need REGEX-funktsioonid olemas ainult Microsoft 365 uuemates versioonides. Vanema versiooniga kolleeg näeb #NAME?. Teisenda tulemused siis enne väärtusteks.
- Ka ilma REGEX-ita saab, kuid piiratumalt: =TEXTAFTER(A2;" ";-1) annab kõik pärast viimast tühikut. See sobib aadressidele „Kerkplein 7“ ja „Dorpsstraat 12-2“, kuid „15 a“ puhul saad ainult a.
