Chapitre 17 · 17.11
Extraire la rue, le numéro et le complément d'une colonne d'adresses
Les adresses provenant d'une boutique en ligne, d'un fichier d'adhérents ou d'un ancien fichier clients se trouvent souvent dans une seule colonne : "Dorpsstraat 12-2". Pour des étiquettes d'expédition, un publipostage ou une importation chez un transporteur, la rue, le numéro et le complément doivent être séparés. La fonction Texte en colonnes n'y parvient pas correctement, car les noms de rue contiennent eux-mêmes des espaces. La séparation du prénom et du nom est expliquée dans Séparer le prénom et le nom de famille à partir d'une seule colonne ; les adresses sont plus délicates. Euro-Office propose pour cela les fonctions REGEX.EXTRAIRE et REGEX.REMPLACER, qui fonctionnent avec un motif de recherche.
Prenons comme exemple la liste de clients de Bloemisterij Het Zonnetje, avec dans la colonne A :
- Koningin Julianalaan 15 a
- Dorpsstraat 12-2
- Laan 1940-1945 8B
- Kerkplein 7
- Hoofdstraat 120 bis
Étape 1 : le numéro avec son complément
Saisissez dans B2 :
=REGEX.EXTRAIRE(A2;"\d+\S*( ?[A-Za-z]{1,3})?$")
Le motif entre guillemets signifie : un ou plusieurs chiffres (\d+), puis éventuellement des caractères sans espace (\S*, comme -2 ou B), puis éventuellement un espace suivi de 1 à 3 lettres (comme " a" ou " bis"), le tout tout à la fin du texte ($). Résultat : 15 a, 12-2, 8B, 7 et 120 bis. Les années dans "Laan 1940-1945" ne sont pas prises en compte, car seule la dernière partie de l'adresse compte.
Étape 2 : la rue
Saisissez dans C2 :
=SUPPRESPACE(REGEX.REMPLACER(A2;"\d+\S*( ?[A-Za-z]{1,3})?$";""))
REGEX.REMPLACER remplace ce même passage par rien, et SUPPRESPACE supprime l'espace à la fin. Il vous reste Koningin Julianalaan, Dorpsstraat, Laan 1940-1945 et ainsi de suite.
Étape 3 : séparer le numéro et le complément
Si le numéro et le complément doivent eux aussi être séparés, poursuivez avec le résultat de l'étape 1 dans B2 :
- Le numéro seul, dans D2 : =REGEX.EXTRAIRE(B2;"^\d+") (les chiffres au début) : 15, 12, 8, 7, 120.
- Le complément, dans E2 : =SUPPRESPACE(REGEX.REMPLACER(B2;"^\d+[- ]?";"")) : a, 2, B, rien, bis.
Recopiez ensuite les formules jusqu'à la dernière ligne. Vous pouvez aussi travailler colonne par colonne : sélectionnez par exemple B2:B6, saisissez la formule et appuyez sur Ctrl+Entrée ; elle est alors placée en une seule fois dans toutes les cellules sélectionnées.

Une adresse avec des virgules
Si l'adresse complète se trouve dans une seule cellule, comme "Dorpsstraat 12, 1234 AB Utrecht", =FRACTIONNER.TEXTE(A9;", ") la divise au niveau de la virgule : "Dorpsstraat 12" se place dans la cellule elle-même et "1234 AB Utrecht" dans la cellule voisine. Vous séparez ensuite le code postal et la ville avec TEXTE.AVANT et TEXTE.APRES, qui renvoient le texte situé avant ou après le énième séparateur. Si "1234 AB Utrecht" se trouve dans C9 :
- Code postal : =TEXTE.AVANT(C9;" ";2) renvoie 1234 AB (tout ce qui précède le deuxième espace).
- Ville : =TEXTE.APRES(C9;" ";2) renvoie Utrecht.
Terminé ? Convertissez en valeurs fixes
Les colonnes séparées sont des formules qui font référence à la colonne A. Si vous souhaitez supprimer la colonne A, ou envoyer le fichier à quelqu'un qui utilise une version plus ancienne d'Excel, convertissez d'abord les résultats en texte ordinaire : sélectionnez les colonnes, copiez-les avec Ctrl+C et collez-les au même endroit avec Ctrl+V. Un petit bouton (Ctrl) apparaît sous les cellules collées. Cliquez dessus et choisissez, sous Valeur, l'option Coller uniquement la valeur.

Pièges à éviter
- Exceptions : elles subsistent, par exemple "Postbus 12", "Hoofdstraat 3 hs", "tegenover 14". Appliquez un filtre aux colonnes et vérifiez les résultats vides ou étranges avant d'utiliser la liste.
- Majuscules : le motif [A-Za-z] prend en compte les minuscules et les majuscules. Si vous utilisez uniquement [a-z], 8B ne sera pas reconnu.
- #N/A avec REGEX.EXTRAIRE signifie que le motif est introuvable, par exemple pour une adresse sans numéro.
- Autres tableurs : Excel ne connaît ces fonctions REGEX que dans les versions récentes de Microsoft 365. Un collègue disposant d'une version plus ancienne verra #NOM?. Convertissez donc d'abord les résultats en valeurs.
- Sans REGEX, c'est aussi possible, mais de façon plus limitée : =TEXTE.APRES(A2;" ";-1) renvoie tout ce qui suit le dernier espace. Cela fonctionne pour "Kerkplein 7" et "Dorpsstraat 12-2", mais avec "15 a" vous n'obtenez que le a.
