Kapitola 17 · 17.11
Ulica, číslo domu a dodatok z jedného stĺpca s adresou
Adresy z e-shopu, evidencie členov alebo starého zoznamu zákazníkov sú často v jednom stĺpci: "Dorpsstraat 12-2". Pre zasielacie štítky, hromadnú korešpondenciu alebo import k prepravcovi musia byť ulica, číslo domu a dodatok oddelené. Funkcia Text na stĺpec si s tým dobre neporadí, pretože aj samotné názvy ulíc obsahujú medzery. Rozdelenie mena a priezviska nájdete v článku Rozdelenie mena a priezviska z jedného stĺpca; adresy sú náročnejšie. Euro-Office má na to funkcie REGEXEXTRACT a REGEXREPLACE, ktoré pracujú s vyhľadávacím vzorom.
Ako príklad použijeme zoznam zákazníkov kvetinárstva Bloemisterij Het Zonnetje, ktorý má v stĺpci A:
- Koningin Julianalaan 15 a
- Dorpsstraat 12-2
- Laan 1940-1945 8B
- Kerkplein 7
- Hoofdstraat 120 bis
Krok 1: číslo domu s dodatkom
Do bunky B2 zadajte:
=REGEXEXTRACT(A2;"\d+\S*( ?[A-Za-z]{1,3})?$")
Vzor v úvodzovkách znamená: jedna alebo viac číslic (\d+), potom prípadne znaky bez medzery (\S*, napríklad -2 alebo B), potom prípadne medzera s 1 až 3 písmenami (napríklad " a" alebo " bis"), a to všetko úplne na konci textu ($). Výsledok: 15 a, 12-2, 8B, 7 a 120 bis. Letopočty v "Laan 1940-1945" zostanú nedotknuté, pretože sa berie do úvahy iba posledná časť adresy.
Krok 2: ulica
Do bunky C2 zadajte:
=TRIM(REGEXREPLACE(A2;"\d+\S*( ?[A-Za-z]{1,3})?$";""))
REGEXREPLACE nahradí rovnakú časť ničím a TRIM odstráni medzeru na konci. Zostane vám Koningin Julianalaan, Dorpsstraat, Laan 1940-1945 a tak ďalej.
Krok 3: číslo a dodatok samostatne
Ak potrebujete oddeliť aj číslo a dodatok, pokračujte s výsledkom z kroku 1 v bunke B2:
- Iba číslo, v bunke D2: =REGEXEXTRACT(B2;"^\d+") (číslice na začiatku): 15, 12, 8, 7, 120.
- Dodatok, v bunke E2: =TRIM(REGEXREPLACE(B2;"^\d+[- ]?";"")): a, 2, B, nič, bis.
Potom vzorce potiahnite až po posledný riadok. Alebo postupujte po stĺpcoch: vyberte napríklad B2:B6, zadajte vzorec a stlačte Ctrl+Enter; vzorec sa naraz vloží do všetkých vybraných buniek.

Adresa s čiarkami
Ak je celá adresa v jednej bunke, napríklad "Dorpsstraat 12, 1234 AB Utrecht", vzorec =TEXTSPLIT(A9;", ") ju rozdelí podľa čiarky: "Dorpsstraat 12" zostane v samotnej bunke a "1234 AB Utrecht" sa zobrazí v susednej bunke. PSČ a obec potom oddelíte pomocou TEXTBEFORE a TEXTAFTER, ktoré vrátia text pred alebo za n-tým oddeľovačom. Ak je "1234 AB Utrecht" v bunke C9:
- PSČ: =TEXTBEFORE(C9;" ";2) vráti 1234 AB (všetko pred druhou medzerou).
- Obec: =TEXTAFTER(C9;" ";2) vráti Utrecht.
Hotovo? Premeňte výsledky na pevné hodnoty
Rozdelené stĺpce sú vzorce, ktoré odkazujú na stĺpec A. Ak chcete stĺpec A odstrániť alebo poslať súbor niekomu so staršou verziou Excelu, preveďte výsledky na obyčajný text: vyberte stĺpce, skopírujte ich pomocou Ctrl+C a vložte ich na to isté miesto pomocou Ctrl+V. Pod vloženými bunkami sa zobrazí tlačidlo (Ctrl). Kliknite naň a v časti Hodnota zvoľte Vložiť iba hodnotu.

Na čo si dať pozor
- Výnimky zostávajú: "Postbus 12", "Hoofdstraat 3 hs", "tegenover 14". Pred použitím zoznamu nastavte na stĺpce filter a skontrolujte prázdne alebo zvláštne výsledky.
- Veľké písmená: vzor [A-Za-z] zahŕňa malé aj veľké písmená. Ak použijete iba [a-z], 8B sa nezachytí.
- #N/A pri REGEXEXTRACT znamená, že sa vzor nenašiel, napríklad pri adrese bez čísla domu.
- Iné tabuľkové procesory: Excel pozná tieto funkcie REGEX iba v novších verziách Microsoft 365. Kolega so staršou verziou uvidí #NAME?. V takom prípade najprv preveďte výsledky na hodnoty.
- Ide to aj bez REGEX, ale s obmedzeniami: =TEXTAFTER(A2;" ";-1) vráti všetko za poslednou medzerou. Funguje to pre "Kerkplein 7" a "Dorpsstraat 12-2", ale pri "15 a" dostanete iba a.
