17. nodaļa · 17.11
Ielas, mājas numura un papildinājuma izdalīšana no vienas adreses kolonnas
Adreses no interneta veikala, biedru reģistra vai veca klientu saraksta bieži ir vienā kolonnā: "Dorpsstraat 12-2". Sūtījumu etiķetēm, masveida sūtījumam vai importam pārvadātāja sistēmā iela, mājas numurs un papildinājums ir jānodala atsevišķi. Ar Teksts par kolonnām tas labi neizdodas, jo pašos ielu nosaukumos ir atstarpes. Vārda un uzvārda sadalīšana ir aprakstīta rakstā Vārda un uzvārda sadalīšana no vienas kolonnas; adreses ir sarežģītākas. Euro-Office šim nolūkam piedāvā funkcijas REGEXEXTRACT un REGEXREPLACE, kas strādā ar meklēšanas paraugu.
Kā piemēru izmantosim uzņēmuma Bloemisterij Het Zonnetje klientu sarakstu, kur kolonnā A ir:
- Koningin Julianalaan 15 a
- Dorpsstraat 12-2
- Laan 1940-1945 8B
- Kerkplein 7
- Hoofdstraat 120 bis
1. darbība: mājas numurs ar papildinājumu
Šūnā B2 ierakstiet:
=REGEXEXTRACT(A2;"\d+\S*( ?[A-Za-z]{1,3})?$")
Paraugs pēdiņās nozīmē: viens vai vairāki cipari (\d+), pēc tam, ja tādas ir, rakstzīmes bez atstarpes (\S*, piemēram, -2 vai B), pēc tam, ja tāda ir, atstarpe ar 1 līdz 3 burtiem (piemēram, " a" vai " bis"), un tas viss pašās teksta beigās ($). Rezultāts: 15 a, 12-2, 8B, 7 un 120 bis. Gadskaitļi nosaukumā "Laan 1940-1945" netiek skarti, jo ņemta vērā tiek tikai adreses pēdējā daļa.
2. darbība: iela
Šūnā C2 ierakstiet:
=TRIM(REGEXREPLACE(A2;"\d+\S*( ?[A-Za-z]{1,3})?$";""))
REGEXREPLACE aizstāj to pašu daļu ar neko, un TRIM noņem atstarpi beigās. Paliek Koningin Julianalaan, Dorpsstraat, Laan 1940-1945 un tā tālāk.
3. darbība: numurs un papildinājums atsevišķi
Ja atsevišķi ir vajadzīgs arī numurs un papildinājums, turpiniet darbu ar 1. darbības rezultātu šūnā B2:
- Tikai numurs, šūnā D2: =REGEXEXTRACT(B2;"^\d+") (cipari sākumā): 15, 12, 8, 7, 120.
- Papildinājums, šūnā E2: =TRIM(REGEXREPLACE(B2;"^\d+[- ]?";"")): a, 2, B, nekas, bis.
Pēc tam velciet formulas uz leju līdz pēdējai rindai. Vai arī strādājiet pa kolonnām: atlasiet, piemēram, B2:B6, ierakstiet formulu un nospiediet Ctrl+Enter; tad formula uzreiz tiek ievadīta visās atlasītajās šūnās.

Adrese ar komatiem
Ja visa adrese ir vienā šūnā, piemēram, "Dorpsstraat 12, 1234 AB Utrecht", tad =TEXTSPLIT(A9;", ") to sadala pie komata: "Dorpsstraat 12" nonāk pašā šūnā, bet "1234 AB Utrecht" blakus šūnā. Pasta indeksu un pilsētu pēc tam varat atdalīt ar TEXTBEFORE un TEXTAFTER, kas atgriež tekstu pirms vai pēc norādītā atdalītāja pēc kārtas. Ja "1234 AB Utrecht" atrodas šūnā C9:
- Pasta indekss: =TEXTBEFORE(C9;" ";2) atgriež 1234 AB (visu pirms otrās atstarpes).
- Pilsēta: =TEXTAFTER(C9;" ";2) atgriež Utrecht.
Gatavs? Pārvērtiet tās par fiksētām vērtībām
Sadalītās kolonnas ir formulas, kas atsaucas uz kolonnu A. Ja vēlaties dzēst kolonnu A vai nosūtīt failu kādam, kam ir vecāka Excel versija, pārvērtiet rezultātus parastā tekstā: atlasiet kolonnas, nokopējiet tās ar Ctrl+C un ielīmējiet tajā pašā vietā ar Ctrl+V. Zem ielīmētajām šūnām parādās poga (Ctrl). Noklikšķiniet uz tās un sadaļā Vērtība izvēlieties Ielīmēt tikai vērtību.

Iespējamās kļūdas
- Izņēmumi paliek: "Postbus 12", "Hoofdstraat 3 hs", "tegenover 14". Pirms saraksta izmantošanas iestatiet kolonnām filtru un pārbaudiet tukšus vai dīvainus rezultātus.
- Lielie burti: paraugs [A-Za-z] ietver gan mazos, gan lielos burtus. Ja izmantosiet tikai [a-z], 8B netiks atrasts.
- #N/A funkcijā REGEXEXTRACT nozīmē, ka paraugs nav atrasts, piemēram, adresei bez mājas numura.
- Citas izklājlapas: Excel šīs REGEX funkcijas pazīst tikai jaunākajās Microsoft 365 versijās. Kolēģis ar vecāku versiju redzēs #NAME?. Tādā gadījumā vispirms pārvērtiet rezultātus vērtībās.
- Var iztikt arī bez REGEX, taču ar ierobežotākām iespējām: =TEXTAFTER(A2;" ";-1) atgriež visu, kas ir pēc pēdējās atstarpes. Tas darbojas ar "Kerkplein 7" un "Dorpsstraat 12-2", bet no "15 a" Jūs iegūsiet tikai a.
