Kapitola 17 · 17.11
Rozdělení sloupce s adresou na ulici, číslo domu a doplněk
Adresy z e-shopu, členské evidence nebo starého seznamu zákazníků bývají často v jednom sloupci: "Dorpsstraat 12-2". Pro adresní štítky, hromadnou korespondenci nebo import k přepravci však potřebujete ulici, číslo domu a doplněk zvlášť. Funkce Text na sloupce si s tím dobře neporadí, protože samotné názvy ulic obsahují mezery. Rozdělení jména a příjmení najdete v článku Rozdělení jména a příjmení z jednoho sloupce; adresy jsou složitější. Euro-Office na to má funkce REGEXEXTRAHOVAT a REGEXNAHRADIT, které pracují s vyhledávacím vzorem.
Jako příklad použijeme seznam zákazníků květinářství Bloemisterij Het Zonnetje, který má ve sloupci A:
- Koningin Julianalaan 15 a
- Dorpsstraat 12-2
- Laan 1940-1945 8B
- Kerkplein 7
- Hoofdstraat 120 bis
Krok 1: číslo domu s doplňkem
Do buňky B2 napište:
=REGEXEXTRAHOVAT(A2;"\d+\S*( ?[A-Za-z]{1,3})?$")
Vzor v uvozovkách znamená: jedna nebo více číslic (\d+), poté případně znaky bez mezery (\S*, například -2 nebo B), poté případně mezera s 1 až 3 písmeny (například " a" nebo " bis"), a to vše úplně na konci textu ($). Výsledek: 15 a, 12-2, 8B, 7 a 120 bis. Letopočty v "Laan 1940-1945" zůstanou nedotčeny, protože se bere v úvahu jen poslední část adresy.
Krok 2: ulice
Do buňky C2 napište:
=PROČISTIT(REGEXNAHRADIT(A2;"\d+\S*( ?[A-Za-z]{1,3})?$";""))
REGEXNAHRADIT nahradí stejnou část ničím a PROČISTIT odstraní mezeru na konci. Zůstane Vám Koningin Julianalaan, Dorpsstraat, Laan 1940-1945 a tak dále.
Krok 3: číslo a doplněk zvlášť
Potřebujete-li mít zvlášť i číslo a doplněk, pokračujte s výsledkem kroku 1 v buňce B2:
- Pouze číslo, v D2: =REGEXEXTRAHOVAT(B2;"^\d+") (číslice na začátku): 15, 12, 8, 7, 120.
- Doplněk, v E2: =PROČISTIT(REGEXNAHRADIT(B2;"^\d+[- ]?";"")): a, 2, B, nic, bis.
Poté vzorce přetáhněte až k poslednímu řádku. Nebo postupujte po sloupcích: označte například B2:B6, napište vzorec a stiskněte Ctrl+Enter; vzorec se tak najednou vloží do všech označených buněk.

Adresa s čárkami
Pokud je celá adresa v jedné buňce, například "Dorpsstraat 12, 1234 AB Utrecht", rozdělí ji =ROZDĚLIT.TEXT(A9;", ") podle čárky: "Dorpsstraat 12" se zobrazí v buňce se vzorcem a "1234 AB Utrecht" v sousední buňce. PSČ a obec pak oddělíte pomocí TEXTPŘED a TEXTZA, které vracejí text před nebo za n-tým oddělovačem. Je-li "1234 AB Utrecht" v buňce C9:
- PSČ: =TEXTPŘED(C9;" ";2) vrátí 1234 AB (vše před druhou mezerou).
- Obec: =TEXTZA(C9;" ";2) vrátí Utrecht.
Hotovo? Převeďte je na pevné hodnoty
Rozdělené sloupce jsou vzorce, které odkazují na sloupec A. Chcete-li sloupec A odstranit nebo soubor poslat někomu se starší verzí Excelu, převeďte výsledky na běžný text: označte sloupce, zkopírujte je pomocí Ctrl+C a vložte je na stejné místo pomocí Ctrl+V. Pod vloženými buňkami se objeví tlačítko (Ctrl). Klikněte na něj a v části Hodnota zvolte Vložit pouze hodnotu.

Na co si dát pozor
- Výjimky zůstanou: "Postbus 12", "Hoofdstraat 3 hs", "tegenover 14". Než seznam použijete, nastavte na sloupce filtr a zkontrolujte prázdné nebo podivné výsledky.
- Velká písmena: vzor [A-Za-z] zahrnuje malá i velká písmena. Použijete-li jen [a-z], 8B se nezachytí.
- #N/A u funkce REGEXEXTRAHOVAT znamená, že se vzor v textu nevyskytuje, například u adresy bez čísla domu.
- Jiné tabulkové procesory: Excel tyto funkce REGEX zná jen v novějších verzích Microsoft 365. Kolega se starší verzí uvidí #NÁZEV?. Výsledky proto nejprve převeďte na hodnoty.
- Bez REGEX to jde také, ale s omezeními: =TEXTZA(A2;" ";-1) vrátí vše za poslední mezerou. To funguje pro "Kerkplein 7" a "Dorpsstraat 12-2", ale u "15 a" získáte jen a.
