17. fejezet · 17.11
Utca, házszám és kiegészítés kinyerése egyetlen címoszlopból
A webáruházból, taglistából vagy régi ügyfélnyilvántartásból származó címek gyakran egyetlen oszlopban szerepelnek: „Dorpsstraat 12-2”. Szállítási címkékhez, körleveles kiküldéshez vagy egy futárszolgálatnál történő importáláshoz viszont az utcát, a házszámot és a kiegészítést külön kell választani. A Szöveg oszlopokká funkcióval ez nem működik jól, mert az utcanevekben is vannak szóközök. A vezeték- és utónév szétválasztásáról a(z) Keresztnév és vezetéknév szétválasztása egyetlen oszlopból című cikk szól; a címek ennél bonyolultabbak. Erre az Euro-Office a REGEXKIVONÁS és a REGEXCSERE függvényt kínálja, amelyek keresési mintával dolgoznak.
Példaként a Bloemisterij Het Zonnetje ügyféllistáját vesszük, amelynek A oszlopában a következők állnak:
- Koningin Julianalaan 15 a
- Dorpsstraat 12-2
- Laan 1940-1945 8B
- Kerkplein 7
- Hoofdstraat 120 bis
1. lépés: a házszám a kiegészítéssel
Írja be a B2 cellába:
=REGEXKIVONÁS(A2;"\d+\S*( ?[A-Za-z]{1,3})?$")
Az idézőjelek közötti minta jelentése: egy vagy több számjegy (\d+), utána esetleg szóköz nélküli karakterek (\S*, például -2 vagy B), utána esetleg egy szóköz 1-3 betűvel (például „ a” vagy „ bis”), mindez a szöveg legvégén ($). Az eredmény: 15 a, 12-2, 8B, 7 és 120 bis. A „Laan 1940-1945” évszámai nem kerülnek bele, mert csak a cím utolsó része számít.
2. lépés: az utca
Írja be a C2 cellába:
=KIMETSZ(REGEXCSERE(A2;"\d+\S*( ?[A-Za-z]{1,3})?$";""))
A REGEXCSERE ugyanezt a részt semmire cseréli, a KIMETSZ pedig eltávolítja a végén maradt szóközt. Így marad a Koningin Julianalaan, a Dorpsstraat, a Laan 1940-1945 és így tovább.
3. lépés: a szám és a kiegészítés külön
Ha a számot és a kiegészítést is külön kell választani, dolgozzon tovább az 1. lépés B2 cellában lévő eredményével:
- Csak a szám, a D2 cellában: =REGEXKIVONÁS(B2;"^\d+") (az elején álló számjegyek): 15, 12, 8, 7, 120.
- A kiegészítés, az E2 cellában: =KIMETSZ(REGEXCSERE(B2;"^\d+[- ]?";"")): a, 2, B, semmi, bis.
Ezután húzza le a képleteket az utolsó sorig. Vagy dolgozzon oszloponként: jelölje ki például a B2:B6 tartományt, írja be a képletet, és nyomja meg a Ctrl+Enter billentyűkombinációt; így a képlet egyszerre az összes kijelölt cellába bekerül.

Vesszőkkel tagolt cím
Ha a teljes cím egy cellában áll, például „Dorpsstraat 12, 1234 AB Utrecht”, akkor a =SZÖVEGFELOSZTÁS(A9;", ") a vesszőnél bontja szét: a „Dorpsstraat 12” magába a cellába, az „1234 AB Utrecht” pedig a mellette lévő cellába kerül. Az irányítószámot és a települést ezután a SZÖVEGELŐTTE és a SZÖVEGUTÁNA függvénnyel választhatja szét, amelyek az n-edik elválasztó előtti vagy utáni szöveget adják vissza. Ha az „1234 AB Utrecht” a C9 cellában áll:
- Irányítószám: a =SZÖVEGELŐTTE(C9;" ";2) eredménye 1234 AB (minden a második szóköz előtt).
- Település: a =SZÖVEGUTÁNA(C9;" ";2) eredménye Utrecht.
Kész? Alakítsa fix értékekké
A szétválasztott oszlopok az A oszlopra hivatkozó képletek. Ha törölni szeretné az A oszlopot, vagy a fájlt valakinek egy régebbi Excel-verzióval küldi el, alakítsa az eredményeket egyszerű szöveggé: jelölje ki az oszlopokat, másolja őket a Ctrl+C billentyűkombinációval, majd illessze be ugyanoda a Ctrl+V billentyűkombinációval. A beillesztett cellák alatt megjelenik egy kis (Ctrl) gomb. Kattintson rá, és az Érték csoportban válassza a Csak érték beillesztése lehetőséget.

Buktatók
- Kivételek mindig maradnak: „Postbus 12”, „Hoofdstraat 3 hs”, „tegenover 14”. Mielőtt a listát használná, állítson be szűrőt az oszlopokra, és ellenőrizze az üres vagy furcsa eredményeket.
- Kis- és nagybetűk: az [A-Za-z] minta a kis- és a nagybetűket is felismeri. Ha csak [a-z] mintát használ, a 8B kimarad.
- A REGEXKIVONÁS függvénynél megjelenő #HIÁNYZIK azt jelenti, hogy a minta nem fordul elő, például egy házszám nélküli címnél.
- Más táblázatkezelők: az Excel ezeket a REGEX függvényeket csak a Microsoft 365 újabb verzióiban ismeri. Egy régebbi verziót használó kolléga #NÉV? hibát lát. Ilyenkor előbb alakítsa az eredményeket értékekké.
- REGEX nélkül is megoldható, de korlátozottabban: a =SZÖVEGUTÁNA(A2;" ";-1) az utolsó szóköz utáni részt adja vissza. Ez a „Kerkplein 7” és a „Dorpsstraat 12-2” esetében működik, a „15 a” esetében viszont csak az a betűt kapja meg.
