Tom travaille avec de nombreuses listes d’adresses. Une chose qui le rend fou, c’est quand l’adresse postale est dans une seule cellule. Tom a besoin du numéro de maison dans une cellule et de la rue dans une cellule différente. Donc, si l’adresse est « 1234 Maple Glen Ave. », alors il a besoin de « 1234 » dans une cellule et de « Maple Glen Ave. » en autre. Il peut copier le numéro de rue dans une colonne placée avant le nom de la rue, mais c’est un long processus pour retaper les numéros et les éliminer du nom de la rue. Tom se demande s’il existe un moyen plus simple de «séparer» le numéro de maison du nom de la rue.

Il existe plusieurs façons de mener à bien cette tâche. Certains pourraient penser que l’utilisation de l’outil Texte en colonnes (sur l’onglet Données du ruban)

donnerait les résultats escomptés. Malheureusement, ce n’est pas la meilleure façon de séparer le numéro de maison. La raison est simple: si vous utilisez l’outil en spécifiant un délimiteur, le seul qui ait un sens est l’espace. Mais si vous faites cela, vous vous retrouvez avec « 1234 Maple Glen Ave. » répartis sur quatre colonnes au lieu des deux souhaités. Vous devrez ensuite trouver un moyen de recoller le nom de la rue.

L’autre façon d’utiliser potentiellement l’outil Texte dans les colonnes consiste à utiliser une largeur de colonne fixe (au lieu d’un délimiteur). Cette approche ne fonctionnera que si toutes vos adresses ont le même nombre de chiffres dans le numéro de maison. Dans la plupart des ensembles de données, bien sûr, ce ne sera pas le cas. Les numéros de maison peuvent être à peu près n’importe quel nombre de chiffres, et ils peuvent même inclure des caractères non numériques (tels que « 1234A », « 1234-B » ou « 1234-36 »).

Une approche qui fonctionnera consiste à utiliser des formules pour séparer les adresses d’origine. Vos formules peuvent saisir le premier espace de l’adresse, renvoyant soit la partie à gauche de l’espace, soit la partie à droite. Voici la façon de tout extraire avant l’espace, étant donné une adresse dans la cellule A1:

=LEFT(A1,FIND(" ",A1)-1)

Si vous êtes absolument certain que les adresses ne contiendront pas de caractères non numériques, vous pouvez envelopper la formule dans la fonction VALEUR afin de vous retrouver avec le numéro de maison sous forme de valeur numérique:

=VALUE(LEFT(A1,FIND(" ",A1)-1))

Pour saisir la partie de l’adresse suivant le premier espace, vous pouvez utiliser cette formule:

=MID(A1,FIND(" ",A1)+1,LEN(A1))

Vous pouvez, si vous préférez, concevoir une formule faisant référence au numéro de maison que vous avez extrait avec la première formule. Supposons, par exemple, que votre formule de numéro de maison se trouve dans la colonne B; vous pouvez placer ce qui suit dans la colonne C:

=TRIM(SUBSTITUTE(A1,B1,))

Avec vos formules dans les colonnes B et C (et les adresses dans la colonne A), vous pouvez copier les formules pour le nombre de lignes nécessaires. Ensuite, vous pouvez sélectionner cette plage B: C et utiliser Collage spécial pour coller les valeurs dans ces cellules. Après cela, vous pouvez supprimer en toute sécurité les adresses d’origine dans la colonne A.

Si vous devez souvent faire de nombreuses adresses, il vaut peut-être mieux utiliser une macro pour effectuer le démontage. L’exemple de macro suivant fonctionne sur n’importe quelle plage que vous avez sélectionnée lorsque vous l’exécutez. Il insère une colonne de cellules vides à gauche de la sélection, remplit ces cellules avec le numéro de maison, puis ajuste les adresses afin que le numéro de maison ne soit plus inclus.

Sub SplitAddress()

Dim c As Range     Dim j As Integer     Dim n As String     Dim addr As String

Selection.Insert Shift:=xlToRight     Selection.Offset(0, 1).Select

For Each c In Selection         j = InStr(1, c, " ")

n = Left(c, j)

c.Offset(0, -1) = n         addr = Trim(Right(c, Len(c) - j))

c = addr     Next End Sub

Si vous préférez, vous pouvez créer une fonction définie par l’utilisateur qui renvoie uniquement le numéro de maison:

Function GrabHouseNumber(Raw As String) As Text     Dim x As Variant     Dim House As Variant

x = Split(Raw, " ")     'use space char to split elements into array     House = x(0)   'first element of array     If Left(House, 1) Like "#" Then         'First char is numeric digit         GrabHouseNumber = House  'set return value as house number     Else         GrabHouseNumber = ""  'First char is text, so not a house number     End If End Function

Un avantage de l’utilisation de la fonction définie par l’utilisateur est qu’elle vérifie si la première partie de l’adresse d’origine commence réellement par un nombre ou non. Si ce n’est pas le cas, cela suppose que l’adresse ne commence pas par un numéro de rue. (Le numéro de maison entier n’a pas besoin d’être numérique; il doit juste commencer par un chiffre.)

_Note: _

Si vous souhaitez savoir comment utiliser les macros décrites sur cette page (ou sur toute autre page des sites ExcelTips), j’ai préparé une page spéciale qui comprend des informations utiles.

lien: / excelribbon-ExcelTipsMacros [Cliquez ici pour ouvrir cette page spéciale dans un nouvel onglet de navigateur].

ExcelTips est votre source pour une formation Microsoft Excel rentable.

Cette astuce (13350) s’applique à Microsoft Excel 2007, 2010, 2013, 2016, 2019 et Excel dans Office 365.