Capitolul 17 · 17.11
Extragerea străzii, a numărului și a completării dintr-o singură coloană de adrese
Adresele dintr-un magazin online, dintr-o evidență a membrilor sau dintr-un fișier vechi de clienți se află adesea într-o singură coloană: „Dorpsstraat 12-2”. Pentru etichete de expediere, un mailing sau un import la o firmă de transport, strada, numărul și completarea trebuie să fie separate. Cu Text în coloane acest lucru nu funcționează bine, deoarece numele străzilor conțin ele însele spații. Separarea prenumelui de nume este descrisă în Despărțirea prenumelui și numelui de familie dintr-o singură coloană; adresele sunt mai dificile. Pentru acestea, Euro-Office are funcțiile REGEXEXTRACT și REGEXREPLACE, care lucrează cu un model de căutare.
Ca exemplu luăm lista de clienți a florăriei Bloemisterij Het Zonnetje, cu următoarele în coloana A:
- Koningin Julianalaan 15 a
- Dorpsstraat 12-2
- Laan 1940-1945 8B
- Kerkplein 7
- Hoofdstraat 120 bis
Pasul 1: numărul cu completarea
Tastați în B2:
=REGEXEXTRACT(A2;"\d+\S*( ?[A-Za-z]{1,3})?$")
Modelul dintre ghilimele înseamnă: una sau mai multe cifre (\d+), apoi eventual caractere fără spațiu (\S*, cum ar fi -2 sau B), apoi eventual un spațiu urmat de 1 până la 3 litere (cum ar fi „ a” sau „ bis”), iar toate acestea chiar la sfârșitul textului ($). Rezultat: 15 a, 12-2, 8B, 7 și 120 bis. Anii din „Laan 1940-1945” nu sunt luați în calcul, deoarece contează doar ultima parte a adresei.
Pasul 2: strada
Tastați în C2:
=TRIM(REGEXREPLACE(A2;"\d+\S*( ?[A-Za-z]{1,3})?$";""))
REGEXREPLACE înlocuiește aceeași porțiune cu nimic, iar TRIM elimină spațiul de la sfârșit. Rămân Koningin Julianalaan, Dorpsstraat, Laan 1940-1945 și așa mai departe.
Pasul 3: numărul și completarea separat
Dacă trebuie separate și numărul și completarea, continuați cu rezultatul din pasul 1 din B2:
- Doar numărul, în D2: =REGEXEXTRACT(B2;"^\d+") (cifrele de la început): 15, 12, 8, 7, 120.
- Completarea, în E2: =TRIM(REGEXREPLACE(B2;"^\d+[- ]?";"")): a, 2, B, nimic, bis.
Apoi trageți formulele în jos până la ultimul rând. Sau lucrați pe coloane: selectați, de exemplu, B2:B6, tastați formula și apăsați Ctrl+Enter; astfel formula este introdusă dintr-odată în toate celulele selectate.

O adresă cu virgule
Dacă întreaga adresă se află într-o singură celulă, de exemplu „Dorpsstraat 12, 1234 AB Utrecht”, atunci =TEXTSPLIT(A9;", ") o împarte la virgulă: „Dorpsstraat 12” ajunge în celula respectivă, iar „1234 AB Utrecht” în celula alăturată. Codul poștal și localitatea le separați apoi cu TEXTBEFORE și TEXTAFTER, care preiau textul dinaintea sau de după al n-lea separator. Dacă „1234 AB Utrecht” se află în C9:
- Cod poștal: =TEXTBEFORE(C9;" ";2) returnează 1234 AB (tot ce se află înaintea celui de-al doilea spațiu).
- Localitate: =TEXTAFTER(C9;" ";2) returnează Utrecht.
Gata? Transformați-le în valori fixe
Coloanele separate sunt formule care se raportează la coloana A. Dacă doriți să ștergeți coloana A sau să trimiteți fișierul cuiva care folosește o versiune mai veche de Excel, transformați mai întâi rezultatele în text obișnuit: selectați coloanele, copiați-le cu Ctrl+C și lipiți-le în același loc cu Ctrl+V. Sub celulele lipite apare un buton (Ctrl). Faceți clic pe el și, sub Valoare, alegeți Lipire numai valoare.

Capcane
- Excepțiile rămân: „Postbus 12”, „Hoofdstraat 3 hs”, „tegenover 14”. Aplicați un filtru pe coloane și verificați rezultatele goale sau ciudate înainte de a folosi lista.
- Majusculele: modelul [A-Za-z] include atât literele mici, cât și majusculele. Dacă folosiți doar [a-z], 8B nu va mai fi recunoscut.
- #N/A la REGEXEXTRACT înseamnă că modelul nu apare, de exemplu la o adresă fără număr.
- Alte foi de calcul: Excel cunoaște aceste funcții REGEX doar în versiunile recente de Microsoft 365. Un coleg cu o versiune mai veche va vedea #NAME?. În acest caz, transformați mai întâi rezultatele în valori.
- Se poate și fără REGEX, dar mai limitat: =TEXTAFTER(A2;" ";-1) returnează tot ce urmează după ultimul spațiu. Acest lucru funcționează pentru „Kerkplein 7” și „Dorpsstraat 12-2”, dar la „15 a” obțineți doar litera a.
