Allan a une liste de plusieurs centaines de noms et d’adresses. Les adresses de rue vont de Main Street, 123 Main Street, US RT 2 ou 187 South Elm St. Il aimerait extraire le numéro de rue des adresses.

Ainsi, l’adresse 123 Main Street se retrouverait avec « 123 » dans une cellule et « Main Street » dans une autre. S’il n’y a pas de numéro de rue, rien ne se retrouve dans la colonne de numéro de rue. L’outil Texte en colonnes ne fonctionnera pas et il se demande comment il peut le faire.

Dans un monde parfait, Excel vous permettrait de séparer facilement les numéros des noms de rue. Comme cette option n’existe pas, vous avez plusieurs choix. L’option la plus longue consiste à ajouter une colonne supplémentaire et à retaper les données. Si, cependant, vous souhaitez gagner du temps, vous pouvez utiliser diverses formules pour accomplir la tâche.

En supposant que la liste d’adresses se trouve dans la colonne A (commençant dans la cellule A1), vous pouvez utiliser une formule similaire à la suivante pour extraire la partie numérique de l’adresse:

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

En supposant que vous mettez la formule dans la cellule B1, vous pouvez alors utiliser une formule différente pour dériver la partie non numérique de l’adresse:

=TRIM(RIGHT(A1,LEN(A1)-LEN(B1)))

Notez que cette approche a une limitation. Certaines adresses, en particulier dans les grandes régions métropolitaines, utilisent un format tel que 152-33, boul. Bell. Les formules ci-dessus fonctionneront pour ces adresses, mais si l’alternative, 152 33, boul. Bell, est utilisée, la formule ne sera pas analysée correctement. À moins que vous ne souhaitiez acheter un programme d’analyse d’adresses développé par des professionnels, les formules ci-dessus et une analyse rapide des résultats devraient être adéquates.

Une autre formule fonctionne dans ce cas. En supposant que votre adresse se trouve dans la cellule A2, entrez la formule suivante dans la cellule B2:

=IF(ISNUMBER(VALUE(LEFT(A2,1))),VALUE(LEFT(A2,FIND(" ",A2)-1)),"")

Cette formule dit: « Si le premier caractère n’est pas un nombre, laissez la cellule vide. Sinon, donnez-moi tous les caractères sur la gauche, mais sans inclure, le premier espace. » Vous pouvez ensuite utiliser le résultat de cette formule pour extraire la partie non numérique de l’adresse:

=IF(B2="",A2,MID(A2,FIND(" ",A2)+1,99))

Une autre approche consiste à utiliser une formule matricielle. Là encore, en supposant que votre adresse se trouve dans la cellule A2, vous pouvez utiliser ce qui suit:

=IF(ISNUMBER(1*MID(A2,ROW($1:$1),1)) = TRUE, LEFT(A2,FIND(" ",A2,1)),"")

Puisqu’il s’agit d’une formule matricielle, vous devez la saisir en utilisant Ctrl + Maj + Entrée. Le résultat est que la formule renvoie la partie numérique de début de l’adresse. Vous pouvez ensuite déterminer la partie non numérique en utilisant la formule matricielle suivante:

=IF(ISNUMBER(1*MID(A2,ROW($1:$1),1)) = TRUE, RIGHT(A2,LEN(A2)-FIND(" ",A2,1)),A2)

Enfin, la macro suivante peut être utilisée pour séparer l’adresse postale du nom de la rue.

Sub GetStreetNum()

Dim sStreet As String     Dim J As Integer     Dim iNum As Integer

For Each cell In Selection         sStreet = cell.Value         J = InStr(sStreet, " ")

If J > 0 Then             iNum = Val(Left(sStreet, J))

If iNum > 0 Then                 cell.Offset(0, 1).Value = iNum                 sStreet = Trim(Mid(sStreet, J, Len(sStreet)))

End If         End If         cell.Offset(0, 2).Value = sStreet     Next End Sub

Pour utiliser cette macro, sélectionnez simplement la plage de cellules contenant vos adresses, puis exécutez-la. La partie numérique de tête de l’adresse apparaîtra dans la cellule à droite de chaque adresse et le solde de l’adresse sera placé dans la cellule à droite de celle-ci. (Vous devez donc vous assurer qu’il y a deux colonnes vides à droite des adresses que vous sélectionnez.)

_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 (8029) s’applique à Microsoft Excel 97, 2000, 2002 et 2003. Vous pouvez trouver une version de cette astuce pour l’interface ruban d’Excel (Excel 2007 et versions ultérieures) ici:

lien: / excelribbon-Extracting_Street_Numbers_from_an_Address [Extraction des numéros de rue d’une adresse].