17 skyrius · 17.11
Gatvės, namo numerio ir priedo išskyrimas iš vieno adreso stulpelio
Adresai iš internetinės parduotuvės, narių apskaitos ar seno klientų sąrašo dažnai būna viename stulpelyje: „Dorpsstraat 12-2“. Siuntų etiketėms, laiškų siuntimui ar duomenų importui pas vežėją gatvė, namo numeris ir priedas turi būti atskirai. Su Text to Columns (tekstas į stulpelius) tai gerai nepavyks, nes gatvių pavadinimuose patys būna tarpų. Vardo ir pavardės skaidymas aprašytas straipsnyje Vardo ir pavardės atskyrimas iš vieno stulpelio; su adresais sudėtingiau. Tam Euro-Office turi funkcijas REGEXEXTRACT ir REGEXREPLACE, kurios veikia pagal paieškos šabloną.
Pavyzdžiui paimkime gėlių parduotuvės Bloemisterij Het Zonnetje klientų sąrašą, kurio A stulpelyje yra:
- Koningin Julianalaan 15 a
- Dorpsstraat 12-2
- Laan 1940-1945 8B
- Kerkplein 7
- Hoofdstraat 120 bis
1 veiksmas: namo numeris su priedu
Į langelį B2 įveskite:
=REGEXEXTRACT(A2;"\d+\S*( ?[A-Za-z]{1,3})?$")
Šablonas kabutėse reiškia: vienas ar daugiau skaitmenų (\d+), po jų galimi simboliai be tarpo (\S*, pavyzdžiui, -2 arba B), po to galimas tarpas su 1-3 raidėmis (pavyzdžiui, „ a“ arba „ bis“), ir visa tai pačiame teksto gale ($). Rezultatas: 15 a, 12-2, 8B, 7 ir 120 bis. Metai adrese „Laan 1940-1945“ lieka nepaliesti, nes svarbi tik paskutinė adreso dalis.
2 veiksmas: gatvė
Į langelį C2 įveskite:
=TRIM(REGEXREPLACE(A2;"\d+\S*( ?[A-Za-z]{1,3})?$";""))
REGEXREPLACE tą pačią dalį pakeičia niekuo, o TRIM pašalina tarpą gale. Lieka Koningin Julianalaan, Dorpsstraat, Laan 1940-1945 ir taip toliau.
3 veiksmas: numeris ir priedas atskirai
Jei numerį ir priedą taip pat reikia atskirti, toliau dirbkite su 1 veiksmo rezultatu langelyje B2:
- Tik numeris, langelyje D2: =REGEXEXTRACT(B2;"^\d+") (skaitmenys pradžioje): 15, 12, 8, 7, 120.
- Priedas, langelyje E2: =TRIM(REGEXREPLACE(B2;"^\d+[- ]?";"")): a, 2, B, nieko, bis.
Tada nutempkite formules iki paskutinės eilutės. Arba dirbkite pagal stulpelius: pažymėkite, pavyzdžiui, B2:B6, įveskite formulę ir paspauskite Ctrl+Enter; tuomet ji iš karto atsidurs visuose pažymėtuose langeliuose.

Adresas su kableliais
Jei visas adresas yra viename langelyje, pavyzdžiui, „Dorpsstraat 12, 1234 AB Utrecht“, formulė =TEXTSPLIT(A9;", ") jį išskaido ties kableliu: „Dorpsstraat 12“ lieka pačiame langelyje, o „1234 AB Utrecht“ patenka į gretimą langelį. Pašto kodą ir vietovę po to atskirsite su TEXTBEFORE ir TEXTAFTER, kurios paima tekstą prieš arba po nurodyto eilės skyriklio. Jei „1234 AB Utrecht“ yra langelyje C9:
- Pašto kodas: =TEXTBEFORE(C9;" ";2) grąžina 1234 AB (viską prieš antrąjį tarpą).
- Vietovė: =TEXTAFTER(C9;" ";2) grąžina Utrecht.
Baigėte? Paverskite tai pastoviomis reikšmėmis
Išskaidyti stulpeliai yra formulės, kurios remiasi A stulpeliu. Jei norite A stulpelį ištrinti arba siųsti failą asmeniui, kuris naudoja senesnę Excel versiją, paverskite rezultatus paprastu tekstu: pažymėkite stulpelius, nukopijuokite juos su Ctrl+C ir įklijuokite toje pačioje vietoje su Ctrl+V. Po įklijuotais langeliais atsiras mygtukas (Ctrl). Spustelėkite jį ir skiltyje Value (reikšmė) pasirinkite Paste only value (įklijuoti tik reikšmę).

Spąstai
- Išimtys išlieka: „Postbus 12“, „Hoofdstraat 3 hs“, „tegenover 14“. Prieš naudodami sąrašą, uždėkite filtrą ant stulpelių ir patikrinkite tuščius ar keistus rezultatus.
- Didžiosios raidės: šablonas [A-Za-z] apima ir mažąsias, ir didžiąsias raides. Jei naudosite tik [a-z], 8B nebus rastas.
- #N/A prie REGEXEXTRACT reiškia, kad šablonas nerastas, pavyzdžiui, adrese nėra namo numerio.
- Kitos skaičiuoklės: Excel šias REGEX funkcijas turi tik naujausiose Microsoft 365 versijose. Kolega su senesne versija matys #NAME?. Tokiu atveju pirmiausia paverskite rezultatus reikšmėmis.
- Galima apsieiti ir be REGEX, bet galimybės ribotesnės: =TEXTAFTER(A2;" ";-1) grąžina viską po paskutinio tarpo. Tai veikia su „Kerkplein 7“ ir „Dorpsstraat 12-2“, tačiau su „15 a“ gausite tik a.
